9 Auditing Database Activity

To audit database activity, you can use unified auditing, which enables you to create policies that audit user behavior in the database in a variety of ways.

Topics:

See Also:

About Auditing

Auditing is the monitoring and recording of selected user database actions.

To perform auditing, you must be granted the appropriate system privileges. To better facilitate separation of duty, the following two default roles are provided:

  • AUDIT_ADMIN role, which enables you to configure auditing and administer both unified audit policies and fine-grained audit policies. It also enables you to view and analyze audit data. Typically, security administrators are granted this role.

  • AUDIT_VIEWER role, which enables you to view and analyze audit data only. Typically, external auditors are granted this role.

This section provides an introduction to unified auditing, which captures audit records from the following locations:

  • Audit records (including SYS audit records) from unified audit policies and AUDIT settings

  • Fine-grained audit records from the DBMS_FGA PL/SQL package

  • Oracle Real Application Security audit records

  • Oracle Recovery Manager audit records

  • Oracle Database Vault audit records

  • Oracle Label Security audit records

Oracle Database consolidates these records in one location, in one format, viewable from the UNIFIED_AUDIT_TRAIL view for single database instances and GV$UNIFIED_AUDIT_TRAIL for Oracle Real Application Clusters environments.

When you upgrade your database to the current release, you must manually migrate to unified auditing if you want to use it. After you complete the migration, in an upgraded database, the audit records from the previous release are still available. You then can archive and purge these older audit trails. Afterwards, the new audit records will be written to the unified audit trail.

For a newly created database, Oracle Database provides mixed mode-enabled auditing, which enables both the old and new audit facilities to run simultaneously.

When you create and enable a unified audit policy, the policy begins to collect audit records right away. You do not need to set initialization parameters to enable overall auditing, as was necessary in previous releases. The policy can be as simple as auditing the activities of a single user or you can create complex audit policies that use conditions. You can have more than one audit policy in effect at a time in a database. An audit policy can contain both system-wide and object-specific audit options. Most of the auditing that you will do for general activities (including standard auditing) requires the use of audit policies

Another type of auditing is fine-grained auditing. Fine-grained auditing provides most of the auditing capabilities as unified auditing, plus the following functionality:

  • Auditing specific columns. You can audit specific relevant columns that hold sensitive information, such as salaries or Social Security numbers.

  • Using event handlers. For example, you can write a function that sends an email alert to a security administrator when an audited column that should not be changed at midnight is updated.

Oracle provides three predefined audit policies that cover commonly used security relevant audit settings. These policies are designed to provide an effective method of enforcing strong internal controls so that your site meets its regulatory compliance requirements.

See Also:

Why Is Auditing Used?

You typically use auditing to perform activities such as enabling user accountability, deterring inappropriate user actions, and investigating suspicious activity.

Auditing is used for the following reasons:

  • Enable accountability for actions. These include actions taken in a particular schema, table, or row, or affecting specific content.

  • Deter users from inappropriate actions based on that accountability.

  • Investigate suspicious activity. For example, if a user is deleting data from tables, then a security administrator might decide to audit all connections to the database and all successful and unsuccessful deletions of rows from all tables in the database.

  • Notify an auditor of actions by an unauthorized user. For example, an unauthorized user could change or delete data, or a user has more privileges than expected, which can lead to reassessing user authorizations.

  • Detect problems with an authorization or access control implementation. For example, you can create audit policies that you expect will never generate an audit record because the data is protected in other ways. However, if these policies do generate audit records, then you will know the other security controls are not properly implemented.

  • Address auditing requirements for compliance. Regulations such as the following have common auditing-related requirements:

    • Sarbanes-Oxley Act

    • Health Insurance Portability and Accountability Act (HIPAA)

    • International Convergence of Capital Measurement and Capital Standards: a Revised Framework (Basel II)

    • Japan Privacy Law

    • European Union Directive on Privacy and Electronic Communications

  • Monitor and gather data about specific database activities. For example, the database administrator can gather statistics about which tables are being updated, how many logical I/O operations are performed, or how many concurrent users connect at peak times.

Tutorial: Creating a Unified Audit Policy

Suppose you wanted to audit SELECT statements on the OE.CUSTOMERS table. In this tutorial, you create unified audit policy that monitors SELECT statements on the OE.CUSTOMERS table.

Topics:

Step 1: If Necessary, Enable Unified Auditing

In this procedure, you check if unified auditing has been enabled, and if it has not, you enable it.

