7 Auditing Database Activity

About Standard Auditing

In standard auditing, you enable auditing of SQL statements, privileges, schema objects, and network or multitier activities. You can audit a specific schema table if you want. To perform this type of audit, you use Database Control.

You can view the standard audit trail by querying the DBA_AUDIT_TRAIL and DBA_COMMON_AUDIT_TRAIL data dictionary views.

Enabling or Disabling the Standard Audit Trail

Before you perform the standard auditing procedures described in this section, you must enable standard auditing. When you enable standard auditing, you can create the audit trail in the database audit trail or write the audit activities to an operating system file. If you write to an operating system file, you can create the audit record in text or XML format.

To enable or disable the standard audit trail:  

1. Start Database Control.

2. Log in as SYS and connect with the SYSDBA privilege.

   • User Name: SYS

   • Password: Enter your password.

   • Connect As: SYSDBA

3. Click Server to display the Server subpage.

4. In the Database Configuration section, click Initialization Parameters.

   The Initialization Parameters page appears.

5. Click SPFile to display the SPFile subpage.

   If the SPFile tab does not display in your installation, then you did not install Oracle Database using a server parameters file. Go to the next step.

6. In the Name field, enter audit_trail to find the AUDIT_TRAIL initialization parameter, and then click Go.

   You can enter the first few characters of the parameter, for example, AUDIT_. Alternatively, you can scroll down the list of parameters to find the AUDIT_TRAIL parameter.

