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 ;
