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:
Oracle Database Security Guide for other ways that you can audit user and database activities
Oracle Database Security Guide for important guidelines for creating audit policies
Oracle Audit Vault and Database Firewall Administrator's Guide for information about advanced auditing features
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.
Oracle Database Upgrade Guide for information about migrating to unified auditing and about mixed mode auditing
Oracle Database Security Guide for information about archiving and purging the audit trail
Oracle Database Security Guide for information about fine-grained auditing
Oracle Database Security Guide for more information about the predefined audit policies
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.
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:
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
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:
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 Administration menu, select Security, then 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:
In the Select Package Objects field, enter SYS.DBMS_AUDIT_MGMT
.
Under Available Packages, select EXECUTE and then click Move to send it to the Selected Privileges list.
Click OK.
In the Edit User page: SEC_ADMIN page, click Apply.
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:
In Enterprise Manager, log out and then log back in again as user sec_admin
.
From the Administration menu, select Security, and then 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:
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, deselect the Enable Audit Policy For All Users check box.
The Enable Audit Policy dialog expands so that you can add specific users.
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.
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:
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.
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 Administration menu, select Security, and then 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.
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:
Log in to the database as user SYSTEM
.
From the Database home page, select Administration, then Security, then Users.
Select the user sec_admin
.
Click Delete.
In the Confirmation page, select Yes.
Exit Enterprise Manager.