use Perl

• Granting permissions on all objects (Score:1)

  by nmueller (1273) on 2006.03.02 16:01 (#46416)

  In general, stuff like that is kind of dangerous. MySQL allows you to use wildcards in several places in grants, and (for some of them) instead of expanding the wildcards at grant time it does so at query time. New users can be created with default permissions that let them access _way_ more stuff then they should. If/as you get more into PostgreSQL you'll see that it likes implicit actions much less then MySQL. Not as good for a joe-user, but if I was running an important database (which is what PostgreSQL was intended for) I wouldn't have it any other way.
  
  That said, these little functions might help you out. call "select grant_all('insert', 'nmueller')" to grant nmueller insert acess on all tables in your database.
  
  create or replace function grant_all (text, text) returns integer as '
  declare
  tables record;
  perms alias for $1;
  user alias for $2;
  begin
  
  for tables in select ''"'' || nspname || ''"."'' || relname || ''"'' as table fr
  om pg_class, pg_namespace where relname not like ''pg_%'' and relkind in (''r'',
    ''v'', ''S'') and nspname != ''information_schema'' and relnamespace = pg_names
  pace.oid loop
  execute ''grant '' || perms || '' on '' || tables.table || '' to '' || user;
  end loop;
  
  return 1;
  end;
  ' language 'plpgsql';
  
  create or replace function revoke_all (text) returns integer as '
  declare
  tables record;
  user alias for $1;
  begin
  
  for tables in select nspname || ''.'' || relname as table from pg_class, pg_name
  space where relname not like ''pg_%'' and relkind in (''r'', ''v'', ''S'') and r
  elnamespace = pg_namespace.oid loop
  execute ''revoke all on '' || tables.table || '' from '' || user;
  end loop;
  
  return 1;
  end;
  ' language 'plpgsql';
  
          --Nate
  
  PS: Try being more positive -- it's good for your health.

  Reply to This