auditing – what the hell is going on in my database

When I came into the office today, a colleague of mine was already waiting for me – one of our daily datapump exports has failed again. When looking into the log, I found this error message:

ORA-01466: unable to read data - table definition has changed

ok, this shouldn’t happen since the application is not doing ddl (hopefully), so who else could it be? But from ancient times I could remember that oracle is auditing several activities… but when I started google to get the name of the audit table, I was confused: it seems in Oracle 12c there are 2 different types auf auditing.

1. old style

ok, this is maybe the method you already know… for auditing all ddl statements on all tables, you just have to enter

audit table

… very simple – but boring 😉

2. new style

the new style is a bit more complex – you have to define a policy first and then activate it. I’m no audit expert, but as far as I could gather the information, the new style is very powerful and can be configured more accurate, but some features of the old method are missing.

Ok, but back to my problem… to make a long story short, by default ddl statements are not captured but could be enabled with both methods. So i decided to use the new way…

so I first checked what is already in my audit table…

SELECT * FROM unified_audit_trail WHERE event_timestamp > sysdate-1 ;

… and waited. I found several posts complaining about slow performance of the audit table when there are too many entries. So first step is a cleanup of the current table. you can do it manually, but i decided to create a scheduler job doing it regularly… and when I’m doing a cleanup job, I’m going to cleanup also the old style audit logs… so after some try and error my job is looking like this:

BEGIN
  DBMS_SCHEDULER.CREATE_JOB
    (
       job_name        => 'PURGE_ALL_AUDIT_LOGS'
      ,start_date      => sysdate
      ,repeat_interval => 'freq=daily; byhour=1; byminute=0; bysecond=0'
      ,job_type        => 'PLSQL_BLOCK'
      ,job_action      => 'BEGIN
  DBMS_AUDIT_MGMT.INIT_CLEANUP(AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL, DEFAULT_CLEANUP_INTERVAL => 24);
  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, LAST_ARCHIVE_TIME => SYSDATE-90);
  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, LAST_ARCHIVE_TIME => SYSDATE-90);
  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,  LAST_ARCHIVE_TIME => SYSDATE-90);
  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD, LAST_ARCHIVE_TIME => SYSDATE-90);
  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,      LAST_ARCHIVE_TIME => SYSDATE-90);
  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML,     LAST_ARCHIVE_TIME => SYSDATE-90);
  DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL, TRUE);
END;');

first we have to initialize the cleanup… this is not necessary for the new unfied audit logs, but for the old style. then we have to define the retention time for all audit types seperately, and last but not least do the purge itself… easy, isn’t it? After running the job once I was able to query the audit table… ok, next step: implement DDL auditing.
First of all you should know that the audit rules are read during the login… if you are wondering why the audit is not working: try logout and login again 😉 I have no idea if shared server processes are different…
So, back to the audit policy… in general there are 3 different methods to define when a command should be logged.

1. defining the used privileges

when someone is not owning a table but is able to read or modify it because of special system privileges (like ‘select any table’)

2. defining actions

you can define the action that should be logged. An action could be ‘create table’ or ‘update table’

3. defining the used role

when the right to do a special operation is permitted via a role, you can just specify it.

Since I want to audit all DDL regardless why the user is allowed to do it, my policy is like this:

CREATE audit policy ddl ACTIONS CREATE TABLE,CREATE INDEX,ALTER TABLE,ALTER INDEX,DROP INDEX,DROP TABLE,TRUNCATE TABLE,RENAME,LOCK TABLE;

if you want to narrow it a bit, you can specify the object that should be monitored…

CREATE audit policy test ACTIONS delete on hr.employees;

And last but not least it’s possible to add custom filter criteria…

CREATE audit policy test ACTIONS delete on hr.employees WHEN 'SYS_CONTEXT(''USERENV'', ''USERNAME'') != ''HR''';

Finaly you have to enable the policy!

audit policy ddl;

thats it… hope I could give you a simple and easy to understand introduction into the auditing feature of oracle.

Benjamin