Category Archives: Auditing

How to check if auditing is turned on and privilege auditing is turned on?

Posted on by
1

If AUDIT_TRAIL is not set to NONE then using the following SQL one can find the statements that have auditing turned on.

SQL> SELECT * FROM dba_stmt_audit_opts union SELECT * FROM dba_priv_audit_opts;

USER_NAME PROXY_NAME
—————————— ——————————
AUDIT_OPTION SUCCESS FAILURE
—————————————- ———- ———-

ALTER ANY PROCEDURE BY ACCESS BY ACCESS

ALTER ANY TABLE BY ACCESS BY ACCESS

ALTER DATABASE BY ACCESS BY ACCESS
..

Advertisements

How to enable auditing?

Posted on by
Reply

By setting the parameter “AUDIT_TRAIL” to “DB” or “OS”, one can enable auditing in the database. To change the value of the parameter you would need to bounce the database for the new value to take into effect. The default value of the parameter is “NONE” in which database auditing is disabled. Even if “AUDIT_TRAIL” is enabled/disabled, Oracle will write OS audit trail at OS audit trail at startup and shutdown of instance, and connections made by SYSOPER and SYSDBA.

If “AUDIT_TRAIL” is set to “OS”, the parameter “AUDIT_FILE_DEST” is the location where audit files are created. The default value of this parameter is “$ORACLE_HOME/rdbms/audit”.

If “AUDIT_TRAIL” is set to “DB”, oracle writes the auditing information in “SYS.AUD$” table. By default the table is created in SYSTEM tablespace.

If one tries to open a database in read only mode and AUDIT_TRAIL is set to DB, Oracle will generate an error stating the audit_trail is incompatible with database open mode.
SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-16006: audit_trail destination incompatible with database open mode