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
:
AUDIT audit_option
[ON schema.object]
[BY username]
[BY { SESSION | ACCESS } ]
[WHENEVER {SUCCESSFUL | NOT
SUCCESSFUL } ]
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. Create a sample table to demonstrate auditing,
create table sesitive_data(
emp_name varchar2(40),
ssn
varchar2(11),
salary
number );
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:
AUDIT SELECT, INSERT, UPDATE, DELETE
on SENSITIVE_DATA;
The steps to view audit information
for all examples is explained latter
4. Audit every time a user connects
to the database as the WAITE account by running: .
AUDIT SESSION BY WAITE;
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
AUDIT DELETE ON SENSITIVE_DATA WHENEVER
NOT SUCCESSFUL;
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.
View audit informationTechnique
Look in the AUD$ table, known as the audit trail, which contains all auditing information, or look at the files in the AUDIT_FILE_DEST directory. When your Oracle8 database is created, the CATAUDIT.SQL script, located in the $ORACLE_HOME/rdbms/admin directory, is run through the CATALOG.SQL script. These scripts create several data dictionary views that make analyzing the AUD$ table easier, such as DBA_AUDIT_TRAIL (all audits), DBA_AUDIT_SESSION (session audits), and DBA_AUDIT_OBJECT (object and statement audits). By querying DBA_OBJ_AUDIT_OPTS, you can determine the audit options for all objects. By querying DBA_PRIV_AUDIT_OPTS, you can determine the audit options for sessions and privileges.
Steps
1. Connect to SQL*Plus as the WAITE account. If you have not already created the SENSITIVE_DATA table and AUDIT commands from How-To 8.8, do so now.
2. Ensure that auditing is working. The SYS.AUD$ table contains every audit entry. Because nothing has been done to create an audit entry, there should be nothing in the table. Do a count on the table to be sure:
SQL> SELECT COUNT(*) FROM SYS.AUD$;
COUNT(*)
--------
0Generate the first audit record by connecting to SQL*Plus again as the WAITE user. Step 4 from How-To 8.8 enabled auditing for each time someone connects as the WAITE user account. Again, do a count on the SYS.AUD$ table:
SQL> SELECT COUNT(*) FROM SYS.AUD$;
COUNT(*)
--------
13. Create an additional audit record by running
SELECT * FROM SENSITIVE_DATA ;
This script will SELECT from the SENSITIVE_DATA table, adding an audit trail entry.4. At this point, two records are in the audit trail. To see the records, you can select from the DBA_AUDIT_SESSION view for session audits, and the DBA_AUDIT_OBJECT view for object audits. Load CHP8_33.SQL into the SQL buffer, and run it.
SELECT USERNAME, TERMINAL, ACTION_NAME,
TO_CHAR (LOGOFF_TIME, ‘DD-MON-YYYY HH:MI:SS’) LOGOFF_TIME
FROM SYS.DBA_AUDIT_SESSION
/
USERNAME TERMINAL ACTION_NAME LOGOFF_TIME -------- -------- ----------- ----------- WAITE WAITE LOGON 10-JAN-1998 03:15:33 SQL>
This shows all the session auditing. Now, select from the DBA_AUDIT_OBJECT view. Load CHP8_34.SQL into the SQL buffer, and run it.
SELECT USERNAME, TERMINAL,
ACTION_NAME, OBJ_NAME,
TO_CHAR (TIMESTAMP, ‘DD-MON-YYYY HH:MI:SS’) TIME
FROM SYS.DBA_AUDIT_OBJECT
/
USERNAME TERMINAL ACTION_NAME OBJ_NAME TIME -------- -------- ----------- -------- ---- WAITE ARI KAPLAN SESSION REC SENSITIVE_DATA 19-SEP-1997 12:47:26 How It Works
Step 1 repeats How-To 8.8, if necessary. Step 2 ensures that auditing is functioning by counting from the SYS.AUD$ table, adding an auditing record by reconnecting as WAITE, and by counting again from the SYS.AUD$ table. Step 3 creates another audit record by selecting from the SENSITIVE_DATA table. Step 4 shows the session audit data by selecting from the DBA_AUDIT_SESSION data dictionary view. It then shows the object audit data by selecting from the DBA_AUDIT_OBJECT data dictionary view.
Comments
This How-To explains how to view data that has been created in the audit trail. How-To 8.10 shows how to delete audit trail entries. To determine which object, statements, and privileges are being audited, you can query the DBA_OBJ_AUDIT_OPTS view, the ALL_DEF_AUDIT_OPTS view, or the DBA_PRIV_AUDIT_ OPTS view.
Delete audit informationTechnique
To disable a particular auditing event, but still enable auditing to continue for other auditing actions, use the NOAUDIT command. NOAUDIT is essentially the opposite of AUDIT. It turns off all object, statement, and system privilege auditing based on the auditing options selected. The syntax of the NOAUDIT command is.
To disable auditing altogether from the database, modify the initialization file, and change the line with AUDIT_TRAIL from DB or OS to NONE. If you want to remove the auditing data dictionary views, run the $ORACLE_HOME/RDBMS/ADMIN/CATNOAUD.SQL script. To delete the audit data, you must either TRUNCATE the AUD$ data dictionary table or delete the unnecessary data from the table.
Steps
1. Connect to SQL*Plus as the WAITE account. If you have not already created the SENSITIVE_DATA table and AUDIT commands from How-To 8.8, do so now. Also, if you have not created audit trail entries from How-To 8.9, do so now.
2. Remove the auditing for people connecting to the database as the WAITE user account by running
NOAUDIT session;3. Remove the auditing from the SENSITIVE_DATA table by running
NOAUDIT SELECT, INSERT, UPDATE, DELETE ON SENSITIVE_DATA;4. To delete auditing data within the database, use the TRUNCATE command on the SYS.AUD$ data dictionary table. This is the only data dictionary table that you should modify. Load CHP8_37.SQL into the SQL buffer and run it.
TRUNCATE TABLE SYS.AUD$ ;
5. To stop auditing altogether from the database, you must modify your initialization file and change the parameter AUDIT_TRAIL to NONE.
How It Works
Step 1 creates all objects, audit options, and audit trail entries. Step 2 removes auditing from users connecting as the WAITE user account. Step 3 removes auditing of SELECT, INSERT, UPDATE, and DELETE for the SENSITIVE_DATA table. Step 4 shows how to delete auditing data from the audit trail. Step 5 shows how to stop auditing altogether for the entire database.
Comments
To prevent the data in the audit trail for growing without bounds and taking up disk space, you can either delete data, turn off auditing options, or turn off auditing altogether. By default, Oracle8 audits every startup, shutdown, and connections as DBA user accounts into operating system files. The files are located by default in $ORACLE_HOME/RDBMS/AUDIT in UNIX, and \ORANT\DATABASE in Windows NT.