Thread: Grant / Revoke functionality
<p>Hi guys,<p>I have a fairly big PostgreSQL 7.4.0 database running here and I am currently busy managing the rights of thedifferent database users. I have a lot of tables, vieuws, sequences etc.<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 somethinglike 'Revoke Select on all tables' followed by 'Grant Select on all views' so users can only access data throughviews. There is only something like 'Grant Select on database xxx'...<p>Is there a way in which I can give rightsto a user for a set of tables/views/sequences WITHOUT having to name all of the table/view/sequence names?<p>Thanksin advance.<p>Alexander Priem
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.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?Kind regards,AlexanderPriem.> On Wednesday 18 February 2004 13:38, Alexander Priem wrote:> > Is there a way in whichI 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> > > ---------------------------(end of broadcast)--------------------------->TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.orgMet vriendelijkegroet,Alexander PriemCICT SolutionsEmail: ap@cict.nlInte rnet: www.cict.nl
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 />Kindregards,<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/sequencesWITHOUT having to name all of the<br />> > table/view/sequence names?<br />> <br />> Youcan 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 />CICTSolutions<br />Email: ap@cict.nl<br />Internet: www.cict.nl<br />
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
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, withouthaving 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 />Kindregards,<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 rightsI 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 thoseright 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="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/></b>
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
> 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