Skip site navigation (1) Skip section navigation (2)

Re: Database Grants Bug

From: Joe Conway <mail(at)joeconway(dot)com>
To: Marcus England <Marcus(dot)England(at)noaa(dot)gov>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: Database Grants Bug
Date: 2003-08-18 15:29:40
Message-ID: 3F40F0E4.2060201@joeconway.com (view raw or flat)
Thread:
Lists: pgsql-bugs
Marcus England wrote:
> IMHO, this is confusing and limiting for Administrators who wish to
> grant privileges beyond CREATE, TEMPORARY, and TEMP across all tables in
> a database. Something I believe most, if not all other DBMS's do. "ALL"
> isn't very consistent.

Again, I don't know what your definition of "most, if not all other 
DBMS's" is, but a quick read through my MSSQL2000 manual indicates SQL 
Server is no different from Postgres in this regard. Same for Oracle 9i. 
I'd say that covers the majority of DBMS installations. I don't have a 
DB2 manual handy to check.

> Reading the comments in the documentation, apparently I'm not the only
> one who's confused about ALL.

True, it seems to come up reasonably frequently. But the docs are pretty 
clear if you read them carefully.

And if you search the mailing list archives, you'll find more than one 
script or function posted that allows GRANTs on all the tables in a 
database, for instance (including one by me). The function is pretty 
simple; here it is again for your convenience (not extensively tested -- 
use at your own risk, modify to suit, etc, etc):

CREATE OR REPLACE FUNCTION grant_all(text) RETURNS TEXT AS '
DECLARE
   rel record;
   sql text;
BEGIN
   FOR rel IN SELECT pg_catalog.quote_ident(c.relname) AS relname FROM 
pg_catalog.pg_class c WHERE c.relkind = ''r'' AND c.relnamespace NOT IN 
(select oid from pg_catalog.pg_namespace where nspname like ''pg\_%'') 
AND pg_catalog.pg_table_is_visible(c.oid) LOOP
     sql := ''grant all on '' || rel.relname || '' to '' || $1;
     RAISE NOTICE ''%'', sql;
     EXECUTE sql;
   END LOOP;
   RETURN ''OK'';
END;
' LANGUAGE 'plpgsql';

create user foo;
select grant_all('foo');


Joe


In response to

Responses

pgsql-bugs by date

Next:From: Marcus EnglandDate: 2003-08-18 15:49:29
Subject: Re: Database Grants Bug
Previous:From: Marcus EnglandDate: 2003-08-18 14:31:58
Subject: Re: Database Grants Bug

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group