Thread: User Privileges
All, How can you grant privileges to a user to all tables in database? For example I have 5 tables in database A. And now I want to give SELECT/UPDATE/INSERT privileges to a user to all 5 tables. But according to documentation, I have to execute 'GRANT' query 3 times(for select/update/insert) per table. meaning total of 15 times!!!! Is there any way I can do that in just one shot? Basically I want to give user privileges to all tables in databse at once. Like we can do in MySQL by adding a user in 'db' and 'user' table. Is it possible in PostgreSQL? How? Thanks in advance.. Niral
> For example I have 5 tables in database A. And now I want to give > SELECT/UPDATE/INSERT privileges to a user to all 5 tables. But according to > documentation, I have to execute 'GRANT' query 3 times(for > select/update/insert) per table. meaning total of 15 times!!!! That's incorrect... you can do it all in one statement: GRANT select,update,insert TO "<username>" ON table_1,table_2,table_3,etc http://www.postgresql.org/users-lounge/docs/7.0/user/sql-grant.htm -Dan
Thank you.. Niral -----Original Message----- From: Dan Wilson [mailto:phpPgAdmin@acucore.com] Sent: Tuesday, December 26, 2000 1:25 PM To: Niral Trivedi; pgsql-general@postgresql.org Subject: Re: [GENERAL] User Privileges > For example I have 5 tables in database A. And now I want to give > SELECT/UPDATE/INSERT privileges to a user to all 5 tables. But according to > documentation, I have to execute 'GRANT' query 3 times(for > select/update/insert) per table. meaning total of 15 times!!!! That's incorrect... you can do it all in one statement: GRANT select,update,insert TO "<username>" ON table_1,table_2,table_3,etc http://www.postgresql.org/users-lounge/docs/7.0/user/sql-grant.htm -Dan
How do I retrieve this privilages? I want to disable menu-options within a program. For that I have to retrieve the privilages for some tables. gr, Willem ----- Original Message ----- From: "Dan Wilson" <phpPgAdmin@acucore.com> To: "Niral Trivedi" <niral.trivedi@insage.com>; <pgsql-general@postgresql.org> Sent: Tuesday, December 26, 2000 7:25 PM Subject: Re: [GENERAL] User Privileges > > For example I have 5 tables in database A. And now I want to give > > SELECT/UPDATE/INSERT privileges to a user to all 5 tables. But according > to > > documentation, I have to execute 'GRANT' query 3 times(for > > select/update/insert) per table. meaning total of 15 times!!!! > > That's incorrect... you can do it all in one statement: > > GRANT select,update,insert TO "<username>" ON table_1,table_2,table_3,etc > > http://www.postgresql.org/users-lounge/docs/7.0/user/sql-grant.htm > > -Dan > > >
SELECT relacl FROM pg_class WHERE relname = '[table name]'
The relacl column contains specific information that must then be parsed to
determine the users which have access to a the table and the privileges
granted.
Example:
{"=","dan=arw","group developers=arw"}
The first section means the PUBLIC does not have any privileges on the
table. Each permission (SELECT, INSERT, UPDATE/DELETE, RULE) is signified
by a letter. r = select, a = insert, w = update/delete, R = rule. User
"dan" has SELECT, INSERT, UPDATE/DELETE rights, but not RULE rights. Group
"developers" has the same privileges.
For more details --
http://www.postgresql.org/users-lounge/docs/7.0/user/sql-grant.htm
-Dan
----- Original Message -----
From: "W. van den Akker" <wvdakker@wilsoft.nl>
To: <pgsql-general@postgresql.org>
Sent: Tuesday, December 26, 2000 11:57 PM
Subject: Re: [GENERAL] User Privileges
> How do I retrieve this privilages?
> I want to disable menu-options within a program. For that I have to
> retrieve the privilages for some tables.
>
> gr,
>
> Willem
>
> ----- Original Message -----
> From: "Dan Wilson" <phpPgAdmin@acucore.com>
> To: "Niral Trivedi" <niral.trivedi@insage.com>;
> <pgsql-general@postgresql.org>
> Sent: Tuesday, December 26, 2000 7:25 PM
> Subject: Re: [GENERAL] User Privileges
>
>
> > > For example I have 5 tables in database A. And now I want to give
> > > SELECT/UPDATE/INSERT privileges to a user to all 5 tables. But
according
> > to
> > > documentation, I have to execute 'GRANT' query 3 times(for
> > > select/update/insert) per table. meaning total of 15 times!!!!
> >
> > That's incorrect... you can do it all in one statement:
> >
> > GRANT select,update,insert TO "<username>" ON
table_1,table_2,table_3,etc
> >
> > http://www.postgresql.org/users-lounge/docs/7.0/user/sql-grant.htm
> >
> > -Dan
> >
> >
> >
>
Hello, the information can be found in the system-table-column PG_CLASS.RELACL: SELECT RELACL FROM PG_CLASS WHERE RELNAME = 't_dummy'; The result seems to be an array of granted permissions, so you should treat it is an array and look for the appropriate user- or group-name. Hope this helps ... Regards, Jens "W. van den Akker" schrieb: > > How do I retrieve this privilages? > I want to disable menu-options within a program. For that I have to > retrieve the privilages for some tables. > > gr, > > Willem > > ----- Original Message ----- > From: "Dan Wilson" <phpPgAdmin@acucore.com> > To: "Niral Trivedi" <niral.trivedi@insage.com>; > <pgsql-general@postgresql.org> > Sent: Tuesday, December 26, 2000 7:25 PM > Subject: Re: [GENERAL] User Privileges > > > > For example I have 5 tables in database A. And now I want to give > > > SELECT/UPDATE/INSERT privileges to a user to all 5 tables. But according > > to > > > documentation, I have to execute 'GRANT' query 3 times(for > > > select/update/insert) per table. meaning total of 15 times!!!! > > > > That's incorrect... you can do it all in one statement: > > > > GRANT select,update,insert TO "<username>" ON table_1,table_2,table_3,etc > > > > http://www.postgresql.org/users-lounge/docs/7.0/user/sql-grant.htm > > > > -Dan > > > > > > ============================================= Jens Hartwig --------------------------------------------- debis Systemhaus GEI mbH 10875 Berlin Tel. : +49 (0)30 2554-3282 Fax : +49 (0)30 2554-3187 Mobil : +49 (0)170 167-2648 E-Mail : jhartwig@debis.com =============================================