To enable unified auditing:

  1. Log in to SQL*Plus as user SYS with the SYSDBA administrative privilege.

    sqlplus sys as sysdba
    Enter password: password
    
  2. Run the following query to find out if your database has been migrated to use unified auditing. Enter Unified Auditing in the case shown here.

    SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';
    

    If the output for the VALUE column is FALSE, then complete the remaining steps in this section to migrate to unified auditing. If the output is TRUE, then unified auditing is enabled and you can go to "Step 2: Grant the SEC_ADMIN User the AUDIT_ADMIN Role".

  3. Stop the database.

    For single-instance installations, enter the following commands from SQL*Plus:

    SHUTDOWN IMMEDIATE
    EXIT
    

    For Windows systems, stop the Oracle service:

    net stop OracleService%ORACLE_SID%
    

    For Oracle Real Application Clusters (Oracle RAC) installations, shut down each database instance as follows:

    srvctl stop database -db db_name
    
  4. Stop the listener. (Stopping the listener is not necessary for Oracle RAC and Grid Infrastructure listeners.)

    lsnrctl stop listener_name
    

    You can find the name of the listener by running the lsnrctl status command. The name is indicated by the Alias setting.

  5. Go to the $ORACLE_HOME/rdbms/lib directory.

  6. Enable the unified auditing executable.

    • UNIX: Run the following command:

      make -f ins_rdbms.mk uniaud_on ioracle ORACLE_HOME=$ORACLE_HOME
      
    • Windows: Rename the %ORACLE_HOME%/bin/orauniaud12.dll.option file to %ORACLE_HOME%/bin/orauniaud12.dll.

  7. Restart the listener.

    lsnrctl start listener_name
    
  8. Restart the database. Log in to SQL*Plus and then enter the STARTUP command as follows:

    sqlplus sys as sysoper
    Enter password: password
    
    SQL> STARTUP
    

    For Windows systems, start the Oracle service again.

    net start OracleService%ORACLE_SID%
    

    For Oracle RAC installations, from a command line, restart the database as follows:

    srvctl setenv database -db orcl
    

Step 2: Grant the SEC_ADMIN User the AUDIT_ADMIN Role

After you enable unified auditing, you are ready to grant the sec_admin user the AUDIT_ADMIN role, which enables SEC_ADMIN to create audit policies.

To grant the SEC_ADMIN user the AUDIT_ADMIN role:

  1. Access the Database home page for your target database as user SYS with the SYSDBA administrative privilege.

    See Oracle Database 2 Day DBA for more information.

  2. From the Administration menu, select Security, then Users.

  3. Select the SEC_ADMIN account and click Edit.

    If the sec_admin user account does not exist, see "Step 2: Create a Security Administrator Account" for instructions on how to create the sec_admin security administrator account.

  4. In the Edit User: SEC_ADMIN page, select the Roles tab.

  5. Select the Edit List button.

  6. In the Modify Roles page, select AUDIT_ADMIN from the Available Roles list, and then move it to the Selected Roles list. Click OK.

  7. In the Edit User: SEC_ADMIN page, select the Object Privileges tab.

  8. In the Edit User page: SEC_ADMIN page, from the Object Type list, select Package, and then click Add.

  9. In the Add Package Object Privileges page, do the following:

    1. In the Select Package Objects field, enter SYS.DBMS_AUDIT_MGMT.

    2. Under Available Packages, select EXECUTE and then click Move to send it to the Selected Privileges list.

    3. Click OK.

  10. In the Edit User page: SEC_ADMIN page, click Apply.

Step 3: Create and Enable a Unified Audit Policy

You can log in to Enterprise Manager as user sec_admin and then create a unified auditing policy for SELECT statements on the OE.CUSTOMERS table.

To create the unified audit policy:

  1. In Enterprise Manager, log out and then log back in again as user sec_admin.

  2. From the Administration menu, select Security, and then select Audit Settings.

  3. In the Audit Settings page, click Create.

  4. In the Create Audit Policy : Privileges and Roles page, enter the following settings:

    • Name: Enter select_cust_pol for the policy name.

    • Comments: Enter Audit policy for SELECT statements on OE.CUSTOMERS.

  5. Click the Next button.

  6. In the Create Audit Policy : Component Actions page, click Next.

  7. In the Create Audit Policy : Object Actions page, click Add Object.

  8. In the Add Object dialog box, enter the following settings:

    • Schema: OE

    • Type: TABLE

    • Object: CUSTOMERS

    • Action: SELECT

  9. Click OK.

    The Create Audit Policy : Object Actions page should appear as follows:

  10. In the Create Audit Policy : Object Actions window click Next.

  11. In the Create Audit Policy : Conditions window, click Next. In the Create Audit Policy : Review page, click Submit.

    The Audit Settings page appears., with SELECT_CUST_POL in the list of policies.

  12. Select SELECT_CUST_POL and then click the Enable button.

  13. In the Enable Audit Policy dialog box, deselect the Enable Audit Policy For All Users check box.

    The Enable Audit Policy dialog expands so that you can add specific users.

  14. Click the Add User button.

  15. In the Add User dialog box, search for and enter OE (select the Show Oracle Supplied check box) and ensure that the Audit on Success and Audit on Failure check boxes are selected.

  16. Click OK.

  17. Repeat these steps to add user HR to the list.

  18. Do not exit Enterprise Manager.

