Monitoring database activity in Oracle 10g

Learning objective

After completing this topic, you should be able to recognize how to monitor database activity using Oracle's built-in audit tools.

1. Introducing Oracle auditing tools

A database administrator can audit all actions that take place within a database. This is good practice from a security standpoint, as auditing can detect suspicious activity and enable the DBA to take appropriate action.

Auditing is an activity that captures and stores information about what is happening in the database. This increases your system's workload and can affect system performance, especially if you audit every action in the database.

Auditing should therefore be focused, so that only relevant events are captured.

Oracle 10g provides three types of auditing.

• standard database auditing
• value-based auditing
• fine-grained auditing

standard database auditing

Standard database auditing captures several pieces of information about an audited event, including when the event occurred, the user who caused the event, and the client machine that was used. It also captures information on object access.

value-based auditing

Value-based auditing records changes to data, such as inserts, updates, and deletes. It captures not only the occurrence of the audited event, but the actual values that were inserted, updated, or deleted. Value-based auditing is implemented through database triggers.

fine-grained auditing

Fine-grained auditing (FGA) is used to audit SQL statements. In addition to capturing the fact that an audited event occurred, it captures the SQL statement that was used to precipitate the event.

Question

Match the auditing methods with the type of data they capture.

Options:

1. Fine-grained auditing
2. Standard database auditing
3. Value-based auditing

Targets:

1. SQL statements used in an audited event
2. The time of an audited event, who initiated it, and the machine used
3. Changes to data, such as inserts, updates, and deletes

Answer

Fine-grained auditing captures SQL statements used in an audited event. Standard database auditing captures information about an audited event. And value-based auditing captures changes to data, such as inserts, updates, and deletes.

Fine-grained auditing also captures information about the event.

Standard database auditing also captures information on object access.

Value-based auditing is implemented through database triggers.

2. Working with auditing options and results

You use the non-dynamic AUDIT_TRAIL parameter to enable standard database auditing by specifying a storage location for audit records. There are three possible settings for this parameter.

• DB
• OS
• NONE

DB

Setting the parameter to DB stores audit records in the DBA_AUDIT_TRAIL table in the database. This is the normal setting for the parameter.

OS

Setting the parameter to OS stores audit records in the operating-system audit trail.

NONE

Setting the parameter to NONE disables audit record collection in the database.

Database auditing can capture information about

• login events
• the exercise of system privileges
• the exercise of object privileges

However, you need to focus an audit command so that the information it gathers is relevant and useful.

For example, using the AUDIT TABLE audit command as shown captures any operation that affects any table. This will capture too much unfocused information.

You can focus an audit command by using ON and WHENEVER SUCCESSFUL clauses.

You can then focus an audit on, for example, the user who generated an audit event, or on whether the event was successful or unsuccessful.

Question

To use database auditing, you must first point to a storage location for audit records.

What is the initialization parameter you use to do this?

Options:

1. AUDIT_TRAIL
2. AUDIT TABLE
3. WHENEVER SUCCESSFUL

Answer

You use the AUDIT_TRAIL initialization parameter to point to a storage location for audit records.

Option 1 is correct. The default setting for the AUDIT_TRAIL parameter is DB, which stores audit records in the database.

Option 2 is incorrect. The AUDIT TABLE command captures any information that affects any table.

Option 3 is incorrect. The WHENEVER SUCCESSFUL clause is used with the AUDIT command to capture information on audit events that have succeeded..

There are four auditing options in Oracle 10g.

• object privilege auditing
• session auditing
• SQL statement auditing
• system privilege auditing

object privilege auditing

You use object privilege auditing to audit actions on tables, views, procedures, sequences, directories and user-defined data types. You need to specify BY ACCESS to generate a separate audit trail for each action, as the default grouping is by session.

session auditing

You use the AUDIT SESSION option to audit the creation of user sessions. This option generates a separate record for each session created by connection to an instance. Information gathered in a record includes connection time, disconnection time, and logical and physical I/Os processed.

SQL statement auditing

You use the SQL statement AUDIT TABLE to audit any DDL statement that affects a table, including CREATE TABLE, DROP TABLE, and TRUNCATE TABLE. You can focus the audit by username or by success or failure.

system privilege auditing

You use system privilege auditing to audit the exercise of system privileges, such as DROP ANY TABLE. By default, an audit record is generated every time an audited system privilege is exercised. Using the BY SESSION clause generates only one record per session instead of one for each action in a session, thereby improving system performance.

When session auditing, using the WHENEVER NOT SUCCESSFUL clause enables you to detect attempts to break into your database.

You type AUDIT SESSION WHENEVER NOT SUCCESSFUL; and press Enter.

