- 10 Sep, 2023
- 0 Comments
- 2 Mins Read
Oracle Database Auditing
Oracle Database Auditing
Oracle database auditing :
It allows you to monitor certain database actions happening inside the database.
Auditing also helps in looking at the actions performed against a particular table, schema or certain specific rows.
To check database auditing is enabled execute SHOW PARAMETER command
1 | Show parameter audit; |
The AUDIT_TRAIL parameter defines database auditing status. It is having following values
- None – Database auditing is disabled
- os – Enabled, audit logs are stored at OS level, not inside the database
- db – Enabled, audit records are stored inside database (SYS.AUD$ table). If we are creating database by DBCA Utility , then default it is DB else it is none.
- db,extended – Same as db but populates SQL_BIND & SQL_TEXT too
- xml – Enabled, audit records are stored at OS level in XML format
- xml,extended – Same as xml but populates SQL_BIND & SQL_TEXT too
1 | alter system set AUDIT_TRAIL=<status value>, extended scope=spfile; |
Please Note : Here status value indicates any of value mentioned above.
example :
1 | alter system set AUDIT_TRAIL= db, extended scope=spfile; |
If auditing is not enabled, the Oracle Database Adapter cannot retrieve the information about when the user last accessed the account.
To enable auditing , follow the below steps :
- Set the initialization parameter audit_trail to TRUE in the init.ora file (pfile).
- issue the following command at the SQL prompt: ALTER SYSTEM SET audit_trail=TRUE scope=SPFILE.
- To turn on the auditing for user log on as a user with admin authority and Issue the following command at the SQL prompt: AUDIT CONNECT.
- To verify that auditing is enabled on an instance, issue the following command at the SQL prompt: SHOW PARAMETER AUDIT_TRAIL.
P.S. : Any value except NONE or FALSE indicates that auditing is enabled.
AUDIT_FILE_DEST :
The AUDIT_FILE_DEST parameter defines the OS level location of the audit trail files. By default it is set to adump location.
All the SYS operations audit records goes to OS level into AUDIT_FILE_DEST location. audit_file_dest default Location is $ORACLE_HOME/rdbms/audit. The default value is: ORACLE_BASE / admin/ ORACLE_SID/adump.
This create files with the .aud extentionto audit operations.
If you set audit_sys_operations=true.
There is a separate file for every session that connects as SYSDBA.
1 2 3 4 5 6 7 | SQL> show parameter auditNAME TYPE VALUE----------------------- ----------- --------------audit_file_dest string /u01/app/oracle/admin/prim/auditaudit_sys_operations boolean TRUEaudit_trail string DB |