Thread: Revoke privilege from multiple tables
Hello list,
I'd like to give a user only SELECT privileges, database-wide. Is there an easy way to do this? I could execute a REVOKE ALL PRIVILEGES command for each table, then execute a GRANT SELECT command for each table, but that is tedious.
It would be great to do something like this:
REVOKE ALL PRIVILEGES ON * FROM user_name
GRANT SELECT ON * TO user_name
Please help me.
Aaron Dummer
We don't have per-db grant/revoke capabilities. You could create a plpgsql function that does the grant/revokes for an arbitrary table name, then call the function on all the pg_class object, skipping the system tables, of course. I think there are some examples on the plpgsql cookbook web page referenced on the techdocs web site. This is becoming an FAQ, folks. --------------------------------------------------------------------------- Aaron Dummer wrote: > Hello list, > > I'd like to give a user only SELECT privileges, database-wide. Is there an easy way to do this? I could execute a REVOKEALL PRIVILEGES command for each table, then execute a GRANT SELECT command for each table, but that is tedious. > > It would be great to do something like this: > > REVOKE ALL PRIVILEGES ON * FROM user_name > GRANT SELECT ON * TO user_name > > Please help me. > > Aaron Dummer -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073