You can now view details of all unsuccessful sessions.

If you are unsure what type of activity you are looking for, you can use the AUDIT ALL option to audit a broad range of activity. You can use it with object privilege auditing or with a username to focus the option.

There are four different data dictionary views for viewing auditing options.

• ALL_DEF_AUDIT_OPTS
• DBA_STMT_AUDIT_OPTS
• DBA_PRIV_AUDIT_OPTS
• DBA_OBJ_AUDIT_OPTS

ALL_DEF_AUDIT_OPTS

The ALL_DEF_AUDIT_OPTS data dictionary view displays default audit options.

DBA_STMT_AUDIT_OPTS

The DBA_STMT_AUDIT_OPTS data dictionary view displays statement audit options. It contains only records of statement audit options that have been specified.

DBA_PRIV_AUDIT_OPTS

The DBA_PRIV_AUDIT_OPTS data dictionary view displays privilege audit options. It contains only records of privilege audit options that have been specified.

DBA_OBJ_AUDIT_OPTS

The DBA_OBJ_AUDIT_OPTS data dictionary view displays schema object audit options. It contains one record per auditable object, regardless of what object audit options have been specified.

Data dictionary views consist of a column for each auditable option. Here, the INS column displays INSERT audit options and the UPD column displays UPDATE audit options.

SELECT object_name, object_type, ins, upd
FROM dba_obj_audit_opts WHERE object_name = 'EMPLOYEES'

OBJECT_NAME  OBJECT_TY INS UPD
------------ --------- --- ---
EMPLOYEES    TABLE     A/S -/-

Audit options are displayed in the columns as SUCCESSFUL/NOT SUCCESSFUL, with three possible values for each status:

• A (audited by access)
• S (audited by session)
• - (not audited)

SELECT object_name, object_type, ins, upd
FROM dba_obj_audit_opts WHERE object_name = 'EMPLOYEES'
OBJECT_NAME  OBJECT_TY INS UPD
------------ --------- --- ---
EMPLOYEES    TABLE     A/S -/- 

Question

Suppose you're performing an audit to detect unauthorized attempts to access your database.

Type the command to audit unsuccessful user sessions.

Answer

You type the command AUDIT SESSION WHENEVER NOT SUCCESSFUL to audit unsuccessful user sessions.

When you've specified your auditing options, the database begins collecting audit information and storing it in the location you've specified in the AUDIT_TRAIL parameter.

If you set the parameter to OS, the audit records are stored in the event log if your OS is Windows, and in a file if the OS is UNIX or Linux. You specify this file's location using the AUDIT_FILE_DEST parameter.

If you set AUDIT_TRAIL to DB, audit records are stored in a table that is part of the SYS schema.

As a DBA, it's important that you maintain the database audit trail. The audit trail can increase drastically in size and affect system performance if it is not properly maintained.

You can use different options for viewing auditing results.

• DBA_AUDIT_TRAIL
• DBA_AUDIT_EXISTS
• DBA_AUDIT_OBJECT
• DBA_AUDIT_SESSION
• DBA_AUDIT_STATEMENT

DBA_AUDIT_TRAIL

The DBA_AUDIT_TRAIL option enables you to view all audit trail entries. The USER_AUDIT_TRAIL view lists all audit records related to the current user.

DBA_AUDIT_EXISTS

You use the DBA_AUDIT_EXISTS view to display records produced by AUDIT EXISTS and AUDIT NOT EXISTS commands.

DBA_AUDIT_OBJECT

The DBA_AUDIT_OBJECT view enables you to view records concerning all schema objects.

DBA_AUDIT_SESSION

The DBA_AUDIT_SESSION view enables you to view all entries concerning CONNECT and DISCONNECT actions.

DBA_AUDIT_STATEMENT

You use the DBA_AUDIT_STATEMENT view to display records concerning statement auditing, including GRANT, REVOKE, AUDIT, NOAUDIT, and ALTER SYSTEM statements.

Because audit records can contain sensitive information, you should ensure that access to audit records is carefully controlled.

If for some reason you need to delegate the maintenance of the audit trail, you use DELETE_CATALOG_ROLE to grant permission to delete from the audit trail.

Question

Match the audit trail viewing options to the correct description of what can be viewed.

Options:

1. DBA_AUDIT_OBJECT
2. DBA_AUDIT_STATEMENT
3. DBA_AUDIT_EXISTS
4. DBA_AUDIT_TRAIL
5. DBA_AUDIT_SESSION

Targets:

1. Records for AUDIT EXISTS/NOT EXISTS
2. Records concerning schema objects
3. All connect and disconnect entries
4. Records concerning statement auditing
5. All audit trail entries

Answer

