Auditing

Whenever you work with large databases where multiple users maintain the data inside, you will need a way how to audit the activity inside. Oracle Audit allows you to monitor and record all kinds of user activities; from database startups to the creation of objects to the access to a particular table column at a specified period of time.

There are two options Oracle stores audit data:

You can, of course, decide whether you want to monitor the whole instance or just a specific table. I will now describe both options.

Monitoring the whole instance might be a bit of overkill and therefore Oracle offers an individual auditing option. With this, you can easily monitor individual objects based on

You can monitor successful or/and unsuccessful operations, such as DELETE, SELECT, INSERT.

Now, enough of theory and let’s have a little bit more practical part 😉

Check whether the audit trail is enabled and what mode is on:

SELECT value
FROM v$parameter
WHERE name = 'audit_trail';

To enable/disable SELECT tracking for a particular table run these commands:

-- the clause "BY ACCESS" will audit all successful and unsuccessful operations
AUDIT SELECT ON my_schema.secret_table BY ACCESS;

-- to disable the auditing
NOAUDIT SELECT ON my_schema.secret_table;

To track any table changes:

-- audits all DELETE operations on all tables
AUDIT DELETE ANY TABLE BY ACCESS;

-- audits only unsuccessful DELETE operations on all tables
AUDIT DELETE ANY TABLE BY ACCESS WHENEVER NOT SUCCESSFUL;

-- audis only successful DELETE operations on all tables
AUDIT DELETE ANY TABLE BY ACCESS WHENEVER SUCCESSFUL;

You can of course easily combine multiple operations in one command:

AUDIT SELECT TABLE, DELETE TABLE, EXECUTE PROCEDURE BY ACCESS;

If you are suspicious of specific users, you can spy on them too 🙂

-- check what JOHN_DOE is selecting
AUDIT SELECT TABLE BY john_doe BY ACCESS;

-- monitor all activity of JOHN_DOE
AUDIT ALL BY john_doe BY ACCESS;

List of database catalogs where you can find audit logs:

-- logs for LOGON / LOGOFF actions
SELECT * FROM dba_audit_session;

-- complete logs for all actions defined in the AUDIT_TRAIL method
SELECT * FROM dba_audit_trail;
SELECT * FROM sys.aud;

-- logs for monitored objects 
SELECT * FROM dba_audit_object;

-- logs for DCL
SELECT * FROM dba_audit_statement;

To check options for monitored objects, permissions, and statements:

SELECT * FROM dba_obj_audit_opts;
SELECT * FROM dba_priv_audit_opts;
SELECT * FROM dba_stmt_audit_opts;

Fine-grained audit (FGA)

There is another way to monitor activity in the database instance. It uses the DBMS_FGA package. Due to the extreme power, you have to have AUDIT_ADMIN privilege and EXECUTE on this package to use it. This can help you monitor a single column for a specific value.

This example will help you audit all UPDATE commands where the amt_order was lower than 1000.

BEGIN 
 DBMS_FGA.ADD_POLICY (
   object_schema      =>  'oracle_world', 
   object_name        =>  'orders', 
   policy_name        =>  'order_amt_monitor', 
   audit_condition    =>  'amt_order < 1000', 
   audit_column       =>  'amt_order', 
   handler_schema     =>   NULL, 
   handler_module     =>   NULL, 
   enable             =>   TRUE, 
   statement_types    =>  'UPDATE',  -- you can use multiple commands as well -> statement_types => 'INSERT, UPDATE'
   audit_column_opts  =>   DBMS_FGA.ANY_COLUMNS,
   policy_owner       =>  'oracle_world'
 ); 
END;

To manipulate the policy:

BEGIN
  DBMS_FGA.DISABLE_POLICY (
                            object_schema => 'oracle_world',
                            object_name   => 'orders',
                            policy_name   => 'order_amt_monitor');
  DBMS_FGA.DROP_POLICY (
                        object_schema => 'oracle_world',
                        object_name   => 'orders',
                        policy_name   => 'order_amt_monitor');
  DBMS_FGA.ENABLE_POLICY ( 
                          object_schema => 'oracle_world', 
                          object_name   => 'orders', 
                          policy_name   => 'order_amt_monitor',
                          enable        => TRUE);
END;

All operations logged by DBMS_FGA will be stored in this table:

SELECT *
FROM sys.fga_log$;

Conclusion

Oracle Audit is a solid tool for having a complete overview of what is going on in your database. You can practically monitor every single move of your users. However, this all comes with a drawback. The more information you intend to store, the more data will be written and stored somewhere.

You should consider if you really need a thorough audit because you might end up with clogged log tables and serious performance issues.