Re: Grant on several tables at once

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