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
Thanks for your reply.<BR><BR>I already have all the tables/views/sequences set up and filled. Can I create a new schema and 'assign' the existing tables/views/sequences to this new schema? Will this affect current queries I have created?<BR><BR>Kind regards,<BR>Alexander Priem.<BR><BR><BR>> On Wednesday 18 February 2004 13:38, Alexander Priem wrote:<BR>> > Is there a way in which I can give rights to a user for a set of<BR>> > tables/views/sequences WITHOUT having to name all of the<BR>> > table/view/sequence names?<BR>> <BR>> You can use schemas. Just put everything under schema and grant user rights to <BR>> the schema...<BR>> <BR>> HTH<BR>> <BR>> Shridhar<BR>> <BR>> <BR>> ---------------------------(end of broadcast)---------------------------<BR>> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org<BR><BR>Met vriendelijke groet,<BR><BR>Alexander Priem<BR>CICT Solutions<BR>Email: ap@cict.nl<BR>Inte
rnet: www.cict.nl<BR>
Import Notes
Resolved by subject fallback
On Wednesday 18 February 2004 14:41, Alexander Priem wrote:
Thanks for your reply.
I already have all the tables/views/sequences set up and filled. Can I
create a new schema and 'assign' the existing tables/views/sequences to
this new schema? Will this affect current queries I have created?
You mean alter table set schema to foo? I doubt you can do that..
What you can do is dump/reload a single schema. After dumping the schema, edit
the dump to schema creation/set search path by hand and it will insert the
objects in the new schema..
HTH
Shridhar
Import Notes
Resolved by subject fallback
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@ejurka.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
Hi Shridhar,<BR><BR>I created some schemas, as you seggested, but the only rights I can apply to a schema are 'usage' and 'create'. Is it possible to assign 'select', 'insert' and 'update' rights to an entire schema?<BR><BR>Kind regards,<BR>Alexander Priem.<BR><BR><BR><BR>> On Wednesday 18 February 2004 13:38, Alexander Priem wrote:<BR>> > Is there a way in which I can give rights to a user for a set of<BR>> > tables/views/sequences WITHOUT having to name all of the<BR>> > table/view/sequence names?<BR>> <BR>> You can use schemas. Just put everything under schema and grant user rights to <BR>> the schema...<BR>> <BR>> HTH<BR>> <BR>> Shridhar<BR>> <BR><BR>Met vriendelijke groet,<BR><BR>Alexander Priem<BR>CICT Solutions<BR>Email: ap@cict.nl<BR>Internet: www.cict.nl<BR>
Import Notes
Resolved by subject fallback
On Thursday 19 February 2004 15:43, Alexander Priem wrote:
Hi Shridhar,
I created some schemas, as you seggested, but the only rights I can apply
to a schema are 'usage' and 'create'. Is it possible to assign 'select',
'insert' and 'update' rights to an entire schema?
No. You can not do that. You have to grant those right on the objects. However
while revoking them you can just revoke the schema rights and the objects
will be inaccessible to that user.
HTH
Shridhar
Import Notes
Resolved by subject fallback
Ah. In that case schema's are of no use for me. I just want an easy way to give users different rights on tables/views, without having to specify every single table and view. Maybe this is something that needs to be changed in a future release? I have many many tables and views and having to specify every single one of them is just a drag...<BR><BR>Kind regards,<BR>Alexander Priem.<BR><BR><BR>> On Thursday 19 February 2004 15:43, Alexander Priem wrote:<BR>> > Hi Shridhar,<BR>> ><BR>> > I created some schemas, as you seggested, but the only rights I can apply<BR>> > to a schema are 'usage' and 'create'. Is it possible to assign 'select',<BR>> > 'insert' and 'update' rights to an entire schema?<BR>> <BR>> No. You can not do that. You have to grant those right on the objects. Howe> ver <BR>> while revoking them you can just revoke the schema rights and the objects <BR>> will be inaccessible to that user.<BR>> <BR>> HTH<B
R>> <BR>> Shridhar<BR><BR><BR>Met vriendelijke groet,<BR><BR>Alexander Priem<BR>CICT Solutions<BR>Email: ap@cict.nl<BR>Internet: www.cict.nl<BR>
Import Notes
Resolved by subject fallback
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