Received: from fulgencio.claresco.com (dnai-216-15-45-22.cust.dnai.com [216.15.45.22]) by postgresql.org (Postfix) with ESMTP id EA79C4760E6 for ; Thu, 7 Mar 2002 13:53:47 -0500 (EST) Received: from pogden (unknown [10.0.0.201]) by fulgencio.claresco.com (Postfix) with SMTP id 651E237EF6; Thu, 7 Mar 2002 10:53:47 -0800 (PST) From: "Paul Ogden" To: "Eric Du" , Subject: Re: How to grant a privilege on all tables or views or both of a database to someone? Date: Thu, 7 Mar 2002 10:55:51 -0800 Message-ID: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0002_01C1C5C6.A5C9D930" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0) Importance: Normal In-Reply-To: X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300 X-Archive-Number: 200203/86 X-Sequence-Number: 6967 This is a multi-part message in MIME format. ------=_NextPart_000_0002_01C1C5C6.A5C9D930 Content-Type: text/plain; charset="gb2312" Content-Transfer-Encoding: 7bit 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 //grants_tabtouser.sql SELECT 'GRANT { SELECT | UPDATE | INSERT | DELETE | ALL } ON ' || relname || ' TO ;' 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 //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 ------=_NextPart_000_0002_01C1C5C6.A5C9D930 Content-Type: text/html; charset="gb2312" Content-Transfer-Encoding: quoted-printable How to grant a privilege on all tables or views or both = of a database to someone?
Here's=20 what I did, using psql:
 
First=20 you need to build the grant statements.  This is done using select=20 statements against pg_class ( system catalog that stores name along with ot= her=20 information about each table, view, sequence in your database ).  The= =20 output is passed to a file with the \o command.  The file will contain= the=20 GRANT statement for each object according to your search condition describe= d in=20 the WHERE clause.  Run the new file at the psql prompt with the \i com= mand=20 and you are done.  I saved both the psql command line script to build = the=20 grant statements and the resulting sql to grant relevant permission as syst= em=20 files that I can run any time I build or re-build a=20 database.
 
Connect to your database using psql as the user = who=20 owns the objects which you want to grant permissions on.  Be sure to s= et=20 the appropriate permission level by using the correct keyword ( one of Sele= ct,=20 Update, Insert, Delete or All ) after GRANT.  Be sure you have write= =20 permissions to the local directory where you are saving the resulting=20 file(s):
 
     =20 \t            &= nbsp;           &nbs= p;            &= nbsp;       =20 # -- turns of headings so the don't get in=20 the file
      \o /<local=20 directory(ies)>/grants_tabtouser.sql 
    &n= bsp;=20 SELECT 'GRANT { SELECT | UPDATE | INSERT | DELETE | ALL } ON ' || reln= ame=20 || ' TO <username or keyword PUBLIC>;'
      FROM=20 pg_class
      WHERE relkind=20 =3D'r'           &nb= sp;        =20 # -- use =3D 'v' for views and =3D 'S' for sequences or IN ( 'r','v','S' ) = for=20 all
        =   =20  AND relowner=20 IN (           =      =20 # -- remove this clause to generate grants on all objects.  Must be=20 superuser!
        =             &nb= sp;          =20 SELECT usesysid
        =             &nb= sp;          =20 FROM pg_user
        =             &nb= sp;          =20 WHERE usename =3D current_user )
   =20 \g            &= nbsp;           &nbs= p;            &= nbsp;          #=20 -- execute
   =20 \o            &= nbsp;           &nbs= p;            &= nbsp;         =20 # -- end output to file
   =20 \t            &= nbsp;           &nbs= p;            &= nbsp;           #=20 -- turns headings back on
 
then=20 run the sql to issue the grants. Warning, you may wish to examine the=20 grants_tabtouser.sql file first using your favorite editor.  Alternati= vely,=20 you could run the query in the first step without output to file turned on = and=20 examine the results on the screen:
 
    \i /<local=20 directory(ies)>/grants_tabtouser.sql
 

Paul Ogden
<= SPAN=20 class=3D588163818-07032002> 

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

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

Thanks in advance,

Eric Du
-----------------------------------
Te= l:=20 (86-010) 65544068-309
Fax: (86-010) 65544066=20

------=_NextPart_000_0002_01C1C5C6.A5C9D930--