Granting Oracle Schema Permissions (Objects not created yet) – Part 2

After presenting at Collaborate 16 about granting schema permissions, a colleague – Frank Pound send me the following that can also be used to perform the trigger only on “Tables”:

https://community.oracle.com/thread/402826?start=0&tstart=0

create or replace trigger trigger_grant_dml

after create on schema
declare
v_job number;
v_todo varchar2(200);
begin
     if ora_sysevent = 'CREATE' and ora_dict_obj_type = 'TABLE' then
     v_todo:='execute immediate ''grant select, insert, update, delete on '||ora_dict_obj_name||' to my_new_role'';';
     dbms_job.submit(job=>v_job, what=>v_todo);
  end if;
exception
  when others then
  null;
end;
/

2016_1198_gangler_ppt

Please contact me with any questions - mjgangler@gmail.com

Collaborate 16 !

I will be presenting 3 presentations at this years Collaborate 16 in Las Vegas.

Monday – April 11 – 12 – 12:30 – Palm B – Using Puppet and RPM’s to deploy EM12C Agents

Wednesday – April 13 – – 12:00 – 12:30 – Jasmine B – Granting Oracle Schema Permissions When Objects not Created Yet !

Thursday – April 14 – 8:30 am – 9:30 am – Palm  D – Optimize and Simplify Oracle 12C RAC using dNFS, ZFS and OISP

2016_1197_gangler_pdf

 

2016_1198_gangler_ppt

 

2016_1201_gangler_ppt