InTrust 11.5.1 - Preparing for Auditing Oracle

Configuring the Oracle ODBC Proxy

Oracle ODBC driver (8.0.5 or later) must be installed on the Oracle ODBC proxy (that is, the computer where gathering process will run).

• If the process is agentless, then the Oracle ODBC proxy is the computer where InTrust Server resides.
• If agents are used (recommended), then the Oracle ODBC proxy is the computer where the InTrust agent is installed.

Setting Up ODBC

Configuring Database Log Template

Configuring Database Log Template

To simplify the database log gathering process, InTrust Manager offers a number of predefined data sources, in particular, the Oracle log gatherer templates: "Oracle 18c DB-based log", "Oracle 19c DB-based log" and "Oracle 21c DB-based log" for the corresponding Oracle versions.

A data source contains the following:

• Connection settings that the ODBC driver will use when accessing the Oracle database (connection string, password for database access, and so on)
• The SQL query that will be used to retrieve data from the database
• The SQL Cleanup query that will be used to clear data already gathered

You can customize the existing data source or create a new one. For useful details about the procedures briefly outlined here, see Custom Text Log Data Sources.

To customize a data source

1. In InTrust Manager, select Configuration | Data Sources, select the data source you need, and from its shortcut menu, select Copy.
2. Then modify the copy: select Properties from the shortcut menu, click General.
3. Edit the data source name and description.
4. Open the Connection String tab. The Log name is the one that InTrust will give to the log with gathered events (InTrust for Oracle Audit log). This descriptive name will be used to identify corresponding events in the InTrust audit database (for example, to create custom filters for the DB-based log). This name does not need to be modified.
5. Edit the ODBC connection string. If InTrust Manager is running on your Oracle ODBC proxy (that is, the computer with Oracle ODBC driver installed), the connection string will be generated automatically. If InTrust Manager and Oracle ODBC proxy are running on different computers, you will need to create the connection string manually. Make sure you have specified the ODBC driver name, database access credentials, and the TNS name. For details, see Specifying a Connection String below.
   You can use the Keyword button to insert predefined keywords.
6. Specify the password for connection.

7. Decide on the database field that will be used for data sorting. It is strongly recommended that you choose the field that contains the date and time, because InTrust storage is designed for data sorted by date.
8. On the SQL Query tab, enter the SQL query that retrieves necessary data from the database. Ensure ordering by the field you chose on step 7. For example, if the field is called TIMESTAMP, include this:
   order by "TIMESTAMP"
9. In the Field mapping list, configure the matching between the original database fields and those that InTrust stores. This governs how the retrieved data is arranged for storage. Map the LAST_GATHERED_EVENT InTrust field to the database field you chose on step 7.
10. On the SQL Cleanup Query tab, supply an SQL query to be executed after gathering. This query should clear gathered events from the database. The query is not run by default. To make it run, you will have to enable the Clear log files after gathering option for the DB-based log data provider in the gathering policy that uses the template.
11. Click OK to save the changes.

To create a new data source

1. In InTrust Manager, select Configuration | Data Sources, and from the shortcut menu, select New Data Source.
2. On the first step of the New Data Source Wizard you are prompted for the ODBC connection string. Specify the connection string, as described in the previous procedure.
   For details, see the Specifying a Connection String below.
3. Follow the steps of the wizard. You will be prompted for the same data as if you were modifying the data source. Refer to the procedure above for details.

Specifying a Connection String

A connection string must contain the following:

• A driver name—your Oracle ODBC driver (for example, Microsoft ODBC for Oracle)
• A server to communicate with—the TNS name you configured in the TNSNAMES.ORA file
• A UID (user name)—a user name to be used when connecting to the database
• A password—the user’s password for connecting to the database

The connection string can be created automatically if the ODBC driver is installed on the same computer as InTrust Manager. Here is a sample procedure:

1. When creating a new data source, you will be prompted for connection string on the first step of the New Data Source Wizard. When modifying a connection string for the existing data source, in InTrust Manager | Configuration | Data Sources, select the data source, and from its shortcut menu, select Properties. Go to the Connection String tab.
2. Click Create.
3. From the list of drivers, select your Oracle ODBC driver; specify user credentials for database connection and the TNS name.
4. After the connection string is generated and verified, it appears in the ODBC connection string text box of the Properties dialog.

