Refresh roles in database scheduler job

So take a schema name hma_prod and it has several tables and new tables are being added frequently. Users who need read access to these schemas will assigned to HMA_PROD_RO_ROLE.

It is a pain to grant each and every table every time you add a new one. So i set up this daily scheduler job that runs every night and refreshes roles.

For this i created a schema with dba role with access to all access in database.

Create the following procedure in the schema

CREATE OR REPLACE PROCEDURE HISNA_DBA.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;
/

After the procedure is created.. create a scheduler job

BEGIN

  SYS.DBMS_SCHEDULER.CREATE_JOB

    (

       job_name        => 'HISNA_DBA.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

   HISNA_DBA.REFRESH_SCHEMA_PRIVS (''HMA_PROD'', ''HMA_PROD_RO_ROLE'', ''RO'');

end;'

      ,comments        => 'Job to refresh the schema level privileges daily'

    );

  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE

    ( name      => 'HISNA_DBA.DAILY_SCHEMA_REFRESH_PRIVS'

     ,attribute => 'RESTARTABLE'

     ,value     => FALSE);

  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE

    ( name      => 'HISNA_DBA.DAILY_SCHEMA_REFRESH_PRIVS'

     ,attribute => 'LOGGING_LEVEL'

     ,value     => SYS.DBMS_SCHEDULER.LOGGING_OFF);

  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL

    ( name      => 'HISNA_DBA.DAILY_SCHEMA_REFRESH_PRIVS'

     ,attribute => 'MAX_FAILURES');

  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL

    ( name      => 'HISNA_DBA.DAILY_SCHEMA_REFRESH_PRIVS'

     ,attribute => 'MAX_RUNS');

  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE

    ( name      => 'HISNA_DBA.DAILY_SCHEMA_REFRESH_PRIVS'

     ,attribute => 'STOP_ON_WINDOW_CLOSE'

     ,value     => FALSE);

  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE

    ( name      => 'HISNA_DBA.DAILY_SCHEMA_REFRESH_PRIVS'

     ,attribute => 'JOB_PRIORITY'

     ,value     => 3);

  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL

    ( name      => 'HISNA_DBA.DAILY_SCHEMA_REFRESH_PRIVS'

     ,attribute => 'SCHEDULE_LIMIT');

  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE

    ( name      => 'HISNA_DBA.DAILY_SCHEMA_REFRESH_PRIVS'

     ,attribute => 'AUTO_DROP'

     ,value     => FALSE);

  SYS.DBMS_SCHEDULER.ENABLE

    (name                  => 'HISNA_DBA.DAILY_SCHEMA_REFRESH_PRIVS');

END;

/

Please note modify the role name according to your requirement. You can add as many roles as you want just seperating with ;