Grant / Revoke functionality

<P>Hi guys,</P>
<P>I have a fairly big PostgreSQL 7.4.0 database running here and I am currently busy managing the rights of the different database users. I have a lot of tables, vieuws, sequences etc.</P>
<P>If I use Grant / Revoke to manage user rights, I have to name all of the different tables/views to grant or revoke a right. For instance, I cannot say something like 'Revoke Select on all tables' followed by 'Grant Select on all views' so users can only access data through views. There is only something like 'Grant Select on database xxx'...</P>
<P>Is there a way in which I can give rights to a user for a set of tables/views/sequences WITHOUT having to name all of the table/view/sequence names?</P>
<P>Thanks in advance.</P>
<P>Alexander Priem</P>

On Wednesday 18 February 2004 13:38, Alexander Priem wrote:
> Is there a way in which I can give rights to a user for a set of
> tables/views/sequences WITHOUT having to name all of the
> table/view/sequence names?

You can use schemas. Just put everything under schema and grant user rights to
the schema...

HTH

Shridhar

On Wednesday 18 February 2004 08:45, Shridhar Daithankar wrote:
> On Wednesday 18 February 2004 13:38, Alexander Priem wrote:
> > Is there a way in which I can give rights to a user for a set of
> > tables/views/sequences WITHOUT having to name all of the
> > table/view/sequence names?
>
> You can use schemas. Just put everything under schema and grant user rights
> to the schema...

Also - have a look on techdocs.postgresql.org - before we had schemas someone
had written a plpgsql function to do wildcard matching of table names, also a
shell-script/perl iirc.

--
Richard Huxton
Archonet Ltd

On Wed, 18 Feb 2004, Shridhar Daithankar wrote:

> On Wednesday 18 February 2004 13:38, Alexander Priem wrote:
> > Is there a way in which I can give rights to a user for a set of
> > tables/views/sequences WITHOUT having to name all of the
> > table/view/sequence names?
>
> You can use schemas. Just put everything under schema and grant user
> rights to the schema...
>

This is not accurate. The only schema level permissions are CREATE and
USAGE they are independent of the permissions on the objects contained
within. He would still need to grant access to the underlying objects.

Kris Jurka

Kris Jurka <books(at)ejurka(dot)com> writes:
> On Wed, 18 Feb 2004, Shridhar Daithankar wrote:
>> You can use schemas. Just put everything under schema and grant user
>> rights to the schema...

> This is not accurate. The only schema level permissions are CREATE and
> USAGE they are independent of the permissions on the objects contained
> within. He would still need to grant access to the underlying objects.

Right, but he could grant the required rights to PUBLIC and rely on
schema-level USAGE to control whether particular users can actually
get at particular objects. It'd be a pretty coarse-grained structure,
but it might be good enough.

Personally I'd suggest looking at using groups ...

regards, tom lane

> On Wed, 18 Feb 2004, Shridhar Daithankar wrote:
>
> > On Wednesday 18 February 2004 13:38, Alexander Priem wrote:
> > > Is there a way in which I can give rights to a user for a set of
> > > tables/views/sequences WITHOUT having to name all of the
> > > table/view/sequence names?
> >
> > You can use schemas. Just put everything under schema and grant user
> > rights to the schema...
> >
>
> This is not accurate. The only schema level permissions are CREATE and
> USAGE they are independent of the permissions on the objects contained
> within. He would still need to grant access to the underlying objects.
>
> Kris Jurka
>

If you need to do this for a lot of users use group permissions, it's a lot
easier to manage.

Chris