If you decide to create or modify the connection string manually when creating or modifying a data source, make sure you have specified the ODBC driver name, database access credentials, and the TNS name. You can use the Keyword button to insert predefined keywords.

InTrust Applications - Oracle 64-bit Server Communication

For 18C, 19C and 21c Oracle 64-bit servers, download the corresponding 32-bit oracle client software and follow the steps as described below:

1. Oracle 32-bit client should be installed along with
   • InTrust Server.
   • InTrust Manager (if it is installed on a separate machine instead of a server machine)
   • Oracle Server (if you want to create a site for Oracle server PCNote: Here “ORCL” is used as a Service name during the oracle server installation. If any different name is used during the installation, please use that name in the below-mentioned procedure instead of “ORCL”.

Oracle Server Configuration changes

Edit listener.ora & tnsnames.ora files as mentioned below.

listener.ora:

1. Open ..\network\admin\listener.ora file.
2. Modify HOST to 0.0.0.0 IP and save it.

tnsnames.ora:

1. Open ..\network\admin\tnsnames.ora file.
2. Modify HOST to Local machine IP address under Listener_ORCL.
3. Modify HOST to IT2019.diana.local and SERVICE_NAME to orcl.diana.local under ORCL.
4. Here, IT2019 is the oracle server installed computer name, orcl is the Oracle service name and diana is the domain name. Make sure all details are correct as per the working environment.

Oracle 21c 32-bit client installation

Download and install 32-bit oracle 21c client NT_213000_client_home.zip

Oracle 19c 32-bit client installation

Download and install 32-bit oracle 19c client NT_193000_client_home.zip

Oracle 18c 32-bit client installation

1. Download “Instant Client Package—Basic” and “Instant Client Package—ODBC” for Microsoft Windows from https://www.oracle.com/in/database/technologies/instant-client/microsoft-windows-32-downloads.html
2. Unpack both Instant Client packages into a target directory, for example, c:\InstantClient.
3. In the PATH system environment variable, specify the target directory name.
4. In a command shell window (DOS-like), run the odbc_install.exe file from the package.
5. After its execution, add ORACLE_HOME and TNS_ADMIN system environment variables and specify the target directory name (C:\InstantClient for our example).
6. Copy TNSNAMES.ORA and LISENER.ORA in (C:\InstantClient) directory from Oracle server \network\admin\ location.

InTrust Manager Configuration Changes

1. Restart the oracle services.
2. In Quest InTrust Manager | Configuration | Data Sources, add a new Oracle 21c/19c/18c DB-based log data source, or if available copy the existing oracle data source and rename it to Oracle 21c/19c/18c DB-based log.
3. Right-click on the Oracle log and select Properties.
4. On the Connection String tab, click Create and select Oracle ODBC driver and click OK.
5. Enter Service Name, User Name, and Password, and click OK.
6. Please enter the correct oracle server machine name, port, and oracle service name as per the installed environment.
   • Service Name Format: oracle server machine name:port/oracle service name.
   • Example: IT2019:1521/orcl
7. 7. Create a New Site and Policy for Oracle 19c, add New Task, and run this job.

Gathering Data from Multiple Databases

To gather data from multiple Oracle databases, you can either use a separate data source for each database, or use a single data source. When tuning the data collection process, consider the following:

• You will need a separate Oracle ODBC proxy for each database. Include these computers in the ‘Oracle ODBC computers’ site.
• In the TNSNAMES.ORA file, configure the TNS Name for each Oracle ODBC proxy as its computer name.
• When configuring the connection string in the Database Log template, specify the server name using the keyword:
  SERVER=%COMPUTER_NAME%
  When you connect to the database, this keyword will be replaced with the Oracle ODBC proxy name.
• Since the data source will use the same connection string (and, thus, the same credentials for database access) for all databases, make sure this user account is granted access rights to these databases.

It is recommended that you verify the connection string on the Oracle ODBC proxy side. To test the connection string, you can use, for example, ODBC Data Source Administration.