Skip to Main Content
  • Questions
  • Add tables automatically to audit policy


Question and Answer

Connor McDonald

Thanks for the question, Michael.

Asked: September 02, 2022 - 4:30 pm UTC

Last updated: September 06, 2022 - 5:45 am UTC

Version: Oracle Database 19c Enterprise Edition Release - Production

Viewed 100+ times

You Asked

Hi Tom,

Is there a way to automatically add tables a user creates to an audit policy?
I'm thinking a trigger could do this, but can we do it without giving the user the AUDIT_ADMIN role?

What I am really after is a way to audit any action against a schema.

create audit policy audit_schema_names_objects
  actions all on schema_name.*
  only toplevel;

and Connor said...

Yeah I think the best you can do it do some DDL triggering to capture this, eg

create or replace 
trigger tweak_auditing
after create on database
  -- lots of flexibility here in choosing what you want to log
  -- and when etc etc.
  if ora_dict_obj_type = 'TABLE' 
    and  ora_dict_obj_owner in ('....')
    and  dbms_utility.format_call_stack not like '%NIGHTLY%'  -- not the nightly maint jobs
    and  nvl(sys_context('USERENV','MODULE'),'x') != 'DBMS_SCHEDULER'  -- not jobs
    and  ... (whatever you want)
    -- tweak the audit policy
    execute immediate
      'alter audit policy my_policy add actions all on '||ora_dict_obj_owner||'.'||ora_dict_obj_name;
  end if;


  (1 rating)

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library