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
Setting Up ODBC
To provide for collection of Oracle audit trails, you have to install and tune the Oracle ODBC driver on the Windows-based computer responsible for data gathering. You can either install the driver from your Oracle Client CD or download and configure Oracle Instant Client.
Installing ODBC driver from the Oracle Client CD
To install and configure Oracle ODBC driver from the Oracle Client CD, for example, for Oracle 9i Release 2:
- When running the setup, select the Custom installation type.
- Select components to install:
- Oracle Windows Interfaces 184.108.40.206.0—this component contains Oracle ODBC driver
- Oracle Network Utilities 220.127.116.11.0—this component will help you create TNS names automatically with Net Manager
- SQL *Plus 18.104.22.168.0—this component can be used to verify database connection settings
- Follow the installation steps. During the installation, you should specify the Oracle home directory (default value is OraHome92).
- When the installation is complete, run Net Manager by selecting Start | Program Files | Oracle in [Oracle home directory] | Configuration and Migration Tools | Net Manager.
- Select Local | Service Naming, and then click Create. Follow the Net Service Name Wizard to configure the net service name.
- After you complete the wizard, save the configuration.
Downloading Oracle Instant Client
Note: To follow the links specified below, you need to be a register an account on the Oracle web site.
- Download “Instant Client Package—Basic” and “Instant Client Package—ODBC” for Microsoft Windows from https://www.oracle.com/database/technologies/instant-client.html.
- Carry out the installation procedure described in the Instant Client release notes (https://www.oracle.com/database/technologies/releasenote-odbc-ic.html), in particular:
- Unpack both Instant Client packages into a target directory, for example, c:\Oracle\InstantClient.
- In the PATH system environment variable, specify the target directory name.
- In a command shell window (DOS-like), run the odbc_install.exe file from the package.
- After its execution, in the ORACLE_HOME and TNS_ADMIN system environment variables, specify the target directory name (C:\Oracle\InstantClient for our example).
- Generate a TNSNAMES.ORA file, either using the Net Manager (choose Local Net Service Name Configuration) on your Oracle server, or manually. This file will look like the following:
(ADDRESS = (PROTOCOL = TCP)(HOST = MyOracleServer)(PORT = Port)
(SERVICE_NAME = MyOracleService)
Here you need to specify:
- TNS name—can be the same as the database name; this name will be later used in the connection string to identify the database to connect to
- Oracle Server name—the computer hosting Oracle database (for example, MyOracleServer); an IP address can be used
- Port—the default is 1521
- Oracle service name—the database to connect to (for example, MyOracleService)
- Save the file, and reboot the computer.
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 11g DB-based log", "Oracle 10g DB-based log" and "Oracle 9i 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
- In InTrust Manager, select Configuration | Data Sources, select the data source you need, and from its shortcut menu, select Copy.
- Then modify the copy: select Properties from the shortcut menu, click General.
- Edit the data source name and description.
- 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.
- 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.
- Specify the password for connection.
Note: If you enter the password explicitly in the connection string, it will be stored as plain text and appear to other users of InTrust Manager. To prevent unauthorized access to this data, it is recommended that you use the %PASSWORD% keyword instead. This keyword stands for the password to be used for connection. Supply the password in the text box on the same step of the wizard. It will be securely kept in the InTrust configuration database and substituted at connection time.
- 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.
- 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"
- 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.
- 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.
- Click OK to save the changes.
To create a new data source
- In InTrust Manager, select Configuration | Data Sources, and from the shortcut menu, select New Data Source.
- 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.
- 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:
- 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.
- Click Create.
- From the list of drivers, select your Oracle ODBC driver; specify user credentials for database connection and the TNS name.
- 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.
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:
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.