A Auditing Oracle Database Vault

Oracle Database Vault Specific Audit Events

This section contains:

• Oracle Database Vault Audit Events

• Format of the Oracle Database Vault Audit Trail

Archiving and Purging the Oracle Database Vault Audit Trail

You can create an archive of the Oracle Database Vault audit trail by exporting the AUDIT_TRAIL$ system table, which is owned by DVSYS, to a dump file. You should periodically archive and then purge the audit trail to prevent it from growing too large.

To archive and purge the Oracle Database Vault audit trail:

1. Ensure that Data Pump Export is installed.

   Log on to SQL*Plus with administrative privileges and then run the following query:

   sqlplus sys as sysdba
   Enter password: password
   
   SQL> SELECT ROLE FROM DBA_ROLES WHERE ROLE LIKE '%FULL%'
   

   If the query does not return the EXP_FULL_DATABASE and IMP_FULL_DATABASE roles, then Data Pump Export is not installed. To install Data Pump Export, run either the catexp.sql or catalog.sql script. For example:

   @/oracle/app/oracle/admin/catexp.sql;
   

   See Oracle Database Utilities for more information about the Export utility.

2. Disable Oracle Database Vault.

   See "Step 1: Disable Oracle Database Vault" for more information.

3. If the DVSYS account is locked, then unlock it.

   You must have this account unlocked when you run the EXPDP export command. Ensure that you are still logged on as SYS, connecting AS SYSDBA.

   SQL> ALTER USER DVSYS IDENTIFIED BY password ACCOUNT UNLOCK;
   

4. At the operating system command prompt, create a directory for the Oracle Database Vault audit trail (for example, in $ORACLE_BASE/admin/$DB_UNIQUE_NAME/dvaudit).

   You may want to keep this directory in the same location as the operating system audit trail directories for Oracle Database, which by default is in the $ORACLE_BASE/admin/$DB_UNIQUE_NAME/adump directory. In SQL*Plus, you can check the location of the audit trail directory as follows:

   SQL> SHOW PARAMETER AUDIT_FILE_DEST
   
   NAME                 TYPE     VALUE
   -------------------  -------  ---------------------------------------
   audit_file_dest      string   /opt/oracle/app/oracle/admin/orcl/adump
   

5. In SQL*Plus, create a directory object in which to generate the Oracle Database Vault audit trail. To do so, connect as SYS or as any user who has the CREATE ANY DIRECTORY privilege.

   For example:

   SQL> CREATE DIRECTORY dv_audit_dir AS '/opt/oracle/app/oracle/admin/orcl/dvaudit';
   

   Enclose the directory path in single quotation marks, not double quotation marks.

6. Grant read and write privileges on the directory object to user DVSYS.

   For example:

   SQL> GRANT READ, WRITE ON DIRECTORY dvaudit TO dvsys; 
   

7. At the operating system command prompt, enter a command similar to the following to export the DVSYS.AUDIT_TRAIL$ audit table into a new dump file.

   EXPDP DVSYS
   Enter password: password
   DIRECTORY=dvaudit \
   TABLES=DVSYS.AUDIT_TRAIL$ \
   QUERY=DVSYS.AUDIT_TRAIL$:"WHERE timestamp < 2009-08-03:19:34:59" 
   DUMPFILE=dv_audit_031607.dmp
   

   In this specification:

   • DIRECTORY: Enter the directory object that you created in Step 5. Ensure that that the user who is running EXPDP (DVSYS in this example) has read and write permissions on this directory object. If you created the directory object, then you automatically have read and write permissions on it.

   • TABLES: Enter DVSYS.AUDIT_TRAIL$, the name of the audit trail table.

   • QUERY: Optional. This setting writes a subset of the audit table contents to the dump file, in this case, audit records that are less than the timestamp column value of 2009-08-03:19:34:59.

   • DUMPFILE: Enter the name of the dump file that you want to create. The default extension is .dmp, but you can use any extension. Ensure that the file name you specify is unique.

8. In SQL*Plus, purge the Oracle Database Vault audit trail table, now that you have archived it.

   For example, if you archived all audit trail records that are less than the timestamp column value of 2009-08-03:19:34:59, enter the following statement:

   SQL> DELETE FROM DVSYS.AUDIT_TRAIL$ WHERE TIMESTAMP < 2009-08-03:19:34:59;
   

   To completely purge the audit trail and remove the extents allocated to the audit trail table, enter the following statement:

   SQL> TRUNCATE TABLE DVSYS.AUDIT_TRAIL$;
   

9. Exit SQL*Plus.

10. Re-enable Oracle Database Vault.

    See "Step 3: Enable Oracle Database Vault" for more information.

Oracle Database Audit Settings Created for Oracle Database Vault

When you install Oracle Database Vault, it creates a number of AUDIT settings in the database. However, in order for these audit settings to take place, auditing must be enabled in this database. You can check if auditing is enabled by using the SHOW PARAMETER command to find the value of the AUDIT_TRAIL initialization parameter. By default, auditing is disabled in Oracle Database.

If the AUDIT_TRAIL parameter is set to NONE, then auditing is not enabled, so you must set AUDIT_TRAIL. For detailed information about the AUDIT_TRAIL parameter, see Oracle Database Security Guide and Oracle Database Reference.

Table A-2 lists the AUDIT settings that Oracle Database Vault adds to the database.