Skip Headers
Oracle® Database 2 Day + Security Guide
11g Release 2 (11.2)

Part Number E10575-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

7 Auditing Database Activity

This chapter contains:

See Also:

About Auditing

Auditing is the monitoring and recording of selected user database actions. In standard auditing, you use initialization parameters and the AUDIT and NOAUDIT SQL statements to audit SQL statements, privileges, and schema objects, and network and multitier activities.

There are also activities that Oracle Database always audits, regardless of whether auditing is enabled. These activities are administrative privilege connections, database startups, and database shutdowns. See Oracle Database Security Guide for more information.

Another type of auditing is fine-grained auditing. Fine-grained auditing enables you to audit at the most granular level, data access, and actions based on content, using Boolean measurement, such as value > 1000. You can use fine-grained auditing to audit activities based on access to or changes in a column. You can create security policies to trigger auditing when someone accesses or alters specified elements in an Oracle database, including the contents within a specified object. You can create policies that define specific conditions that must take place for the audit to occur. For example, you can audit a particular table column to find out when and who tried to access it during a specified period of time. Furthermore, you can create alerts that are triggered when the policy is violated, and write this data to a separate audit file. Oracle Database Security Guide explains how to perform fine-grained auditing.

Why Is Auditing Used?

You typically use auditing to perform the following activities:

Where Are Standard Audit Activities Recorded?

Oracle Database records audit activities in audit records. Audit records provide information about the operation that was audited, the user performing the operation, and the date and time of the operation. Audit records can be stored in either a data dictionary table, called the database audit trail, or in operating system files, called an operating system audit trail. Oracle Database also provides a set of data dictionary views that you can use to track suspicious activities. See Oracle Database Security Guide for more information about these views.

When you use standard auditing, Oracle Database writes the audit records to either to DBA_AUDIT_TRAIL (the SYS.AUD$ table), the operating system audit trail, or to the DBA_COMMON_AUDIT_TRAIL view, which combines standard and fine-grained audit log records.

In addition, the actions performed by administrators are recorded in the syslog audit trail when the AUDIT_SYSLOG_LEVEL initialization parameter is set.

Auditing General Activities Using Standard Auditing

This section explains how to use standard auditing to audit activities performed on SQL statements, privileges, schema objects, and network or multitier activities.

This section contains:

See Also:

Oracle Database Security Guide for detailed information about managing the standard audit trail

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.

See Also:

Oracle Database Security Guide for a roadmap of how and why you can use the different types of audit options available

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:

ALTER ANY PROCEDURE CREATE ANY LIBRARY DROP ANY TABLE
ALTER ANY TABLE CREATE ANY PROCEDURE DROP PROFILE
ALTER DATABASE CREATE ANY TABLE DROP USER
ALTER PROFILE CREATE EXTERNAL JOB EXEMPT ACCESS POLICY
ALTER SYSTEM CREATE PUBLIC DATABASE LINK GRANT ANY OBJECT PRIVILEGE
ALTER USER CREATE SESSION GRANT ANY PRIVILEGE
AUDIT SYSTEM CREATE USER GRANT ANY ROLE
CREATE ANY JOB DROP ANY PROCEDURE  

Oracle Database audits the following SQL statement shortcuts by default:

ROLE SYSTEM AUDIT PUBLIC SYNONYM
DATABASE LINK PROFILE SYSTEM GRANT

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.

Note:

If your applications use the default audit settings from Oracle Database 10g Release 2 (10.2), then you can revert to these audit settings until you modify the applications to use the Release 11g audit settings. To do so, run the undoaud.sql script.

After you have modified your applications to conform to the Release 11g audit settings, then you can manually update your database to use the audit configuration that suits your business needs, or you can run the secconf.sql script to apply the Release 11g default audit settings.

The undoaud.sql and secconf.sql scripts are in the $ORACLE_HOME/rdbms/admin directory. The undoaud.sql script affects audit settings only, and the secconf.sql script affects both audit and password settings. They have no effect on other security settings.

See Also:

  • Oracle Database SQL Language Reference for detailed information about the SQL statements described in this section

  • Oracle Database Reference for detailed information about the AUDIT_TRAIL initialization parameter

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.

See Also:

Oracle Database Security Guide for detailed information about auditing SQL statements

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;

See Also:

Oracle Database Security Guide for detailed information about auditing privileges

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 PROXY clause in the AUDIT statement.

For example, to audit SELECT TABLE statements issued on behalf of client jackson by the proxy application server appserve:

AUDIT SELECT TABLE BY jackson ON BEHALF OF appserve;

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.

See Also:

Oracle Database Security Guide for detailed information about auditing in a multitier environment

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.

See Also:

