Thursday, January 11, 2018

How to bulk-grant privileges to a selection of users using PL/SQL

This anonymous PL/SQL script will select all the desired users, and then grant SELECT on a number of performance views to these.

set serveroutput on

DECLARE

 CURSOR c1 IS
  select username
  from dba_users
  where username like 'IT%'
  or username like 'MAITD%';

BEGIN
 FOR x IN c1 LOOP
    dbms_output.put_line('user ' || x.username || ' processed.' );
    execute immediate('GRANT SELECT ON SYS.IND$ TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.OBJ$ TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.TAB$ TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.USER$ TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$DATABASE TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$INSTANCE TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$LATCH TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$LIBRARYCACHE TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$MYSTAT TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$PROCESS TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$ROWCACHE TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$SESSION TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$SESSTAT TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$SESS_IO TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$SGASTAT TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$STATNAME TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$SYSSTAT TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$SYSTEM_EVENT TO ' || x.username);
 END LOOP;
END;
/

No comments:

Post a Comment