7. In the Value field, select one of the following values:

   • DB: Enables database auditing and directs standard audit records to the database audit trail (SYS.AUD$), except for records that are always written to the operating system audit trail. (This value is the default if you created the database using Database Configuration Assistant. Otherwise, the default is NONE.)

   • OS: Enables database auditing and directs all audit records to an operating system file. Writing the audit trail to operating system files is better for performance instead of sending the audit records to the SYS.AUD$ table. If the auditor is distinct from the database administrator, you must use the operating system setting. Any auditing information stored in the database is viewable and modifiable by the database administrator.

     To specify the location of the operating system audit record file, set the AUDIT_FILE_DEST initialization parameter. The first default directory is $ORACLE_BASE/admin/$ORACLE_SID/adump.

   • NONE: Disables standard auditing.

   • DB, EXTENDED: Performs all actions of the AUDIT_TRAIL=DB setting and also populates the SQL bind and SQL text CLOB-type columns of the SYS.AUD$ table, when available. (These two columns are populated only when this parameter is specified.)

   • XML: Writes to the operating system audit record file in XML format. Prints all elements of the AuditRecord node (as specified by the by the XML schema in http://xmlns.oracle.com/oracleas/schema/dbserver_audittrail-11_2.xsd XSD file) except Sql_Text and Sql_Bind to the operating system XML audit file. This .xsd file represents the schema definition of the XML audit file. An XML schema is a document written in the XML Schema language.

   • EXTENDED: Specifies XML, EXTENDED, which performs all actions of XML and also populates the SQL bind and SQL text CLOB-type columns of the SYS.AUD$ table, wherever possible. (These columns are populated only when this parameter is specified.)

   For a detailed explanation of the AUDIT_TRAIL initialization parameter settings, see Oracle Database Security Guide.

8. Click Apply.

9. Restart the Oracle Database instance:

   1. Click the Database Instance link.

   2. Click Home to display the Database Control home page.

   3. Under General, click Shutdown.

   4. In the Startup/Shutdown Credentials page, enter your credentials.

      See Oracle Database 2 Day DBA for more information.

   5. After the shutdown completes, click Startup.

Note the following:

• You do not need to restart the database if you change the auditing of objects. You only need to restart the database if you made a universal change, such as turning on or off all auditing or changing the destination of the audit trail operating system files.

• You do not need to set AUDIT_TRAIL to enable either fine-grained auditing or SYS auditing. (SYS auditing enables you to monitor the activities of a system administrator. See Oracle Database Security Guide for more information.) For fine-grained auditing, you add and remove fine-grained auditing policies as necessary, applying them to the specific operations or objects you want to monitor. You can use the AUDIT_SYS_OPERATIONS parameter to enable and disable SYS auditing.

Using Default Auditing for Security-Relevant SQL Statements and Privileges

When you use Database Configuration Assistant (DBCA) to create a new database, Oracle Database configures the database to audit the most commonly used security-relevant SQL statements and privileges. It also sets the AUDIT_TRAIL initialization parameter to DB. If you decide to use a different audit trail type (for example, OS if you want to write the audit trail records to operating system files), then you can do that: Oracle Database continues to audit the privileges that are audited by default. If you disable auditing by setting the AUDIT_TRAIL parameter to NONE, then no auditing takes place.

Oracle Database audits the following privileges by default:

Oracle Database audits the following SQL statement shortcuts by default:

To individually control the auditing of SQL statements and privileges, use the AUDIT and NOAUDIT statements.

Oracle strongly recommends that you audit the database. Auditing is an effective method of enforcing strong internal controls so that your site can meet its regulatory compliance requirements, as defined in the Sarbanes-Oxley Act. This enables you to monitor business operations and catch any activities that may deviate from company policy. Doing so translates into tightly controlled access to your database and the application software. By enabling auditing by default, you can generate an audit record for audit and compliance personnel.

Individually Auditing SQL Statements

The SQL statements that you can audit are in the following categories:

• DDL statements. For example, enabling the auditing of tables (AUDIT TABLE) audits all CREATE and DROP TABLE statements

• DML statements. For example, enabling the auditing of SELECT TABLE audits all SELECT ... FROM TABLE/VIEW statements, regardless of the table or view

Statement auditing can be broad or focused, for example, by auditing the activities of all database users or of only a select list of users.

Individually Auditing Privileges

Privilege auditing is a way to audit statements that can use a system privilege. For example, you can audit the SELECT ANY TABLE privilege if you want to audit all the SELECT statements that will use the SELECT ANY TABLE privilege. You can audit the use of any system privilege. Similar to statement auditing, privilege auditing can audit the activities of all database users or of only a specified list. As with SQL statement auditing, you use the AUDIT and NOAUDIT statements to enable and disable privilege auditing. In addition, you must have the AUDIT SYSTEM system privilege before you can enable auditing.

Privilege audit options match the corresponding system privileges. For example, the option to audit use of the DELETE ANY TABLE privilege is DELETE ANY TABLE. For example:

AUDIT DELETE ANY TABLE BY ACCESS WHENEVER NOT SUCCESSFUL;

To audit all successful and unsuccessful uses of the DELETE ANY TABLE system privilege, enter the following statement:

AUDIT DELETE ANY TABLE BY ACCESS;

To audit all unsuccessful SELECT, INSERT, and DELETE statements on all tables and unsuccessful uses of the EXECUTE PROCEDURE system privilege, by all database users, and by individual audited statement, issue the following statement:

AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE BY ACCESS WHENEVER NOT SUCCESSFUL;

Using Proxies to Audit SQL Statements and Privileges in a Multitier Environment

You can audit the activities of a client in a multitier environment by specifying a proxy in the Add Audited Statements or Add Audited Privileges page in Database Control. You can use the SQL AUDIT statement to audit the activities of a client in a multitier environment. To do so, use the BY user clause in the AUDIT statement.

For example, to audit SELECT TABLE statements issued by the proxy application user jackson:

AUDIT SELECT TABLE BY jackson;

Afterward, user jackson can connect using the appserve proxy user as follows:

CONNECT appserve[jackson]
Enter password: password

The middle tier can also set the user client identity in a database session, enabling the auditing of user actions through the middle-tier application. The user client identity then shows up in the audit trail.

Individually Auditing Schema Objects

Schema object auditing can audit all SELECT and DML statements permitted by object privileges, such as SELECT or DELETE statements on a particular table. The GRANT and REVOKE statements that control those privileges are also audited.

Auditing Network Activity

You can use the AUDIT statement to audit unexpected errors in network protocol or internal errors in the network layer. The types of errors uncovered by network auditing are not connection failures, but can have several other possible causes. One possible cause is an internal event set by a database engineer for testing purposes. Other causes include conflicting configuration settings for encryption, such as the network not finding the information required to create or process expected encryption.

To enable network auditing:  

1. Start SQL*Plus and log on with administrative privileges, such as SYSTEM, or as a security administrator. For example:

   sqlplus system
   Enter password: password
   

   SQL*Plus starts, connects to the default database, and then displays a prompt.

   For detailed information about starting SQL*Plus, see Oracle Database 2 Day DBA.

2. Enter the following statement:

   AUDIT NETWORK;
   

   To disable network auditing, enter the following:

   NOAUDIT NETWORK;
   

3. Exit SQL*Plus:

   EXIT
   

Step 1: Log In and Enable Standard Auditing

First, log in, and, if necessary, enable standard auditing.

To enable standard auditing:  

1. Start Database Control.

2. Log in as SYS.

   • User Name: SYS

   • Password: Enter your password.

   • Connect As: SYSDBA

3. Click Server to display the Server subpage.

4. In the Database Configuration section, click Initialization Parameters.

   The Initialization Parameters page appears.

5. Click SPFile to display the SPFile subpage.

   If the SPFile tab does not display in your installation, then you did not install Oracle Database using a server parameters file. Go to the next step.

6. In the Name field, enter AUDIT_TRAIL to find the AUDIT_TRAIL parameter, and then click Go.

   You can enter the first few characters of the parameter, for example, AUDIT. Alternatively, you can scroll down the list of parameters to find the AUDIT_TRAIL parameter. To sort the list of parameters in alphabetical order, click the Name column.

7. In the Value field, make a note of the current setting, and then change it to DB_EXTENDED.

   By default, the AUDIT_TRAIL parameter is set to DB, which enables database auditing and directs all audit records to the database audit trail (SYS.AUD$), except for records that are always written to the operating system audit trail. DB_EXTENDED has this functionality, plus it records SQL text and SQL bind variables.

8. Click Apply.

9. Restart the Oracle Database instance.

   From a command line, enter the following commands:

   sqlplus sys as sysoper
   Enter password: password
   
   SQL> SHUTDOWN IMMEDIATE
   SQL> RESTART
   

   At this point, you can check the AUDIT_TRAIL setting by entering the following command:

   SQL> SHOW PARAMETER AUDIT_TRAIL
   
   NAME                     TYPE            VALUE
   ------------------------ --------------- ---------------------------------\
   audit_trail              string          DB_EXTENDED 
   

Step 2: Enable Auditing for SELECT Statements on the OE.CUSTOMERS Table

Next, enable auditing for SELECT statements on the OE.CUSTOMERS table.

To enable auditing of SELECT statements for the OE.CUSTOMERS table:  

1. Ensure that the sample user sec_admin exists.

   Log on as SYSTEM, and then from the Database Control home page, click Server to display the Server subpage. Select Users under Security, and check the list of accounts for sec_admin. "Step 1: Create a Security Administrator Account" explains how to create the sec_admin security administrator account. If you still have Oracle Database Vault enabled, then you must recreate the account using the Database Vault Account Manager account.

2. In SQL*Plus, log in as user OE and then grant sec_admin the SELECT privilege on the OE.CUSTOMERS table.

   sqlplus oe
   Enter password: password
   Connected.
   
   SQL> GRANT SELECT ON CUSTOMERS TO sec_admin;
   

3. Log in to Database Control as user SYS with the SYSDBA privilege.

4. Click Server to display the Server subpage.

5. In the Security section, click Audit Settings.

   The Audit Settings page appears.

6. Select the Audited Objects subpage.

7. Click Add.

   The Add Audited Object page appears.

8. Enter the following information:

   • Object Type: Select Table.

   • Table: Enter OE.CUSTOMERS.

   • Available Statements: Select SELECT, and then click Move to move it to the Selected Statements list.

9. Click OK.

10. Log out of Database Control.

Step 3: Test the Audit Settings

At this stage, auditing is enabled and any SELECT statements performed on the OE.CUSTOMERS table are written to the to DBA_AUDIT_TRAIL data dictionary view. Now, you are ready to test the audit settings.

To test the audit settings:  

1. Start SQL*Plus, and connect as user sec_admin.

   sqlplus sec_admin
   Enter password: password
   

2. Enter the following SELECT statement to create an alert in the audit trail:

   SELECT COUNT(*) FROM OE.CUSTOMERS;
   

3. Enter the following statement to view the DBA_AUDIT_TRAIL view:

   SELECT USERNAME, SQL_TEXT, TIMESTAMP 
   FROM DBA_AUDIT_TRAIL 
   WHERE SQL_TEXT LIKE 'SELECT %';
   

   For this SELECT statement, enter the text for the SQL_TEXT column ('SELECT %') using the same case that you used when you entered the SELECT statement in Step 2. In other words, if you entered that SELECT statement in lowercase letters, then enter 'select %' when you query the DBA_AUDIT_TRAIL view, not 'SELECT %'.

   Output similar to the following appears:

   USERNAME          SQL_TEXT                                TIMESTAMP
   ----------------- --------------------------------------- ------------------
   SEC_ADMIN         SELECT COUNT(*) FROM OE.CUSTOMERS       31-MAR-10
   

4. Exit SQL*Plus:

   EXIT
   

Step 4: Optionally, Remove the Components for This Tutorial

Optionally, remove the audit settings that you created earlier.

To remove the audit settings in Database Control:  

1. Log in to Database Control as user SYS with the SYSDBA privilege.

2. Click Server to display the Server subpage.

3. In the Security section, click Audit Settings.

   The Audit Settings page appears.

4. Select the Audited Objects subpage.

5. Under Schema, enter OE.

6. Under Object Name, enter CUSTOMERS.

7. Click Search.

8. Select the box next to the OE.CUSTOMERS audited schema, and then click Remove.

   A Confirmation dialog box appears.

9. Select Yes.

10. Click the Database Instance link to return to the Database home page.

11. Select the Server subpage, and then under Database Configuration, select Initialization Parameters.

12. Select the SPFile subpage.

13. Find the AUDIT_TRAIL parameter and then set it to the original value. Click Apply.

14. Exit Database Control.

15. Restart the database:

    sqlplus sys as sysoper
    Enter password: password
    
    SQL> SHUTDOWN IMMEDIATE
    SQL> RESTART
    

Step 5: Remove the SEC_ADMIN Security Administrator Account

This is the last example in this guide. If you no longer need the sec_admin administrator account, then you should remove it.

To remove the sec_admin security administrator account:  

1. Log in to Database Control as user SYSTEM.

   If Oracle Database Vault is enabled, then you must log on as the Database Vault Account Manager.

2. In the Database Control home page, click Server to display the Server subpage.

3. In the Security section, click Users.

   The Users page appears.

4. In the Name field, enter sec_admin.

5. Click Search.

6. Select the selection box next to the sec_admin user account, and then click Delete.

   A Confirmation dialog box appears.

7. Select Yes.

8. Exit Database Control.

Guideline for Using Default Auditing of SQL Statements and Privileges

When you create a new database, you can enable the auditing of a select set of SQL statements and privileges. Oracle recommends that you enable default auditing. Auditing is an effective method of enforcing strong internal controls so that your site meets its regulatory compliance requirements. See "Using Default Auditing for Security-Relevant SQL Statements and Privileges" for more information about default auditing.

Guidelines for Managing Audited Information

Although auditing has a minimal impact on database performance, limit the number of audited events as much as possible. This minimizes the performance impact on the execution of audited statements and the size of the audit trail, making it easier to analyze and understand.

Follow these guidelines when devising an auditing strategy:

1. Evaluate your reason for auditing.

   After you understand of the reasons for auditing, you can devise an appropriate auditing strategy and avoid unnecessary auditing.

   For example, suppose you are auditing to investigate suspicious database activity. This information by itself is not specific enough. What types of suspicious database activity do you suspect or have you noticed? A more focused auditing purpose might be to audit unauthorized deletions from arbitrary tables in the database. This purpose narrows the type of action being audited and the type of object being affected by the suspicious activity.

2. Audit knowledgeably.

   Audit the minimum number of statements, users, or objects required to get the targeted information. This prevents unnecessary audit information from cluttering the meaningful information. Balance your need to gather sufficient security information with your ability to store and process it.

   For example, if you are auditing to gather information about database activity, then determine exactly what types of activities you want to track, audit only the activities of interest, and audit only for the amount of time necessary to gather the information that you want. As another example, do not audit objects if you are only interested in logical I/O information for each session.

Guidelines for Auditing Typical Database Activity

When your purpose for auditing is to gather historical information about particular database activities, follow these guidelines:

1. Audit only pertinent actions.

   To avoid cluttering meaningful information with useless audit records and to reduce the amount of audit trail administration, audit only the targeted database activities. You can audit specific actions by using fine-grained auditing. Oracle Database Security Guide describes fine-grained auditing in detail.

2. Archive audit records and purge the audit trail.

   After you collect the required information, archive the audit records of interest, and purge the audit trail of this information.

   To archive audit records, you copy the relevant records to a database table, for example, using INSERT INTO table SELECT ... FROM SYS.AUD$ ... for the standard audit trail. (Fine-grained audit records are in the SYS.FGA_LOG$ table.) Alternatively, you can export the audit trail table to an operating system file. Oracle Database Utilities explains how to export tables by using Oracle Data Pump.

   To purge audit records, you delete standard audit records from the SYS.AUD$ table and fine-grained audit records from the SYS.FGA_LOG$ table. For example, to delete all audit records from the standard audit trail, enter the following statement:

   DELETE FROM SYS.AUD$;
   

   Alternatively, to delete all audit records from the standard audit trail generated as a result of auditing the table emp, enter the following statement:

   DELETE FROM SYS.AUD$ WHERE OBJ$NAME='EMP';
   

3. Follow the privacy considerations of your company.

   Privacy regulations often lead to additional business privacy policies. Most privacy laws require businesses to monitor access to personally identifiable information (PII), and this type of monitoring is implemented by auditing. A business-level privacy policy should address all relevant aspects of data access and user accountability, including technical, legal, and company policy concerns.

Guidelines for Auditing Suspicious Database Activity

When you audit to monitor suspicious database activity, follow these guidelines:

1. Audit general information, and then audit specific information.

   When you start to audit for suspicious database activity, often not much information is available to target specific users or schema objects. Therefore, set audit options more generally at first, that is, by using the standard audit options described in "Auditing General Activities Using Standard Auditing".

   After you have recorded and analyzed the preliminary audit information, disable general auditing, and then audit specific actions. You can use fine-grained auditing, described in Oracle Database Security Guide, to audit specific actions. Continue this process until you gather enough evidence to draw conclusions about the origin of the suspicious database activity.

2. Protect the audit trail.

   When auditing for suspicious database activity, protect the audit trail so that audit information cannot be added, changed, or deleted without being audited. You audit the standard audit trail by using the AUDIT SQL statement. For example:

   sqlplus sys as sysdba 
   Enter password: password
   
   SQL> AUDIT SELECT ON SYS.AUD$ BY ACCESS;