Auditing Database Activity

9 Auditing Database Activity

Unified auditing enables you to create policies that audit user behavior in the database in a variety of ways.

Topics:

About Auditing
Auditing is the monitoring and recording of selected user database actions.
Why Is Auditing Used?
Auditing enables activities such as enabling user accountability, deterring inappropriate user actions, and investigating suspicious activity.
Tutorial: Creating a Unified Audit Policy
In this tutorial, you create unified audit policy that monitors SELECT statements on the OE.CUSTOMERS table.

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

9.2 Why Is Auditing Used?

Auditing enables 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.

9.3 Tutorial: Creating a Unified Audit Policy

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.
Step 2: Grant the SEC_ADMIN User the AUDIT_ADMIN Role
Next, you are ready to grant the sec_admin user the AUDIT_ADMIN role, which enables SEC_ADMIN to create audit policies.
Step 3: Create and Enable a Unified Audit Policy
In Enterprise Manager as user sec_admin, you can create a unified auditing policy for SELECT statements on the OE.CUSTOMERS table.
Step 4: Test the Unified Audit Policy
With auditing enabled, you are ready to test the audit settings.
Step 5: Optionally, Remove the Components for This Tutorial
You can remove the components of this tutorial if you no longer need them.
Step 6: Optionally, Remove the SEC_ADMIN Security Administrator Account
If you no longer need the sec_admin administrator account, then you should remove it.

9.3.1 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:

Log in to SQL*Plus as user SYS with the SYSDBA administrative privilege.
```
sqlplus sys as sysdba
Enter password: password
```
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.
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
```
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.
Go to the $ORACLE_HOME/rdbms/lib directory.
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.
Restart the listener.
```
lsnrctl start listener_name
```
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
```

9.3.2 Step 2: Grant the SEC_ADMIN User the AUDIT_ADMIN Role

Next, 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:

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.
From the Schema menu, select Users.
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.
In the Edit User: SEC_ADMIN page, select the Roles tab.
Select the Edit List button.
In the Modify Roles page, select AUDIT_ADMIN from the Available Roles list, and then move it to the Selected Roles list. Click OK.
In the Edit User: SEC_ADMIN page, select the Object Privileges tab.
In the Edit User page: SEC_ADMIN page, from the Object Type list, select Package, and then click Add.
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.
In the Edit User page: SEC_ADMIN page, click Apply.

9.3.3 Step 3: Create and Enable a Unified Audit Policy

In Enterprise Manager as user sec_admin, you can create a unified auditing policy for SELECT statements on the OE.CUSTOMERS table.

To create the unified audit policy:

In Enterprise Manager, log out and then log back in again as user sec_admin.
From the Security menu, select Audit Settings.
In the Audit Settings page, click Create.
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.
Click the Next button.
In the Create Audit Policy : Component Actions page, click Next.
In the Create Audit Policy : Object Actions page, click Add Object.
In the Add Object dialog box, enter the following settings:
- Schema: OE
- Type: TABLE
- Object: CUSTOMERS
- Action: SELECT
Click OK.

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

Description of the illustration audit_create.gif
In the Create Audit Policy : Object Actions window click Next.
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.
Select SELECT_CUST_POL and then click the Enable button.
In the Enable Audit Policy dialog box, select the Few Users check box.

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

Description of the illustration audit_enable.png
Click the Add User button.
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.
Click OK.
Repeat these steps to add user HR to the list.
Do not exit Enterprise Manager.

9.3.4 Step 4: Test the Unified Audit Policy

With auditing enabled, you are ready to test the audit settings.

Any SELECT statements that are performed on the OE.CUSTOMERS table will be written to the unified audit trail. To find the audit records, you must query the UNIFIED_AUDIT_TRAIL dynamic view.

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:

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

Connect as user sec_admin.

connect sec_admin
Enter password: password

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

9.3.5 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:

In Enterprise Manager, ensure that you are logged in as user sec_admin.
Select Administration, then Security, and then Audit Settings to display the Audit Settings page.
Select the SELECT_CUST_POL policy, click Disable, and then select Yes in the Confirmation dialog box.
Select the SELECT_CUST_POL policy again, click Delete, and then select Yes in the Confirmation dialog box. Then click Yes again.
Log out of Enterprise Manager and then log back in again as user SYS with the SYSDBA administrative privilege.
From the Schema menu, select Users.
Select the SEC_ADMIN user and then click Edit.
In the Edit User : SEC_ADMIN page, select Roles.
Select the Edit List button.
In the Modify Roles page, move the AUDIT_ADMIN role to the Available Roles list and then click OK.
In the Edit User : SEC_ADMIN page, select the Object Privileges tab.
Select the EXECUTE privilege for the DBMS_AUDIT_MGMT package, and then click Delete.
Click Apply.

9.3.6 Step 6: Optionally, Remove the SEC_ADMIN Security Administrator Account

If you no longer need the sec_admin administrator account, then you should remove it.

To remove the sec_admin security administrator account:

Log in to the database as user SYSTEM.
From the Database home page, select Schema, then Users.
Select the user sec_admin.
Click Delete.
In the Confirmation page, select Yes.
Exit Enterprise Manager.