Dynamically keeping a role up to date

Sometimes (mainly in dev environments) it's nice to have roles dynamically updated so developers dont have to keep remembering to do it, proper scripts can later be built/updated for formal release into test environments.

To dynaically keep a role #DUMMYROLE' up do date every time the dummy schema 'ALIGNE' is changed we can create the following trigger in the SYS schema to keep the role up to date.

CREATE OR REPLACE TRIGGER ROLE_UPDATER

BEFORE CREATE OR DROP OR ALTER ON DATABASE

DECLARE

l_job number;

str varchar2(4000);

BEGIN

    IF ora_sysevent IN ('CREATE', 'ALTER') AND ora_dict_obj_owner in ('ALIGNE') AND ora_dict_obj_type in ('TABLE','VIEW')

      THEN

str := 'execute immediate '||''''||'GRANT SELECT ON '||ora_dict_obj_owner||'.'||ora_dict_obj_name||' to dummyrole'||''''||';';

dbms_job.submit( l_job, str);

  END IF;

END;

Note - job_queue_processes must be at least 1 for this to work.....