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; /
Minimalistic Oracle contains a collection of practical examples from my encounters with Oracle technologies. When relevant, I also write about other technologies, like Linux or PostgreSQL. Many of the posts starts with "how to" since they derive directly from my own personal experience. My goal is to provide simple examples, so that they can be easily adapted to other situations.
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.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment