Role refresh and scheduler job script in oracle database

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

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;
/