Re: Database Grants Bug

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