Обсуждение: GRANT SELECT
Is there a simple way to grant SELECT privileges to a user for all the tables in a database, without having to do it for each table? Thanks, Steve -- Stephen Crawford Center for Environmental Informatics The Pennsylvania State University
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER } [, ...] | ALL [ PRIVILEGES ] } ON { [ TABLE ] table_name [, ...] | ALL TABLES IN SCHEMA schema_name [, ...] } TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ] See more on PostgreSQL Documentation
View this message in context: Re: GRANT SELECT
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
ElEucas
México
ElEucas
México
View this message in context: Re: GRANT SELECT
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
I see that works for 9, but I'm still in 8.4. But another person just sent me a bit of code to do it:
Steve
On 8/27/2012 1:20 PM, ElEucas wrote:
do $$ declare tn text; begin for tn in select c.oid::regclass::text from pg_class c join pg_namespace n on n.oid = c.relnamespace where n.nspname not in ('pg_catalog', 'information_schema') and n.nspname !~ '^pg_toast' and c.relkind in ('r','') loop execute format('grant select on %s to your_user', tn); end loop; end; $$ ;Thanks,
Steve
On 8/27/2012 1:20 PM, ElEucas wrote:
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER } [, ...] | ALL [ PRIVILEGES ] } ON { [ TABLE ] table_name [, ...] | ALL TABLES IN SCHEMA schema_name [, ...] } TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ] See more on PostgreSQL Documentation--
ElEucas
México
View this message in context: Re: GRANT SELECT
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
-- Stephen Crawford Center for Environmental Informatics The Pennsylvania State University
2012/8/27 Stephen Crawford <src176@psu.edu>: > I see that works for 9, but I'm still in 8.4. But another person just sent > me a bit of code to do it: > just don't use "format" function execure 'grant select on ' || tn || 'to ....'; > > do $$ > declare tn text; > begin > for tn in select c.oid::regclass::text > from pg_class c join pg_namespace n on n.oid = c.relnamespace > where n.nspname not in ('pg_catalog', > 'information_schema') and n.nspname !~ '^pg_toast' and c.relkind in > ('r','') > loop > execute format('grant select on %s to your_user', tn); > end loop; > end; > $$ ; > > Thanks, > Steve > > > On 8/27/2012 1:20 PM, ElEucas wrote: > > GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | > TRIGGER } [, ...] | ALL [ PRIVILEGES ] } ON { [ TABLE ] table_name [, ...] | > ALL TABLES IN SCHEMA schema_name [, ...] } TO { [ GROUP ] role_name | PUBLIC > } [, ...] [ WITH GRANT OPTION ] See more on PostgreSQL Documentation > -- > ElEucas > México > > ________________________________ > View this message in context: Re: GRANT SELECT > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > > -- > Stephen Crawford > Center for Environmental Informatics > The Pennsylvania State University
On 2012-08-27, Stephen Crawford <src176@psu.edu> wrote: > Is there a simple way to grant SELECT privileges to a user for all the > tables in a database, without having to do it for each table? grant the role (membership of) a role that already has the priveleges. -- ⚂⚃ 100% natural