Oracle Auditing FAQ

Q: How do I enable auditing?
A: Oracle auditing is enabled out of the box if you created your database using DBCA. If you created it manually, it may be disabled. To enable auditing, alter the audit_trail init parameter to "db" or "db, extended".
    Example:
       show parameter audit_trail
       alter system set audit_trail = db,extended scope = spfile;
       shutdown immediate;
       startup open;


Q: How do I view the audit trail?
A: Select from DBA_AUDIT_TRAIL or DBA_COMMON_AUDIT_TRAIL. DBA_COMMON_AUDIT_TRAIL is useful if you also have fine grained auditing on some objects. All of these are views, the actual base table is SYS.AUD$


Q: How do I see which actions are being audited?
A: Refer to the DBA_STMT_AUDIT_OPTS, DBA_PRIV_AUDIT_OPTS and DBA_OBJ_AUDIT_OPTS views.


Q: How do I audit only a specific table?
A: Enable auditing on that table with the audit command.
    Example:
       -- audit every time the user does DML on the emp table  
       audit insert, update, delete on emp by access;
       -- audit once per session if user views salary info
       audit select on emp by session;


Q: How do I audit the actual SQL text? I cannot see the queries in my audit log.
A: Normally, Oracle defaults to "db" audit level, which logs the activity, but not the SQL text or bind variables. To enable this,  set audit_trail parameter to "db,extended", and remember to restart Oracle. Be advised, auditing too much with extended enabled will cause your audit trail to grow quickly.

     select action, sql_text from dba_audit_trail


Q: How do I stop auditing certain things?
A: Use the NOAUDIT command. For every "AUDIT ..." command, you can issue a NOAUDIT command to reverse action, except NOAUDIT command does not take "BY ACCESS" or "BY SESSION" as an option.


Q: I issued "AUDIT ALL BY SCOTT BY ACCESS" but I still don't see an audit entry for the user's queries.
A: AUDIT ALL does not enable SELECT auditing. Use more specific audit statements like "AUDIT SELECT ON <tab> BY <user> BY ACCESS"


Q: I did everything correct to enable auditing, and issued AUDIT SELECT for a table, then ran a teste with "SELECT * FROM SCOTT.EMP" and still see no audit trail.
A: Probably you are testing as SYS or SYSDBA, which is not audited unless explicitly enabled with init parameter AUDIT_SYS_OPERATIONS = TRUE. Try logging in as a regular user to test.


Q: How do I clean out the audit trail?
A: Traditional approach: Delete or truncate the SYS.AUD$ table. As of 11.1.0.7 and patch level 10.2.0.5 you can use the new DBMS_AUDIT_MGMT package, namely the methods CLEAN_AUDIT_TRAIL and SET_LAST_ARCHIVE_TIMESTAMP .
See: http://download.oracle.com/docs/cd/E13850_01/doc.102/e13841/avadm_app_d_audit_mgmt.htm


Q: My audit trail is big. Can I move it to its own tablespace?
A1: With versions prior to 10gR2 the answer is No, you should not. Oracle does not support moving SYS.AUD$ out of the SYSTEM tablespace. You should simply add an extra datafile, or increase the size of the SYSTEM tablespace, or decrease the number of operations that are audited. The best practice for a heavily audited system is to setup a scheduled process to archive audit data into an archive tablespace, and/or delete the SYS.AUD$ table.
A2: With the latest 10gR2 patch level (10.2.0.5+) as well as 11g (11.1.0.7+) you can finally use the new DBMS_AUDIT_MGMT package, namely the DBMS_AUDIT_MGMT.SET_AUDIT_ TRAIL_LOCA TION method to move SYS.AUD$ to a user supplied tablespace. This is officially supported by Oracle.
See: http://download.oracle.com/docs/cd/E13850_01/doc.102/e13841/avadm_app_d_audit_mgmt.htm


Q: How do I archive audit data?
A: Just like any table, use export, data pump, or copy to another table. With recent versions of Oracle (10.2.0.5+ and 11.1.0.7+) you can also use the DBMS_AUDIT_MGMT package to archive and purge the audit trail.
See: http://download.oracle.com/docs/cd/E13850_01/doc.102/e13841/avadm_app_d_audit_mgmt.htm


Q: I'd like to keep some of the audited events, but clean out others.
A: You can treat SYS.AUD$ just like any table, delete certain records while keeping others. Just do not drop or move the table.


Q: What happens if the audit trail gets full?
A: Depending on what is being audited, it could potentially lock your database. Connections will hang, statements will hang, until Oracle can write to the audit trail. Be very careful about what you audit, and how much space you allocate for growth in the SYSTEM tablespace. Use the AUTOEXTEND option on the system tablespace. Recent patches have added the DBMS_AUDIT_MGMT package to schedule and control audit purge jobs and archiving from a standard PL/SQL interface.


Q: I want to audit all changes to a table, I want to save the old and new values for every record.
A: If you REALLY want to do this, use a trigger.

    CREATE OR REPLACE TRIGGER AUD_SALARY AFTER UPDATE ON EMP FOR EACH ROW ...
                      

Select all Open in new window

Q: Can I audit only access to certain columns? Or when certain values are used?
A: Yes, with 9i or later, using a Fine Grained Auditing (FGA) policy
Example: Audit only the salary column of EMP table.

   dbms_fga.add_policy (
                            object_schema=>'SCOTT',
                            object_name=>'EMP',
                            policy_name=>'EMP_ACCESS',
                            audit_condition => NULL,  -- TRUE
                            audit_column    => 'SAL',
                            statement_types => 'SELECT,INSERT,UPDATE,DELETE'
                         );
                      

Select all Open in new window

Q: I didn't have auditing enabled, but I want to see what changed yesterday. Is there any way I can do this.
A: Use Log Miner. Refer to the Oracle documentation to setup Log Miner. You need to have archive logs to see more history than your online logs.


Q: Can I audit where the user logged in from?
A: DBA_AUDIT_TRAIL contains various details, including the user's machine, username, and terminal.


Q: I want to audit my DBAs, but I need to keep the audit trail safe from tampering.
A: Use OS level auditing (set audit_trail = os). The audit trail is written to an OS file. Audit SYS user by setting AUDIT_SYS_OPERATIONS = TRUE. Limit access to the Oracle OS user so the audit trail is not under the control of someone logging in remotely with DBA role. Don't give out SYSDBA privileges. Users with SYSDBA access can clear the SYS.AUD$ table, however, even delete's of the audit trail will be audited, so suspicious activity will show to some extent.


Q: I have set audit_trail = os. Where is my OS audit trail?
A: The default is $ORACLE_HOME/rdbms/audit, however you can set this with the AUDIT_FILE_DEST init parameter.