Re: [ADMIN] Grant on several tables at once

[Ang Chin Han]

There was a review of a DB book on Slashdot some time ago, and parts of the book said that SELECTs are always safe since it never modifies anything in the db :)

This proves it wrong:


Try this (7.4Devel but should work on 7.3): Granting to several users.

-- pg_grant(priviledge, table, user)
create or replace function
  pg_grant(text, text, text) returns boolean language plpgsql as
'begin
execute ''grant '' || $1 || '' on '' || $2 || '' to '' || $3;
return true;
end;';

create or replace function
  pg_revoke(text, text, text) returns boolean language plpgsql as
'begin
execute ''revoke '' || $1 || '' on '' || $2 || '' from '' || $3;
return true;
end;';

-- Check if these are the tables you want:
select schemaname || '.' || tablename from pg_tables where
  schemaname = 'public';

-- Here we go:
select count(*) from pg_tables where
  schemaname = 'public' and
  pg_grant('select', schemaname || '.' || tablename, 'marcelo');

-- "Undo"
select count(*) from pg_tables where
  schemaname = 'public' and
  pg_revoke('select', schemaname || '.' || tablename, 'marcelo');

--
Linux homer 2.4.18-14 #1 Wed Sep 4 13:35:50 EDT 2002 i686 i686 i386 GNU/Linux
11:00am up 222 days, 2:05, 5 users, load average: 5.03, 5.08, 5.08

pgp00000.pgp
Description: PGP signature