Step 4: Test the Unified Audit Policy

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

A unified auditing policy takes effect in the next user session for the users who are being audited. So, before their audit records can be captured, the users must connect to the database after the policy has been created.

To test the audit settings:

  1. In SQL*Plus, connect as user OE and query the OE.CUSTOMERS table so that you can generate a record for the audit trail.

    connect OE
    Enter password: password
    
    SELECT COUNT(*) FROM CUSTOMERS;
    
    COUNT(*)
    --------
         319
    
  2. Connect as user HR and try to run this query.

    connect HR
    Enter password: password
    
    SELECT COUNT(*) FROM OE.CUSTOMERS;
    
    ERROR at line 1:
    ORA-00942: table or view does not exist
    

    At this point, the audit trail contains two records, one for a successful query by user OE and another for a failed query attempt by user HR.

  3. Connect as user SH and try to run the same query.

    connect SH
    Enter password: password
    
    SELECT COUNT(*) FROM OE.CUSTOMERS;
    
    ERROR at line 1:
    ORA-00942: table or view does not exist
    

    Because this user is not being audited, there should be no audit record for this action.

  4. Connect as user sec_admin.

    connect sec_admin
    Enter password: password
    
  5. Run then the following procedure.

    EXEC SYS.DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;
    

    If the audit trail mode is QUEUED, then audit records are not written to disk until the in-memory queues are full. This command explicitly flushes the queues to disk, so that you can see the audit trail records in the UNIFIED_AUDIT_TRAIL view.

  6. Enter the following statement to query the UNIFIED_AUDIT_TRAIL view:

    col dbusername format a12
    col sql_text format a30
    col event_timestamp format a38
    
    SELECT DBUSERNAME, SQL_TEXT, EVENT_TIMESTAMP
    FROM UNIFIED_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 for users OE, HR, and SH. 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:

    DBUSERNAME SQL_TEXT                          EVENT_TIMESTAMP
    ---------- --------------------------------- --------------------------------
    OE         SELECT COUNT(*) FROM CUSTOMERS    04-JAN-13 03.39.02.468963 PM 
    HR         SELECT COUNT(*) FROM OE.CUSTOMERS 04-JAN-13 03.38.05.974127 PM 
    

    Because the audit policy only applied to users OE and HR, there is no record for user SH's actions.

Step 5: Optionally, Remove the Components for This Tutorial

You can remove the components of this tutorial if you no longer need them.

To remove the audit settings for this tutorial:

  1. In Enterprise Manager, ensure that you are logged in as user sec_admin.

  2. Select Administration, then Security, and then Audit Settings to display the Audit Settings page.

  3. Select the SELECT_CUST_POL policy, click Disable, and then select Yes in the Confirmation dialog box.

  4. Select the SELECT_CUST_POL policy again, click Delete, and then select Yes in the Confirmation dialog box. Then click Yes again.

  5. Log out of Enterprise Manager and then log back in again as user SYS with the SYSDBA administrative privilege.

  6. From the Administration menu, select Security, and then select Users.

  7. Select the SEC_ADMIN user and then click Edit.

  8. In the Edit User : SEC_ADMIN page, select Roles.

  9. Select the Edit List button.

  10. In the Modify Roles page, move the AUDIT_ADMIN role to the Available Roles list and then click OK.

  11. In the Edit User : SEC_ADMIN page, select the Object Privileges tab.

  12. Select the EXECUTE privilege for the DBMS_AUDIT_MGMT package, and then click Delete.

  13. Click Apply.

Step 6: Optionally, 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 the database as user SYSTEM.

  2. From the Database home page, select Administration, then Security, then Users.

  3. Select the user sec_admin.

  4. Click Delete.

  5. In the Confirmation page, select Yes.

  6. Exit Enterprise Manager.