Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • 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.