Configure Oracle Database 11g for Auditing

This section explains how to configure Standard Auditing on your Oracle Database 11g, preparing for monitoring with Netwrix Auditor.

NOTE: Starting with version 9.96, Netwrix Auditor provides limited support of Oracle Database 11g. See Considerations for Oracle Database Auditing for more information.

IMPORTANT! Verify that Oracle Data Provider for .NET and Oracle Instant Client are installed and properly configured on the computer where Netwrix Auditor Server is installed. Netwrix Auditor does not provide any special notification for that.

To configure Standard Auditing on your Oracle Database 11g, take these steps:

  1. Select the audit trail to store audit records. Oracle Database has the following options:

    • Database audit trail— Set by default.
    • XML audit trail— Recommended.
    • OS files—Not supported by current version of Netwrix Auditor.
  2. Enable auditing of Oracle Database changes, using the corresponding command.

Select audit trail to store Oracle audit records

  1. On the computer where your database is deployed, run the sqlplus tool.
  2. Connect to your Oracle Database using Oracle account with the SYSDBA privilege. For example:

    OracleUser as sysdba

    Enter your password.

  3. Depending on where you want to store audit records, execute the required command.
Store to... Execute...

Store audit records to XML audit trail (recommended).

NOTE: Use this audit trail if you want Netwrix Auditor to report on actions performed by users with SYSDBA and SYSOPER privileges. Otherwise, these actions will not be audited.

ALTER SYSTEM SET audit_trail=XML SCOPE=SPFILE;

If you want to enable auditing of actions performed by SYS user and by users connecting with SYSDBA and SYSOPER privileges, execute:

ALTER SYSTEM SET audit_sys_operations=TRUE SCOPE=SPFILE;

Database audit trail (default setting)

NOTE: In this case, actions performed by user SYS and users connecting with SYSDBA and SYSOPER privileges will not be audited.

ALTER SYSTEM SET audit_trail=DB SCOPE=SPFILE;

 

Store audit records to XML or database audit trail and keep full text of SQL-specific query in audit records.

NOTE: Only ALTER actions will be reported.

For database audit trail:

ALTER SYSTEM SET audit_trail=DB, EXTENDED SCOPE=SPFILE;

For XML audit trail:

ALTER SYSTEM SET audit_trail=XML, EXTENDED SCOPE=SPFILE;

  1. If you turned auditing on or off, you will need to restart the database. For that, run the following:

    SHUTDOWN IMMEDIATE

    STARTUP

NOTE: If you only changed auditing settings, database restart is not required.

NOTE: If you are using Oracle Real Application Clusters (RAC), see the Starting and Stopping Instances and Oracle RAC Databases section in Real Application Clusters Administration and Deployment Guide for more information on restarting your instances.

Enable auditing of Oracle Database changes

  1. On the computer where your database is deployed, run the sqlplus tool.
  2. Connect to your Oracle Database—use Oracle account with the SYSDBA privilege. For example:

    OracleUser as sysdba

    Enter your password.

  1. Depending on your monitoring requirements, enable auditing of the database parameters with the related command.

    To monitor for... Execute...

    Configuration
    changes

    • For any user:

    AUDIT ALTER SYSTEM,SYSTEM AUDIT,SESSION,TABLE,USER,
    VIEW,ROLE,PROCEDURE,TRIGGER,PROFILE,DIRECTORY,
    MATERIALIZED VIEW,SYSTEM GRANT,NOT EXISTS,
    ALTER TABLE,GRANT DIRECTORY,GRANT PROCEDURE,
    GRANT TABLE;

    AUDIT ALTER DATABASE, FLASHBACK ARCHIVE ADMINISTER;

    NOTE: If you want to disable configuration auditing, use the following commands:

    NOAUDIT ALTER SYSTEM,SYSTEM AUDIT,SESSION,
    TABLE,USER,VIEW,ROLE,PROCEDURE,TRIGGER,PROFILE,
    DIRECTORY,MATERIALIZED VIEW,SYSTEM GRANT,
    NOT EXISTS,ALTER TABLE,GRANT DIRECTORY,
    GRANT PROCEDURE,GRANT TABLE;

    NOAUDIT ALTER DATABASE,
    FLASHBACK ARCHIVE ADMINISTER;

    • For specific user:

    AUDIT SYSTEM GRANT, SESSION, TABLE, PROCEDURE BY <USER_NAME>;

    NOTE: You can specify several users separated by commas.

    Successful data access and changes

    AUDIT SELECT,INSERT,DELETE,UPDATE,RENAME,
    FLASHBACK ON <TABLE_NAME> BY ACCESS WHENEVER SUCCESSFUL;

    Failed data access and changes

    AUDIT SELECT,INSERT,DELETE,UPDATE,RENAME,
    FLASHBACK ON <TABLE_NAME>
    BY ACCESS WHENEVER NOT SUCCESSFUL;

    For additional information on ALTER SYSTEM and AUDIT parameters, see the following Oracle database administration documents:

After an audit parameter has been enabled or disabled, Netwrix Auditor will start collecting data after successful logon session.

Also, remember to do the following: