8.8 How do I…Enable database auditing?Problem
I would like to closely monitor activity within my database. It is important to know how often a particular user logs in and I’d also like to know when a specific table is updated or altered. It is also important to know when another user account tries to select data and fails. How to I enable database auditing?
Technique
By using the AUDIT command, one can audit a wide range of activities within the database. One-hundred and fifty-nine different actions can be audited in Oracle8, which is too numerous to explain individually in this How-To. The actions fall into three general categories: object auditing (whenever an object is referenced by a user), privilege auditing (whenever a privilege is used or fails an attempt to be used), and statement auditing (when a statement is issued). The syntax of the AUDIT command is shown in Figure 8.29.
Object auditing can involve auditing statements on objects, such as CREATE INDEX, ALTER TABLE, GRANTs on an object, or DROP PUBLIC DATABASE LINK. Privilege auditing can involve auditing any system privilege, such as the SELECT ANY SEQUENCE privilege, the ALTER SYSTEM privilege, or the ALTER USER privilege. Statement auditing can record DDL or DML statements, based on a type of database object.
All audit statements can be specified for a particular user or all users with the BY username clause. If desired, they can also be specified to record to the audit trail only if a statement succeeds or fails, by the WHENEVER SUCCESSFUL or WHENEVER NOT SUCCESSFUL clause. The BY ACCESS and BY SESSION clause indicates how often the audit trail is updated by one session between login and logout. For example, if a user issues ten SELECT statements against an audited table, then one record will be recorded in the audit trail if BY SESSION was selected for the audit, and ten records if BY ACCESS.
Steps
1. To enable database auditing, you must first modify your initialization file and change the parameter AUDIT_TRAIL. To send the auditing events to the database audit trail, change the AUDIT_TRAIL parameter to DB. The audit records are then inserted into the AUD$ data dictionary table in the SYS user account. To send the auditing events to the operating system trail, change the AUDIT_TRAIL parameter to OS and the audit files will be located in the AUDIT_FILE_DEST directory. This directory by default is $ORACLE_HOME/RDBMS/AUDIT in UNIX, and \ORANT\DATABASE in Windows NT. The database must be shut down and restarted for auditing to take effect.
2. Run SQL*Plus and connect as the WAITE user account. Run CHP8_28.SQL to create a sample table to demonstrate auditing, as shown in Figure 8.30. The table contains Social Security and salary information, and by auditing the table, the database administrator can determine when and how users access and modify the table.
3. Audit all SELECT, INSERT, UPDATE, and DELETE statements made against the SENSITIVE_DATA table by any user by running CHP8_29.SQL, as shown in Figure 8.31. The steps to view audit information for all examples in this How-To is explained in How-To 8.9.
4. Audit every time a user connects to the database as the WAITE account by running CHP8_30.SQL, as shown in Figure 8.32. This is good to track the time the WAITE user account logs in and logs out. If you are the only user with the WAITE password, this is also a good method to determine whether anyone else logs in to the database.
5. Audit each time a user attempts to DELETE records from the SENSITIVE_DATA table and fails, by running CHP8_31.SQL, as shown in Figure 8.33.
How It Works
Step 1 enables auditing within the database by modifying the initialization parameter file to set AUDIT_TRAIL to DB. Step 2 creates the SENSITIVE_DATA table to demonstrate auditing. Step 3 enables auditing for all SELECT, INSERT, UPDATE, and DELETE statements made against the SENSITIVE_DATA table by any user. Step 4 audits every time a user connects to the database as the WAITE account. Step 5 audits each time a user attempts to DELETE records from the SENSITIVE_DATA table and fails.
Comments
The SYS and INTERNAL accounts are never audited. If a user rolls back a transaction, the statement is still audited. A user account can only audit activity within their own account, unless they have AUDIT ANY privileges, and the DBA role contains them. Operating system audit trails are automatically generated in the $ORACLE_HOME/RDBMS/AUDIT directory, which keeps track of all instance startups, instance shutdowns, and connections to the database as a DBA user account. Even if auditing is not on, these files will be created. Be sure to periodically purge these files, or the file system eventually fills up. Many database administrators, even advanced DBAs, don’t know about these files.