When a new table is created , a SELECT priveleges on that TABLE is added to a ROLE.
====================================================================================
--Create the tigger and the hr_s role under sys.
CREATE OR REPLACE TRIGGER ddl_trig
AFTER DDL
ON DATABASE
DECLARE
main_sql_text varchar2(100);
job_name_ varchar2(30);
job_type_ varchar2(30);
job_action_ varchar2(4000);
sql_text varchar2(400);
job_id_ varchar2(30);
start_date_ timestamp;
BEGIN
if (ora_sysevent='CREATE' and ora_dict_obj_type='TABLE' and ora_dict_obj_owner='HR' )then
sql_text := 'grant select on '||ora_dict_obj_owner||'.'||ora_dict_obj_name||' to hr_s';
select to_char(systimestamp,'DDMMYYYYHH24MISS') into job_id_ from dual;
select SYSTIMESTAMP + interval '1' minute into start_date_ from dual;
main_sql_text:=''''||sql_text||'''';
job_name_:='job'||job_id_;
job_type_:='PLSQL_BLOCK';
job_action_:='begin execute immediate('||main_sql_text||'); end;';
execute immediate ('begin DBMS_SCHEDULER.create_job (job_name=> :a,job_type=> :b,job_action => :c,start_date=> :d,repeat_interval => NULL,end_date=> NULL,enabled=>TRUE,comments=> NULL); end;') using job_name_,job_type_,job_action_,start_date_;
end if;
END ddl_trig;
/
--Grant the role on a privelges users.
