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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s