Oracle Database Security Guide for detailed information about auditing schema objects

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
    

See Also:

Oracle Database Security Guide for detailed information about auditing network activity

Tutorial: Creating a Standard Audit Trail

Suppose you wanted to audit SELECT statements on the OE.CUSTOMERS table. In this tutorial, you enable standard auditing, enable auditing for the SELECT SQL statement, run the SELECT SQL statement on the OE.CUSTOMERS table, and then check its audit file.

In this tutorial:

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 SYSTEM.

    • User Name: SYSTEM

    • Password: Enter your password.

    • Connect As: Normal

  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.

  7. In the Value field, select the DB (Database) option.

    The DB option 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.

  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.

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.

  2. Grant sec_admin the SELECT privilege on the OE.CUSTOMERS table.

  3. Log in to Database Control as user sec_admin.

  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. Shut down the database instance and then restart it.

    1. In the upper, right corner of the Database Control page, select Logout.

    2. Click Login.

    3. In the Login page, enter the following login information:

      User Name: SYS

      Password: The password of the system administrator

      Connect As: SYSOPER

      Use the SYSOPER system privilege to shut down and restart the database.

    4. Under General, click Shutdown.

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

      See Oracle Database 2 Day DBA for more information.

    6. After the shutdown completes, click Startup.

    7. Exit 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 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, TIMESTAMP FROM DBA_AUDIT_TRAIL;
    

    Output similar to the following appears:

    USERNAME          TIMESTAMP
    ---------------------------
    SEC_ADMIN         07-MAY-09
    
  4. Exit SQL*Plus:

    EXIT
    

Step 4: Optionally, Remove the Components for This Tutorial

Optionally, remove the audit settings you created earlier.

To remove the audit settings in Database Control:  

  1. Log in to Database Control using administrative privileges.

  2. Go to the Database Control home page.

  3. Click Server to display the Server subpage.

  4. In the Security section, click Audit Settings.

    The Audit Settings page appears.

  5. Select the Audited Objects subpage.

  6. Under Schema, enter OE.

  7. Under Object Name, enter CUSTOMERS.

  8. Click Search.

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

    A Confirmation dialog box appears.

  10. Select Yes.

  11. Exit Database Control.

To set AUDIT_TRAIL to its original value:

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, you should remove it.

To remove the sec_admin security administrator account:  

  1. Log in to Database Control using administrative privileges.

  2. Go to the Database Control home page.

  3. Click Server to display the Server subpage.

  4. In the Security section, click Users.

    The Users page appears.

  5. In the Name field, enter sec_admin.

  6. Click Search.

  7. Select the box next to the sec_admin user account, and then click Remove.

    A Confirmation dialog box appears.

  8. Select Yes.

  9. Exit Database Control.

Guidelines for Auditing

This section contains the following topics:

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. Remember 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; 
    

Initialization Parameters Used for Auditing

Table 7-1 lists initialization parameters that you can use to secure auditing.

Table 7-1 Initialization Parameters Used for Auditing

Initialization Parameter Default Setting Description

AUDIT_TRAIL

DB

Enables or disables auditing. See "Enabling or Disabling the Standard Audit Trail" for detailed information. For a full listing of the AUDIT_TRAIL parameters and an example of setting them, see Oracle Database Security Guide.

AUDIT_FILE_DEST

ORACLE_BASE/admin/ORACLE_SID/adump

or

ORACLE_HOME/rdbms/audit

Specifies the operating system directory into which the audit trail is written when the AUDIT_TRAIL initialization parameter is set to OS, XML, or XML,EXTENDED. Oracle Database writes the audit records in XML format if the AUDIT_TRAIL initialization parameter is set to XML.

Oracle Database also writes mandatory auditing information to this location, and if the AUDIT_SYS_OPERATIONS initialization parameter is set, writes audit records for user SYS.

AUDIT_SYS_OPERATIONS

FALSE

Enables or disables the auditing of top-level operations directly issued by user SYS, and users connecting with SYSDBA or SYSOPER privilege. Oracle Database writes the audit records to the audit trail of the operating system. If you set the AUDIT_TRAIL initialization parameter to XML or XML, EXTENDED, it writes the audit records in XML format.

On UNIX systems, if you have also set the AUDIT_SYSLOG_LEVEL parameter, then it overrides the AUDIT_TRAIL parameter, which writes the SYS audit records to the system audit log using the SYSLOG utility.

AUDIT_SYSLOG_LEVEL

No default setting

On UNIX systems, writes the SYS and standard OS audit records to the system audit log using the SYSLOG utility.


To modify an initialization parameter, see "Modifying the Value of an Initialization Parameter". For detailed information about initialization parameters, see Oracle Database Reference and Oracle Database Administrator's Guide.