Grant on several tables at once
Grant on several tables at once
От:
MaRcElO PeReIrA <gandalf_mp@yahoo.com.br>
Дата:
Hello All, I have a database that contain 72 tables. How can I grant "SELECT" on all those 72 tables to a user, at once? I use to do: # grant select on table1 to marcelo; # grant select on table2 to marcelo; # ... # grant select on table72 to marcelo; but it is a lot of tables... I would like to do this at once! Thanks in advance and Best Regards, Marcelo
Re: Grant on several tables at once
От:
Rajesh Kumar Mallah <mallah@trade-india.com>
Дата:
if its possible to generate the list of tables from a sql query then do something like \o out.sql select ' grant select on table1 to ' || tablename || ' ;' ; \i out.sql regds mallah. MaRcElO PeReIrA wrote: > Hello All, > > I have a database that contain 72 tables. How can I grant "SELECT" on > all those 72 tables to a user, at once? > > I use to do: > > # grant select on table1 to marcelo; > # grant select on table2 to marcelo; > # ... > # grant select on table72 to marcelo; > > but it is a lot of tables... I would like to do this at once! > > Thanks in advance and > Best Regards, > > Marcelo > > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings
Re: Grant on several tables at once
От:
Andreas Pflug <pgadmin@pse-consulting.de>
Дата:
MaRcElO PeReIrA wrote: > Hello All, > > I have a database that contain 72 tables. How can I grant "SELECT" on > all those 72 tables to a user, at once? > > I use to do: > > # grant select on table1 to marcelo; > # grant select on table2 to marcelo; > # ... > # grant select on table72 to marcelo; > > but it is a lot of tables... I would like to do this at once! > > Thanks in advance and > Best Regards, > > Marcelo Hi Marcelo, you will need a tool for that. pgAdmin2 (win32 only) can grant on multiple objects at once, pgAdmin3 lacks this feature at the moment. Regards, Andreas
Re: Grant on several tables at once
От:
sforteln@fhcrc.org
Дата:
Marcelo,
Something like this should work to grant rights on all non system tables
psql -t -A -U $DBOWNER -c "select tablename from pg_tables where tablename not
like 'pg_%'" $DB_NAME | xargs -i ./psql -t -A -U $DBOWNER -c "grant ALL on {} to
$USER" $DB_NAME
Quoting MaRcElO PeReIrA :
> Hello All,
>
> I have a database that contain 72 tables. How can I grant "SELECT" on
> all those 72 tables to a user, at once?
>
> I use to do:
>
> # grant select on table1 to marcelo;
> # grant select on table2 to marcelo;
> # ...
> # grant select on table72 to marcelo;
>
> but it is a lot of tables... I would like to do this at once!
>
> Thanks in advance and
> Best Regards,
>
> Marcelo
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
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