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

От:
Ang Chin Han <angch@bytecraft.com.my>
Дата:
Вложения:
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
FAQ