Thread: GRANT question

Is there a shortcut to grant all privileges on a schema and it's objects to
a user  without having to issue GRANT statements for each individual object
in the schema?  Given all the possible types of objects I doubt it but
thought I would ask just in case there was a way to do it.

Chris

On Wed, Jun 02, 2004 at 07:51:58 -0700,
  Chris Ochs <chris@paymentonline.com> wrote:
> Is there a shortcut to grant all privileges on a schema and it's objects to
> a user  without having to issue GRANT statements for each individual object
> in the schema?  Given all the possible types of objects I doubt it but
> thought I would ask just in case there was a way to do it.

No. People have asked about similar ways to do lots of grants at once,
but I haven't seen anyone claim to be working on this.

<>How about using phpPgAdmin? Or something like that?

Also, is there a SQL statement that will return all the possible
privileges? Use that in a subselect and then grant WHERE?
Chris Ochs <chris@paymentonline.com> wrote:
<quote
---------------------------------------------------------------------->

>> Is there a shortcut to grant all privileges on a schema and it's objects to
>> a user  without having to issue GRANT statements for each individual object
>> in the schema?  Given all the possible types of objects I doubt it but
>> thought I would ask just in case there was a way to do it.
>
>

No. People have asked about similar ways to do lots of grants at once,
but I haven't seen anyone claim to be working on this.
</quote ----------------------------------------------------------------------->

Chris Ochs said:
> Is there a shortcut to grant all privileges on a schema and it's objects
> to
> a user  without having to issue GRANT statements for each individual
> object
> in the schema?  Given all the possible types of objects I doubt it but
> thought I would ask just in case there was a way to do it.

There is a roundabout way, but it's a bit cludgey - but I use it a lot as
we add new tables, views and functions to our development db.

There are some examples below.

For example, select 'grant all on '||schemaname||'.'||tablename||' to
MYUSERNAME;'
from pg_tables
where schemaname in ('SOMESCHEMA1', 'SOMESCHEMA2', ... etc)
order by schemaname, tablename;

This creates a series of grant statements which you can then apply against
the database (using psql for example).

I guess that there is no reason why you couldn't create a function (using
a temp table, and the execute statement) which you passed the schema name
to grant rights for, and the user to receive the rights, and possibly
specify the kind of rights to grant. Invoking the function would then
apply the grants for you. I haven't bothered with this as the simple
queries below work fine for me.

Here are the queries I use:

For TABLES
==========
select 'grant all on '||schemaname||'.'||tablename||' to SOMEUSERNAME;'
from pg_tables
where schemaname in ('SOMESCHEMA1', 'SOMESCHEMA2')
order by schemaname, tablename;

For VIEWS
=========
SELECT 'grant all on '||n.nspname||'.'||c.relname||' to SOMEUSERNAME;'
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname in ('SOMESCHEMA1', 'SOMESCHEMA2')
and c.relkind = 'v'
ORDER BY n.nspname, c.relname;

For FUNCTIONS
=============
select 'grant all on function
'||n.nspname||'.'||p.proname||'('||oidvectortypes(p.proargtypes)||') to
SOMEUSERNAME;'
from pg_proc p, pg_namespace n
where n.oid = p.pronamespace
and n.nspname in ('SOMESCHEMA1', 'SOMESCHEMA2')
order by n.nspname, p.proname;

Hope that helps some.

John Sidney-Woollett

Missed one out, ... the script for sequences is:

select 'grant all on '||n.nspname||'.'||c.relname||' to MYUSERNAME;'
from pg_class c, pg_namespace n
where n.oid = c.relnamespace
and c.relkind IN ('S')
and n.nspname in ('SOMESCHEMA1', 'SOMESCHEMA2');

John Sidney-Woollett