Using Database Auditing - Enterprise DBA Part 1A: Architecture and Administration

Enabling and Disabling Database Auditing

Once you have decided what to audit, you set the AUDIT_TRAIL initialization parameter to enable auditing for the instance. This parameter indicates whether the audit trail is written to a database table or the operating system audit trail.

Syntax AUDIT_TRAIL = value

where value can be one of the following:

DB enables auditing and directs all audit records to the database audit trail (SYS.AUD$)

OS enables auditing and directs all audit records to the operating system audit trail (if permitted on the operating system) NONE disables auditing (This is the default value.)

® Database Auditing Audittrail Audit options Parameter file Enable database auditing DBA Specify audit options Database User Execute command Generate audit trail Review audit information Server process OS audit trail

... 18-30 Enterprise DBA Part 1A: Architecture and Administration

Enabling Database Auditing (continued)

Audit records will not be written to the audit trail unless the DBA has set the

AUDIT_TRAIL parameter to DB or OS. Although the SQL statements AUDIT and NOAUDIT can be used at any time, records will only be written to the audit trail if the DBA has set the AUDIT_TRAIL parameter in the initialization file.

Note: The Installation and Configuration Guide for your os system provides information on writing audit records to the OS audit trail.

Specifying Audit Options

Next, you set specific auditing options using the AUDIT command. With the AUDIT command, you indicate which commands, users, objects, or privileges to audit. You can also indicate whether an audit record should be generated for each occurrence or once per session. If an auditing option is no longer required, you can turn off the option with the NOAUDIT command.

Execution of Statements

When users execute PL/SQL and SQL statements, the server process examines the auditing options to determine if the statement being executed should generate an audit record. SQL statements inside PL/SQL program units are individually audited, as necessary, when the program unit is executed. Because views and procedures may refer to other database objects, several audit records may be generated as the result of executing a single statement.

Generating Audit Data

The generation and insertion of an audit trail record is independent of a user’s transaction; therefore, if a user’s transaction is rolled back, the audit trail record remains intact. Since the audit record is generated during the execute phase, a syntax error, which occurs during the parse phase, will not cause an audit trail record to be generated.

Reviewing Audit Information

Examine the information generated during auditing by selecting from the audit trail data dictionary views or by using an operating system utility to view the operating system audit trail. This information is used to investigate suspicious activity and to monitor database activity.

Events Audited on Request

You can specify the auditing options using the AUDIT command. These audit records are never generated by sessions established by the user SYS or connections as

INTERNAL. Connections by these users bypass certain internal features of the Oracle server to enable administrative operations to occur, such as database startup,

shutdown, and recovery. Statement Auditing

You can audit by using a type of SQL statement or by a type of object. The statement auditing example audits all CREATE, ALTER, and DROP USER statements for all users.

Statement auditing options are typically broad, auditing the use of several types of related actions per option. For example, AUDIT TABLE tracks several DDL statements regardless of the table on which they are issued. You can set statement auditing to audit selected users or every user in the database.

Enabling Auditing Options • Statement auditing

• Privilege auditing

• Schema object auditing

AUDIT select any table BY summit BY ACCESS; AUDIT user;

AUDIT user;

AUDIT LOCK ON summit.employee BY ACCESS WHENEVER SUCCESSFUL; AUDIT LOCK ON summit.employee BY ACCESS WHENEVER SUCCESSFUL;

... 18-32 Enterprise DBA Part 1A: Architecture and Administration

Privilege Auditing

Privilege auditing audits the use of system privileges. In the example in this slide, whenever SUMMIT uses the SELECT ANY TABLE privilege, an audit entry is generated; an entry will only be generated if SUMMIT queries tables belonging to other users, for which he has not received SELECT privileges. When auditing, owner privileges are checked first, then object privileges, and then system privileges. So if a user’s SELECT ANY TABLE privilege is being audited, and he selects from a table he owns, then the SELECT ANY TABLE privilege would not cause an audit record to be generated, because the user can select from the table using his ownership privilege. Schema Object Auditing

Schema object auditing audits statements performed on a specific schema object. In the example, an audit trail entry is generated when a user successfully executes the LOCK command on the object SUMMIT.EMPLOYEE.

Syntax

Use the following command to enable auditing options: Privilege or Statement Auditing

AUDIT {statement|system_priv}

[, {statement|system_priv} ]... [BY user [, user ]... ]

[BY {SESSION|ACCESS} ] [WHENEVER [NOT] SUCCESSFUL]

Syntax (continued)

Object Auditing

AUDIT statement [, statement ]... ON {[schema.]object|DEFAULT} [BY {SESSION|ACCESS}]

[WHENEVER [NOT] SUCCESSFUL]

where: statement specifies the SQL statement type or schema-object to audit

system_priv specifies the system privilege to audit schema.schema-object identifies the object chosen for auditing DEFAULT sets the specified object options as

default object options for subsequently created objects

user indicates to only audit the users in the list (If this clause is omitted, then all users’ activities are audited.)

BY SESSION causes the Oracle server to insert only one record per database object into the audit trail for each session, no matter how many SQL statements of the same type are submitted (This is the default, except for DDL.)

BY ACCESS causes the Oracle server to insert a record into the audit trail each time an audited statement is submitted (For Data Definition Language (DDL) statements, the Oracle server always audits by access.)

WHENEVER specifies that auditing is to be carried out only on successful or unsuccessful completion of SQL statements (The default is both.)

... 18-34 Enterprise DBA Part 1A: Architecture and Administration

Syntax (continued) Note

• Because audit records are generated during the execution phase, parse errors, such as TABLE OR VIEW DOES NOT EXIST, cannot be trapped by using the

WHENEVER UNSUCCESSFUL clause.

• Statement and privilege auditing options specified by the AUDIT command apply only to subsequent sessions, not to the current session. In contrast, changes to schema object audit options become effective for current sessions immediately. Disabling Auditing

Use the NOAUDIT statement to stop auditing chosen by the AUDIT command. Note: A NOAUDIT statement reverses the effect of a previous AUDIT statement. Note that the NOAUDIT statement must have the same syntax as the previous AUDIT statement and that it only reverses the effects of that particular statement. Therefore, if one AUDIT statement (statement A) enables auditing for a specific user, and a second (statement B) enables auditing for all users, then a NOAUDIT statement to disable auditing for all users reverses statement B, but leaves statement A in effect and continues to audit the user that statement A specified.

Viewing Auditing Results

The views listed above contain information from the audit trail. The following is an example that shows the audit records generated when a series of statements are executed:

SQL> SELECT username, obj_name, action_name, priv_used 2 FROM sys.dba_audit_object

3 WHERE owner = ’SUMMIT’ 4 AND obj_name = ’EMPLOYEE’;

USERNAME OBJ_NAME ACTION_NAME PRIV_USED --- --- --- --- SUMMIT EMPLOYEE SESSION REC ADAMS EMPLOYEE SESSION REC SYSTEM EMPLOYEE SESSION REC DELETE ANY TABLE 3 rows selected.

Data Dictionary View ALL_DEF_AUDIT_OPTS DBA_STMT_AUDIT_OPTS DBA_PRIV_AUDIT_OPTS DBA_OBJ_AUDIT_OPTS

Description

Default audit options Statement auditing options Privilege auditing options Schema object auditing options

... 18-36 Enterprise DBA Part 1A: Architecture and Administration

In document Enterprise DBA Part 1A: Architecture and Administration (Page 129-136)