Received: from fulgencio.claresco.com (dnai-216-15-45-22.cust.dnai.com [216.15.45.22]) by postgresql.org (Postfix) with ESMTP id 6C6BE475E83 for ; Thu, 7 Mar 2002 15:59:25 -0500 (EST) Received: from pogden (unknown [10.0.0.201]) by fulgencio.claresco.com (Postfix) with SMTP id 67B6F37EF6; Thu, 7 Mar 2002 12:59:26 -0800 (PST) From: "Paul Ogden" To: "Andrew G. Hammond" Cc: "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 13:01:29 -0800 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0) In-reply-to: <20020307204059.GA22890@xyzzy.dhs.org> Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300 X-Archive-Number: 200203/92 X-Sequence-Number: 6973 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