The audit trail viewing options are now matched correctly with the descriptions of what can be viewed.

This view contains audit trail records for all objects in the schema.

This view contains audit records concerning GRANT, REVOKE, AUDIT, NOAUDIT, and ALTER SYSTEM statements.

This view lists audit trail entries produced by AUDIT EXISTS and by AUDIT NOT EXISTS.

You use the USER_AUDIT_TRAIL view to list all audit records related to the current user.

This view lists all audit trail records concerning CONNECT and DISCONNECT actions.

3. Value-based auditing and auditing SYSDBA and SYSOPER users

Unlike standard database auditing, which records only that changes have occurred in a database, value-based auditing captures the actual values that have been inserted, updated, or deleted.

Value-based auditing is initiated by event-driven PL/SQL constructs called database triggers. A trigger is fired when a user inserts, updates, or deletes data from a table to which the trigger is attached. It then copies audit information to a special table.

Because database triggers are executed each time an insert, update, or delete operation occurs, value-based auditing can have an adverse effect on system performance.

Therefore, it should only be used when extra information on audited data is necessary.

An example of a typical trigger is one that captures changes to the salary column of an HR.EMPLOYEES table, inserting an audit record into the AUDIT_EMPLOYEES table if a salary value changes.

The audit record will show

• the username and IP address from which the change was made
• the primary key of the changed record
• the actual salary values that were changed

Supplement

Selecting the link title opens the resource in a new browser window.

Launch window

View the code for the database trigger.

In addition to value-based auditing, you can use database triggers to capture information about user connections that may not have been captured by standard database auditing, such as IP address, program name, and terminal name.

Because SYSDBA and SYSOPER users have privileges to start and shut down the database and can make changes to the database when it is closed, you need to store the audit trail for these privileges outside the database.

By default, Oracle captures SYSDBA and SYSOPER login events, but you need to enable auditing specifically in order to capture other actions.

To enable auditing of SYSDBA and SYSOPER users, you set the AUDIT_SYS_OPERATIONS initialization parameter to TRUE.

If you are auditing SYS operations, you use the AUDIT_FILE_DEST initialization parameter to specify where the audit records will be stored.

If your OS is Windows, the audit trail is stored by default in the Windows event log. On UNIX or Linux platforms, audit records are stored in the location $ORACLE_HOME/rdbms/audit.

Question

Identify the initialization parameter used to enable auditing of SYSDBA and SYSOPER users.

Options:

1. AUDIT_FILE_DEST
2. AUDIT_SYS_OPERATIONS
3. AUDIT_TRAIL

Answer

You use the AUDIT_SYS_OPERATIONS initialization parameter to enable auditing of SYSDBA and SYSOPER users.

Option 1 is incorrect. The AUDIT_FILE_DEST initialization parameter is used to control where the audit records are to be stored.

Option 2 is correct. The AUDIT_SYS_OPERATIONS initialization parameter should be set to TRUE to enable the auditing.

Option 3 is incorrect. The AUDIT_TRAIL initialization parameter is used to enable standard database auditing.

Question

If SYS operations are audited, which initialization parameter controls where audit records will be stored?

Options:

1. AUDIT_FILE_DEST
2. AUDIT_SYS_OPERATIONS
3. AUDIT_TRAIL

Answer

The AUDIT_FILE_DEST initialization parameter controls where audit records will be stored if SYS operations are audited.

Option 1 is correct. You use AUDIT_FILE_DEST to control where the SYS audit records are stored - either in the event log (on Windows) or $ORACLE_HOME/rdbms/audit (on UNIX and Linux).

Option 2 is incorrect. You use the AUDIT_SYS_OPERATIONS parameter to enable auditing of SYSDBA and SYSOPER users.

Option 3 is incorrect. AUDIT_TRAIL is used to control the storage of audit records in standard database auditing, but not when auditing SYS operations.

Summary

Database auditing captures and stores information about actions in the database. Oracle Database 10g provides three types of auditing: standard database auditing, value-based auditing, and fine-grained auditing.

You enable standard database auditing using the AUDIT_TRAIL parameter. You can use clauses to focus an audit and ensure it gathers only relevant information. There are four auditing options in Oracle, and four different data dictionary views for viewing auditing options. It's important to maintain the database audit trail, as it can affect system performance. You can use different options for viewing auditing results.

You use value-based auditing to capture actual values that have been inserted, updated, or deleted in a database. Value-based auditing uses triggers to audit information. To audit users with SYSDBA and SYSOPER privileges, you set the AUDIT_SYS_OPERATIONS initialization parameter to TRUE and the AUDIT_FILE_DEST initialization parameter to specify where the audit records will be stored. You need to store the audit trail outside the database.