pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
How to grant a privilege on all tables or views or both of a database to someone?
4+ messages / 3 participants
[nested] [flat]

* How to grant a privilege on all tables or views or both of a database to someone?
@ 2002-03-07 10:37  Eric Du <duxy@CDSC.COM.CN>
  0 siblings, 1 reply; 4+ messages in thread

From: Eric Du @ 2002-03-07 10:37 UTC (permalink / raw)
  To: pgsql-sql

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=gb2312">
<META NAME="Generator" CONTENT="MS Exchange Server version 6.0.4417.0">
<TITLE>How to grant a privilege on all tables or views or both of a database to someone?</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/plain format -->

<P><FONT SIZE=2>Is there a shortcut to grant a privilege on all tables or views or both of a database instead of once a table or view?</FONT>
</P>

<P><FONT SIZE=2>Thanks in advance,</FONT>
</P>

<P><FONT SIZE=2>Eric Du</FONT>

<BR><FONT SIZE=2>-----------------------------------</FONT>

<BR><FONT SIZE=2>Tel: (86-010) 65544068-309</FONT>

<BR><FONT SIZE=2>Fax: (86-010) 65544066</FONT>
</P>

</BODY>
</HTML>



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: How to grant a privilege on all tables or views or both of a database to someone?
@ 2002-03-07 18:55  Paul Ogden <pogden@claresco.com>
  parent: Eric Du <duxy@CDSC.COM.CN>
  0 siblings, 1 reply; 4+ messages in thread

From: Paul Ogden @ 2002-03-07 18:55 UTC (permalink / raw)
  To: Eric Du <duxy@CDSC.COM.CN>; pgsql-sql

How to grant a privilege on all tables or views or both of a database to
someone?Here's what I did, using psql:

First you need to build the grant statements.  This is done using select
statements against pg_class ( system catalog that stores name along with
other information about each table, view, sequence in your database ).  The
output is passed to a file with the \o command.  The file will contain the
GRANT statement for each object according to your search condition described
in the WHERE clause.  Run the new file at the psql prompt with the \i
command and you are done.  I saved both the psql command line script to
build the grant statements and the resulting sql to grant relevant
permission as system files that I can run any time I build or re-build a
database.

Connect to your database using psql as the user who owns the objects which
you want to grant permissions on.  Be sure to set the appropriate permission
level by using the correct keyword ( one of Select, Update, Insert, Delete
or All ) after GRANT.  Be sure you have write permissions to the local
directory where you are saving the resulting file(s):

      \t                                              # -- turns of headings
so the don't get in the file
      \o /<local directory(ies)>/grants_tabtouser.sql
      SELECT 'GRANT { SELECT | UPDATE | INSERT | DELETE | ALL } ON ' ||
relname || ' TO <username or keyword PUBLIC>;'
      FROM pg_class
      WHERE relkind ='r'                     # -- use = 'v' for views and =
'S' for sequences or IN ( 'r','v','S' ) for all
            AND relowner IN (                 # -- remove this clause to
generate grants on all objects.  Must be superuser!
                                SELECT usesysid
                                FROM pg_user
                                WHERE usename = current_user )
    \g                                                # -- execute
    \o                                                # -- end output to
file
    \t                                                 # -- turns headings
back on

then run the sql to issue the grants. Warning, you may wish to examine the
grants_tabtouser.sql file first using your favorite editor.  Alternatively,
you could run the query in the first step without output to file turned on
and examine the results on the screen:

    \i /<local directory(ies)>/grants_tabtouser.sql

Paul Ogden


 -----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Eric Du
Sent: Thursday, March 07, 2002 02:38
To: pgsql-sql@postgresql.org
Subject: [SQL] How to grant a privilege on all tables or views or both of a
database to someone?



  Is there a shortcut to grant a privilege on all tables or views or both of
