Quantcast

Limit the normal user to see system catalog or not??? And create privilege???

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Limit the normal user to see system catalog or not??? And create privilege???

leaf_yxj
For oracle, the normal user can't see all the system catalog. but for postgresql, it looks like all the user can see the system catalog.  Should we limit the user read privilege to system catalog?

In oracle, the system privilege has create table, create view,create function.  For postgresql database, how to control the user who only can create table but can't create view. Based on the test I did, once the user has the create privilege on the schema, the user will have any create privilege on that schema. In postgresql, Rule is used to control that ??? very confused!

Thanks.
Regards.

Grace
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Limit the normal user to see system catalog or not??? And create privilege???

Scott Marlowe-2
On Wed, Mar 28, 2012 at 10:54 AM, leaf_yxj <[hidden email]> wrote:
> For oracle, the normal user can't see all the system catalog. but for
> postgresql, it looks like all the user can see the system catalog.  Should
> we limit the user read privilege to system catalog?

Yeah, postgresql tends to focus on controlling what the user can DO
not so much on what they can SEE about the schema.  However...

> In oracle, the system privilege has create table, create view,create
> function.  For postgresql database, how to control the user who only can
> create table but can't create view. Based on the test I did, once the user
> has the create privilege on the schema, the user will have any create
> privilege on that schema. In postgresql, Rule is used to control that ???
> very confused!

PostgreSQL just doesn't have the fine grained control that Oracle has.
 If you can create a table, you can create a view.  OTOH, since a view
is basical an empty table with a rule on top, it's not like it's all
that different.

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Limit the normal user to see system catalog or not??? And create privilege???

Adrian Klaver-3
In reply to this post by leaf_yxj
On 03/28/2012 09:54 AM, leaf_yxj wrote:

> For oracle, the normal user can't see all the system catalog. but for
> postgresql, it looks like all the user can see the system catalog.  Should
> we limit the user read privilege to system catalog?
>
> In oracle, the system privilege has create table, create view,create
> function.  For postgresql database, how to control the user who only can
> create table but can't create view. Based on the test I did, once the user
> has the create privilege on the schema, the user will have any create
> privilege on that schema. In postgresql, Rule is used to control that ???
> very confused!

Path to unconfusion:):
http://www.postgresql.org/docs/9.0/interactive/sql-grant.html

You can grant CREATE on a schema and then restrict CREATE within the
schema for different objects types. In recent versions you are looking
for ALL * IN SCHEMA schema_name where * is the object type.

>
> Thanks.
> Regards.
>
> Grace
>
> --


--
Adrian Klaver
[hidden email]

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Limit the normal user to see system catalog or not??? And create privilege???

Bruce Momjian
On Wed, Mar 28, 2012 at 01:54:58PM -0700, Adrian Klaver wrote:

> On 03/28/2012 09:54 AM, leaf_yxj wrote:
> >For oracle, the normal user can't see all the system catalog. but for
> >postgresql, it looks like all the user can see the system catalog.  Should
> >we limit the user read privilege to system catalog?
> >
> >In oracle, the system privilege has create table, create view,create
> >function.  For postgresql database, how to control the user who only can
> >create table but can't create view. Based on the test I did, once the user
> >has the create privilege on the schema, the user will have any create
> >privilege on that schema. In postgresql, Rule is used to control that ???
> >very confused!
>
> Path to unconfusion:):
> http://www.postgresql.org/docs/9.0/interactive/sql-grant.html
>
> You can grant CREATE on a schema and then restrict CREATE within the
> schema for different objects types. In recent versions you are
> looking for ALL * IN SCHEMA schema_name where * is the object type.

I think the problem with ALL * IN SCHEMA it just applies permissions on
all objects in the schema at a point in time, i.e. it doesn't apply to
objects created _after_ that command was run.

--
  Bruce Momjian  <[hidden email]>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Limit the normal user to see system catalog or not??? And create privilege???

Adrian Klaver-3
On 05/02/2012 11:42 AM, Bruce Momjian wrote:

> On Wed, Mar 28, 2012 at 01:54:58PM -0700, Adrian Klaver wrote:
>> On 03/28/2012 09:54 AM, leaf_yxj wrote:
>>> For oracle, the normal user can't see all the system catalog. but for
>>> postgresql, it looks like all the user can see the system catalog.  Should
>>> we limit the user read privilege to system catalog?
>>>
>>> In oracle, the system privilege has create table, create view,create
>>> function.  For postgresql database, how to control the user who only can
>>> create table but can't create view. Based on the test I did, once the user
>>> has the create privilege on the schema, the user will have any create
>>> privilege on that schema. In postgresql, Rule is used to control that ???
>>> very confused!
>>
>> Path to unconfusion:):
>> http://www.postgresql.org/docs/9.0/interactive/sql-grant.html
>>
>> You can grant CREATE on a schema and then restrict CREATE within the
>> schema for different objects types. In recent versions you are
>> looking for ALL * IN SCHEMA schema_name where * is the object type.
>
> I think the problem with ALL * IN SCHEMA it just applies permissions on
> all objects in the schema at a point in time, i.e. it doesn't apply to
> objects created _after_ that command was run.

True, but in the above was an explanation of default privileges which
led to this link:

http://www.postgresql.org/docs/9.0/interactive/sql-alterdefaultprivileges.html

ALTER DEFAULT PRIVILEGES does allow you to control what happens in the future.
Admittedly not the most obvious connection:)


--
Adrian Klaver
[hidden email]

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Limit the normal user to see system catalog or not??? And create privilege???

Bruce Momjian
On Wed, May 02, 2012 at 04:03:01PM -0700, Adrian Klaver wrote:

> On 05/02/2012 11:42 AM, Bruce Momjian wrote:
> > On Wed, Mar 28, 2012 at 01:54:58PM -0700, Adrian Klaver wrote:
> >> On 03/28/2012 09:54 AM, leaf_yxj wrote:
> >>> For oracle, the normal user can't see all the system catalog. but for
> >>> postgresql, it looks like all the user can see the system catalog.  Should
> >>> we limit the user read privilege to system catalog?
> >>>
> >>> In oracle, the system privilege has create table, create view,create
> >>> function.  For postgresql database, how to control the user who only can
> >>> create table but can't create view. Based on the test I did, once the user
> >>> has the create privilege on the schema, the user will have any create
> >>> privilege on that schema. In postgresql, Rule is used to control that ???
> >>> very confused!
> >>
> >> Path to unconfusion:):
> >> http://www.postgresql.org/docs/9.0/interactive/sql-grant.html
> >>
> >> You can grant CREATE on a schema and then restrict CREATE within the
> >> schema for different objects types. In recent versions you are
> >> looking for ALL * IN SCHEMA schema_name where * is the object type.
> >
> > I think the problem with ALL * IN SCHEMA it just applies permissions on
> > all objects in the schema at a point in time, i.e. it doesn't apply to
> > objects created _after_ that command was run.
>
> True, but in the above was an explanation of default privileges which
> led to this link:
>
> http://www.postgresql.org/docs/9.0/interactive/sql-alterdefaultprivileges.html
>
> ALTER DEFAULT PRIVILEGES does allow you to control what happens in the future.
> Admittedly not the most obvious connection:)

Oh, I forgot about that one.

--
  Bruce Momjian  <[hidden email]>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Limit the normal user to see system catalog or not??? And create privilege???

leaf_yxj
Hi Super Guys,
 
Thanks. I learned  a lot. It's very good for me to know that.
 
Regards.
 
Grace



At 2012-05-03 07:15:29,"Bruce Momjian" <[hidden email]> wrote: >On Wed, May 02, 2012 at 04:03:01PM -0700, Adrian Klaver wrote: >> On 05/02/2012 11:42 AM, Bruce Momjian wrote: >> > On Wed, Mar 28, 2012 at 01:54:58PM -0700, Adrian Klaver wrote: >> >> On 03/28/2012 09:54 AM, leaf_yxj wrote: >> >>> For oracle, the normal user can't see all the system catalog. but for >> >>> postgresql, it looks like all the user can see the system catalog.  Should >> >>> we limit the user read privilege to system catalog? >> >>> >> >>> In oracle, the system privilege has create table, create view,create >> >>> function.  For postgresql database, how to control the user who only can >> >>> create table but can't create view. Based on the test I did, once the user >> >>> has the create privilege on the schema, the user will have any create >> >>> privilege on that schema. In postgresql, Rule is used to control that ??? >> >>> very confused! >> >> >> >> Path to unconfusion:): >> >> http://www.postgresql.org/docs/9.0/interactive/sql-grant.html >> >> >> >> You can grant CREATE on a schema and then restrict CREATE within the >> >> schema for different objects types. In recent versions you are >> >> looking for ALL * IN SCHEMA schema_name where * is the object type. >> >  >> > I think the problem with ALL * IN SCHEMA it just applies permissions on >> > all objects in the schema at a point in time, i.e. it doesn't apply to >> > objects created _after_ that command was run. >>  >> True, but in the above was an explanation of default privileges which >> led to this link: >>  >> http://www.postgresql.org/docs/9.0/interactive/sql-alterdefaultprivileges.html >>  >> ALTER DEFAULT PRIVILEGES does allow you to control what happens in the future. >> Admittedly not the most obvious connection:) > >Oh, I forgot about that one. > >--  >  Bruce Momjian  <[hidden email]>        http://momjian.us >  EnterpriseDB                             http://enterprisedb.com > >  + It's impossible for everything to be true. +



Loading...