The following scheduler job refreshes all the roles in the db with grants for newly created tables under schemas.
It is such a pain to manually grant read or read write access to users in the db when a new table is created in an application schema.
So for this task we created a user name role_refresh. This user has dba role
The roles we create have a naming convention.
Read only roles will have schema_name_ro_role
Read/Write roles will habe schema_name_rw_role
Under this user we created a procedure as well as scheduler job
Procedure
Scheduler Job
It is such a pain to manually grant read or read write access to users in the db when a new table is created in an application schema.
So for this task we created a user name role_refresh. This user has dba role
The roles we create have a naming convention.
Read only roles will have schema_name_ro_role
Read/Write roles will habe schema_name_rw_role
Under this user we created a procedure as well as scheduler job
Procedure
CREATE OR REPLACE PROCEDURE Role_refresh.REFRESH_SCHEMA_PRIVS (p_schema IN VARCHAR2, p_role IN VARCHAR2, p_privilege IN VARCHAR2) AUTHID CURRENT_USER IS v_exec_string VARCHAR2(300); CURSOR c_tables IS SELECT table_name FROM sys.dba_tables WHERE owner = p_schema; CURSOR c_views IS SELECT object_name FROM sys.dba_objects WHERE owner = p_schema AND status='VALID' AND object_type='VIEW'; grant_option EXCEPTION; PRAGMA EXCEPTION_INIT(grant_option, -1720); BEGIN IF (p_schema IS NULL OR p_role IS NULL OR p_privilege IS NULL) THEN dbms_output.put_line('One of the mandatory parameters are blank.'); dbms_output.put_line('Usage: refresh_schema_privs(<schema_name>, <role>, [ RO | RW ])'); ELSE FOR r_tables IN c_tables LOOP BEGIN IF UPPER(p_privilege) = 'RO' THEN v_exec_string := 'GRANT SELECT ON ' || p_schema || '.' || r_tables.table_name || ' TO ' || p_role; ELSIF UPPER(p_privilege) = 'RW' THEN v_exec_string := 'GRANT SELECT, INSERT, UPDATE, DELETE, ALTER ON ' || p_schema || '.' || r_tables.table_name || ' TO ' || p_role; ELSE dbms_output.put_line('Invalid Input for Parameter privilege.'); END IF; EXECUTE IMMEDIATE v_exec_string; EXCEPTION WHEN grant_option THEN CONTINUE; END; END LOOP; FOR r_views in c_views LOOP BEGIN EXECUTE IMMEDIATE 'GRANT SELECT ON ' || p_schema || '.' || r_views.object_name || ' TO ' || p_role; EXCEPTION WHEN grant_option THEN CONTINUE; END; END LOOP; END IF; END; /
Scheduler Job
BEGIN SYS.DBMS_SCHEDULER.CREATE_JOB ( job_name => 'ROLE_REFRESH.DAILY_SCHEMA_REFRESH_PRIVS' ,start_date => TO_TIMESTAMP_TZ('2014/03/18 09:32:40.131457 America/Los_Angeles','yyyy/mm/dd hh24:mi:ss.ff tzr') ,repeat_interval => 'FREQ=DAILY;BYHOUR=1;BYMINUTE=0;BYSECOND=0' ,end_date => NULL ,job_class => 'DEFAULT_JOB_CLASS' ,job_type => 'PLSQL_BLOCK' ,job_action => 'begin ROLE_REFRESH.REFRESH_SCHEMA_PRIVS (''TEST_SCHEMA'', ''TEST_SCHEMA_RO_ROLE'', ''RO''); ROLE_REFRESH.REFRESH_SCHEMA_PRIVS (''TEST_SCHEMA'', ''TEST_SCHEMA_RW_ROLE'', ''RW''); end;' ,comments => 'Job to refresh the schema level privileges daily' ); SYS.DBMS_SCHEDULER.SET_ATTRIBUTE ( name => 'ROLE_REFRESH.DAILY_SCHEMA_REFRESH_PRIVS' ,attribute => 'RESTARTABLE' ,value => FALSE); SYS.DBMS_SCHEDULER.SET_ATTRIBUTE ( name => 'ROLE_REFRESH.DAILY_SCHEMA_REFRESH_PRIVS' ,attribute => 'LOGGING_LEVEL' ,value => SYS.DBMS_SCHEDULER.LOGGING_OFF); SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL ( name => 'ROLE_REFRESH.DAILY_SCHEMA_REFRESH_PRIVS' ,attribute => 'MAX_FAILURES'); SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL ( name => 'ROLE_REFRESH.DAILY_SCHEMA_REFRESH_PRIVS' ,attribute => 'MAX_RUNS'); SYS.DBMS_SCHEDULER.SET_ATTRIBUTE ( name => 'ROLE_REFRESH.DAILY_SCHEMA_REFRESH_PRIVS' ,attribute => 'STOP_ON_WINDOW_CLOSE' ,value => FALSE); SYS.DBMS_SCHEDULER.SET_ATTRIBUTE ( name => 'ROLE_REFRESH.DAILY_SCHEMA_REFRESH_PRIVS' ,attribute => 'JOB_PRIORITY' ,value => 3); SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL ( name => 'ROLE_REFRESH.DAILY_SCHEMA_REFRESH_PRIVS' ,attribute => 'SCHEDULE_LIMIT'); SYS.DBMS_SCHEDULER.SET_ATTRIBUTE ( name => 'ROLE_REFRESH.DAILY_SCHEMA_REFRESH_PRIVS' ,attribute => 'AUTO_DROP' ,value => FALSE); SYS.DBMS_SCHEDULER.ENABLE (name => 'ROLE_REFRESH.DAILY_SCHEMA_REFRESH_PRIVS'); END; /