a database instead of once a table or view?

  Thanks in advance,

  Eric Du
  -----------------------------------
  Tel: (86-010) 65544068-309
  Fax: (86-010) 65544066

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: How to grant a privilege on all tables or views or both of a database to someone?
@ 2002-03-07 20:40  Andrew G. Hammond <drew@xyzzy.dhs.org>
  parent: Paul Ogden <pogden@claresco.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Andrew G. Hammond @ 2002-03-07 20:40 UTC (permalink / raw)
  To: Paul Ogden <pogden@claresco.com>; +Cc: Eric Du <duxy@CDSC.COM.CN>; pgsql-sql

On Thu, Mar 07, 2002 at 10:55:51AM -0800, Paul Ogden wrote:
> How to grant a privilege on all tables or views or both of a database to
> someone?Here's what I did, using psql:

Personally I solved this using plpgsql:

-- usage: SELECT grant_all('privilidge', 'user');
-- grants privilidge (SELECT, UPDATE, INSERT, DELETE or ALL) to the user
-- for all non-pg_ objects in the database (except indices).

DROP FUNCTION grant_all(text, text);
CREATE FUNCTION grant_all(text, text) RETURNS boolean AS '
DECLARE item RECORD;
  priv ALIAS FOR $1;
  user ALIAS FOR $2;
BEGIN 
  FOR item IN SELECT * FROM pg_class 
    WHERE relname !~ ''^pg_'' 
    AND relkind <> ''i''
  LOOP
    EXECUTE ''GRANT '' || priv 
      || '' ON '' || quote_ident(item.relname)
      || '' TO '' || quote_ident(user);
  END LOOP;
  RETURN ''t'';
END;' LANGUAGE 'plpgsql';

DROP FUNCTION grant_all_tables(text, text);
CREATE FUNCTION grant_all_tables(text, text) RETURNS boolean AS '
DECLARE item RECORD;
  priv ALIAS FOR $1;
  user ALIAS FOR $2;
BEGIN
  FOR item IN SELECT * FROM pg_class 
    WHERE relname !~ ''^pg_''
    AND relkind = ''r''
  LOOP
    EXECUTE ''GRANT '' || priv
      || '' ON '' || quote_ident(item.relname)
      || '' TO '' || quote_ident(user);
  END LOOP;
  RETURN ''t'';
END;' LANGUAGE 'plpgsql';

DROP FUNCTION grant_all_tables_and_views(text, text);
CREATE FUNCTION grant_all_tables_and_views(text, text) RETURNS boolean AS '
DECLARE item RECORD;
  priv ALIAS FOR $1;
  user ALIAS FOR $2;
BEGIN
  FOR item IN SELECT * FROM pg_class
    WHERE relname !~ ''^pg_''
    AND relkind  IN (''r'', ''v'')
  LOOP
    EXECUTE ''GRANT '' || priv
      || '' ON '' || quote_ident(item.relname)
      || '' TO '' || quote_ident(user);
  END LOOP;
  RETURN ''t'';
END;' LANGUAGE 'plpgsql';

-- 
Andrew G. Hammond  mailto:drew@xyzzy.dhs.org  http://xyzzy.dhs.org/~drew/
56 2A 54 EF 19 C0 3B 43 72 69 5B E3 69 5B A1 1F              613-389-5481 
5CD3 62B0 254B DEB1 86E0  8959 093E F70A B457 84B1
"To blow recursion you must first blow recur" -- me

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: How to grant a privilege on all tables or views or both of a database to someone?
@ 2002-03-07 21:01  Paul Ogden <pogden@claresco.com>
  parent: Andrew G. Hammond <drew@xyzzy.dhs.org>
  0 siblings, 0 replies; 4+ messages in thread

From: Paul Ogden @ 2002-03-07 21:01 UTC (permalink / raw)
  To: Andrew G. Hammond <drew@xyzzy.dhs.org>; +Cc: Eric Du <duxy@CDSC.COM.CN>; pgsql-sql

Andrew,

That's a nice solution.  Was wondering if you think its feasible to do the
same, but as only one function with an additional parameter for relation
type ( table, view, sequence, all ).

Also, say I have tables owned by different users ( ie some tables owned by a
taskmanager process and others owned by web application ) and I only want to
issue grants on objects owned by one of these users?

Thanks,

Paul Ogden

-----Original Message-----
From: Andrew G. Hammond [mailto:drew@xyzzy.dhs.org]
Sent: Thursday, March 07, 2002 12:41
To: Paul Ogden
Cc: Eric Du; pgsql-sql@postgresql.org
Subject: Re: [SQL] How to grant a privilege on all tables or views or
both of a database to someone?


On Thu, Mar 07, 2002 at 10:55:51AM -0800, Paul Ogden wrote:
> How to grant a privilege on all tables or views or both of a database to
> someone?Here's what I did, using psql:

Personally I solved this using plpgsql:

-- usage: SELECT grant_all('privilidge', 'user');
-- grants privilidge (SELECT, UPDATE, INSERT, DELETE or ALL) to the user
-- for all non-pg_ objects in the database (except indices).

DROP FUNCTION grant_all(text, text);
CREATE FUNCTION grant_all(text, text) RETURNS boolean AS '
DECLARE item RECORD;
  priv ALIAS FOR $1;
  user ALIAS FOR $2;
BEGIN
  FOR item IN SELECT * FROM pg_class
    WHERE relname !~ ''^pg_''
    AND relkind <> ''i''
  LOOP
    EXECUTE ''GRANT '' || priv
      || '' ON '' || quote_ident(item.relname)
      || '' TO '' || quote_ident(user);
  END LOOP;
  RETURN ''t'';
END;' LANGUAGE 'plpgsql';

DROP FUNCTION grant_all_tables(text, text);
CREATE FUNCTION grant_all_tables(text, text) RETURNS boolean AS '
DECLARE item RECORD;
  priv ALIAS FOR $1;
  user ALIAS FOR $2;
BEGIN
  FOR item IN SELECT * FROM pg_class
    WHERE relname !~ ''^pg_''
    AND relkind = ''r''
  LOOP
    EXECUTE ''GRANT '' || priv
      || '' ON '' || quote_ident(item.relname)
      || '' TO '' || quote_ident(user);
  END LOOP;
  RETURN ''t'';
END;' LANGUAGE 'plpgsql';

DROP FUNCTION grant_all_tables_and_views(text, text);
CREATE FUNCTION grant_all_tables_and_views(text, text) RETURNS boolean AS '
DECLARE item RECORD;
  priv ALIAS FOR $1;
  user ALIAS FOR $2;
BEGIN
  FOR item IN SELECT * FROM pg_class
    WHERE relname !~ ''^pg_''
    AND relkind  IN (''r'', ''v'')
  LOOP
    EXECUTE ''GRANT '' || priv
      || '' ON '' || quote_ident(item.relname)
      || '' TO '' || quote_ident(user);
  END LOOP;
  RETURN ''t'';
END;' LANGUAGE 'plpgsql';

--
Andrew G. Hammond  mailto:drew@xyzzy.dhs.org  http://xyzzy.dhs.org/~drew/
56 2A 54 EF 19 C0 3B 43 72 69 5B E3 69 5B A1 1F              613-389-5481
5CD3 62B0 254B DEB1 86E0  8959 093E F70A B457 84B1
"To blow recursion you must first blow recur" -- me




^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2002-03-07 21:01 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-03-07 10:37 How to grant a privilege on all tables or views or both of a database to someone? Eric Du <duxy@CDSC.COM.CN>
2002-03-07 18:55 ` Paul Ogden <pogden@claresco.com>
2002-03-07 20:40   ` Andrew G. Hammond <drew@xyzzy.dhs.org>
2002-03-07 21:01     ` Paul Ogden <pogden@claresco.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox