InTrust 11.4.2 - Preparing for Auditing Oracle

Gathering from Unix-Based Computers

On a Unix-based computer hosting Oracle database, events from administrative users are written to the Oracle audit log files in text format. You can use InTrust to collect this data from Oracle hosts running on Linux or Unix machines. The InTrust gathering policy is named ‘Oracle for Unix administrative users events’ and treats the Oracle audit log as a Oracle Text Log data source of Custom Text Log type. You will have to customize the corresponding template, as described later in this section.

It is recommended that you use InTrust agents for gathering. Agents should be installed manually on target Unix machines. Install the agent and establish connection with InTrust Server as described in detail in Installing Agents Manually.

After you have deployed the agent on the target computer, include this computer in the Oracle for Unix Servers site.

To collect Oracle audit log from Unix-based computer

1. Find the path to the Oracle audit log file you want to collect. For that:
   1. Run the following command from the command prompt (SQLPlus must be installed on your computer):
      sqlplus /nolog
   2. Connect to the database, using the following command:
      connect username/password@TNSName as SYSDBA
      where
      • Username, password—credentials for database connection
      • TNSName—database you want to connect to
   3. Next, run the following query:
      select name, value from v$parameter where name=’audit_file_dest’
      The Oracle log path will look like the following example:
      /u01/app/oracle/product/10.2.0/db_1/rdbms/audit
2. Check whether the log files are generated as a result of administrative activity.
3. In InTrust Manager, select Configuration | Data Sources, on the right pane, select Oracle Text Log, and create a copy of it. Right-click the copy and select Properties.
4. On the General tab, supply a name and description for the new data source.
5. On the Settings tab, make sure the log name is Oracle Audit Log. Click Edit.
   In the Path to text log file text box, supply the path you obtained on step 2.
   Click Next.
6. In the Regular Expressions list, select the first expression (it is of Data type), and click Edit.
7. In the Field mapping list, click Add, and add Insertion String #7. Specify your Oracle database name as the value for this field. Click OK, and then finish the wizard.
8. If you are gathering with agents, configure the “Oracle for Unix administrative users events” gathering policy, specifying the Custom Text Log you customized on steps 3 through 6 as the data source.
9. Configure the “Oracle Daily Collection” task, as follows:
   1. Disable all unnecessary jobs by clearing the Enabled check box on the job’s General tab.
   2. Make sure that the “Oracle for Unix administrative users audit collection” job is enabled.
   3. Go to the job’s Gathering tab, and specify the site you configured on step 8. Select the Use agents to execute this job on target computers check box.
   4. From the task’s shortcut menu, select Properties, and modify them as necessary.

Gathering logs from a Unix computer without an agent has the following differences from the workflow described above:

• The directory with the log must be available as an SMB share.
• The path to this SMB share must be specified in the using the %COMPUTER_NAME% variable and the share name (\\%COMPUTER_NAME%\share_name).
• The host must be a member of an InTrust site in the Configuration | Sites | Microsoft Windows Network container. InTrust currently supports gathering from network shares only in Microsoft Windows Environment sites; this workaround makes InTrust aware of the share even though the processed computer is not actually running Windows.
• A separate InTrust gathering policy under the Gathering | Gathering Policies | Microsoft Windows Network node must be created.
• The gathering job must use the custom site and gathering policy described above, instead of the predefined Oracle-related site and policy, which are intended for gathering with agents.
• The Use agents to execute this job on target computers check box must be turned off in the gathering job.

Auditing the Standard Audit Trail

Oracle database logs are retrieved from the SYS.AUD$ table in the database and communicated using the ODBC driver installed on the Windows-based computer that performs the InTrust gathering process. In other words, this computer operates like an “Oracle ODBC proxy”.

Generally, an Oracle ODBC proxy (with ODBC driver installed) can be one of the following:

• A computer where InTrust Server runs
• A dedicated Windows-based computer—in this case you need to deploy an InTrust agent on it

It is recommended that you use a dedicated machine for this purpose. Otherwise, the InTrust server is responsible for both communication with Oracle and gathering events, and this can lead to server overload.

Consider organizing Oracle ODBC proxy computers into the “Oracle ODBC computers” site; InTrust agents will run the gathering service on them.

To gather Oracle database log data, take the following steps:

1. Turn on auditing of the Oracle hosts, as described in the following section.
2. In InTrust Manager, customize the Database Log Template by setting up the data source and connection settings for Oracle ODBC proxy, as described below.
3. Verify the connection settings.
4. Modify the InTrust predefined objects (site, policy, and task) for Oracle database log collection, as described below.

Turning on Auditing of an Oracle Host

1. 1 Connect to Oracle database as SYSDBA (use Oracle Enterprise Manager, or run an SQL query described below). For example, if you have SQLPlus installed on your computer, you can run the following command in the command prompt:
   sqlplus /nolog
   Then run the following command:
   connect username/password@TNSName as SYSDBA
   where
   • username, password—credentials for the database connection
   • TNSName—the database you want to connect to
2. Then you can run the following query:
   select name, value from v$parameter where name like ‘audit%’
3. Check the AUDIT_TRAIL value returned by the query. It must be set to the following:
   • For Oracle 9i: DB
   • For Oracle 10g: DB_EXTENDED

If not, run the corresponding query, as follows:

• For Oracle 9i:
  ALTER SYSTEM SET AUDIT_TRAIL = "DB" SCOPE=SPFILE;
• For Oracle 10g and 11g:
  ALTER SYSTEM SET AUDIT_TRAIL = "DB_EXTENDED" SCOPE=SPFILE;
• For Oracle 11g:
  ALTER SYSTEM SET AUDIT_TRAIL = 'DB','EXTENDED' SCOPE=SPFILE;

4. Wait for the system to report that it has been altered, and restart Oracle database.
5. Run the command necessary to provide the data for the report you need. For details, see the following section.

Fine-Tuning Audit Options for Reports

Oracle has a very flexible auditing configuration system with many options. To see what audit options are currently enabled, run:

SELECT AUDIT_OPTION, SUCCESS, FAILURE FROM DBA_STMT_AUDIT_OPTS;

The output should be similar to the following:

AUDIT_OPTION SUCCESS FAILURE

---------------------------------------- ---------- ----------

ALTER SYSTEM BY ACCESS BY ACCESS

SYSTEM AUDIT BY ACCESS BY ACCESS

CREATE SESSION BY ACCESS BY ACCESS

TABLE BY ACCESS BY ACCESS

CLUSTER BY ACCESS BY ACCESS

TABLESPACE BY ACCESS BY ACCESS

USER BY ACCESS BY ACCESS

ROLLBACK SEGMENT BY ACCESS BY ACCESS

TYPE BY ACCESS BY ACCESS

INDEX BY ACCESS BY ACCESS

CREATE USER BY ACCESS BY ACCESS

Add and remove audit options as necessary. For example, to start getting data for the "User Logons and Logoffs" report, run:

AUDIT SESSION

To disable auditing of a particular feature, run something like the following:

NOAUDIT CLUSTER

If you want to create your audit option configuration from scratch, you can start by turning off all audit options, as follows:

NOAUDIT ALL

See the descriptions of the reports you need to find out their auditing requirements.