Configuring an Oracle Database Server

Versions: Oracle 10g and 11g

Collector: Oracle Database Collector

To configure an Oracle database server for the collection of log messages by TLC, complete the following steps:

1. The following file contains a SQL script that defines a package, a single Oracle user account, and related permissions:

<install_directory>\Schema\Oracle\Oracle_Database_Collector_Install_V0.sql

Open a text editor and change the account's password to a password of your choosing. When you create Log Sources for this Monitored Asset in TLC (see Working with Log Sources for an Oracle Database Collector), you will need this password.

2. To open Oracle SQL Developer, select: 

Start > Programs > Oracle > Oracle-[Oracle_instance] > Application Development > SQL Developer

Where [Oracle_instance] is the name of the Oracle database instance.

Note 

If you do not have Oracle SQL Developer, you may configure the server with Oracle SQL *Plus. To do so: 

1. Open Oracle SQL *Plus.

2. Connect to the Oracle database using the SYS account as DBA.

3. Run the following script:

<install_directory>\Schema\Oracle\ Oracle_Database_Collector_Install_V0.sql

4. Proceed to step 5.

3. To create a new connection for the Oracle database:
a. In the side bar, select the Connections tab and click the New Connection button.  
b. In the New / Select Database Connection dialog, enter a Connection Name.
c. In the Username field, enter SYS.
d. Enter the Password for the SYS account.
e. From the Role drop-down, select SYSDBA.
f. In the Hostname field, enter the host name or IP address of the Oracle server.
g. In the SID field, enter the name of the Oracle database.
h. Click Test.
i. If the test is successful, click Save to close the New / Select Database Connection dialog.
4. To add the Tripwire Log Center package to the Oracle database:
a. Open the following file in the TLC Manager installation directory:

<TLC_Manager_install_dir>\Schema\Oracle\Oracle_Database_Collector_Install_V0.sql

b. To run the script with the SYS user account, press CTRL + ENTER (or mark the whole script and press F5).
c. In the Connections panel, select the Packages group and click the Refresh button.

Oracle SQL Developer adds the TRIPWIRE_LOGCENTER package to the Packages group.

5. To open Oracle SQL*Plus:
a. Select: 

Start > Programs > Oracle > Oracle-[Oracle_instance] > Application Development > SQL*Plus

Where [Oracle_instance] is the name of the Oracle database instance.

b. Log in with the SYS user account.
6. To configure the AUDIT_TRAIL parameter, enter the following commands at the Oracle SQL*Plus command prompt:

ALTER SYSTEM SET audit_trail='db' SCOPE=SPFILE;

ALTER SYSTEM SET audit_sys_operations=true SCOPE=SPFILE;

SHUTDOWN IMMEDIATE;
STARTUP;

Tips 

To review the current settings for your AUDIT and AUDIT_TRAIL parameters, enter: 

SHOW PARAMETERS AUDIT;

If you enter audit_trail='db_extended', rather than audit_trail='db', Oracle will include the values from the SQL_TEXT and SQL_BIND columns in generated log messages. The SQL_TEXT column will contain the SQL sentence that generated the logged event.
7. In Oracle SQL Developer, enter the following commands in the tab for the new connection (created above) to enable the AUDIT parameters of your choosing. For example: 

AUDIT ALL BY ACCESS

AUDIT SESSION;

AUDIT DELETE ANY TABLE;

AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE;

AUDIT ALL;

AUDIT ALL PRIVILEGES;

AUDIT ALL ON DEFAULT;

AUDIT NETWORK;

For more information about AUDIT parameters, refer to your Oracle documentation.

8. (Optional) Now, you can enable Fine-Grained Auditing for any database table or view by creating policies.

To create a policy for a table or view, enter the following commands in the connection tab, mark the whole statement, and then press F5:

BEGIN

DBMS_FGA.add_policy(

<object_schema> => 'user',

<object_name> => 'object',

<policy_name> => 'policy_name',

<audit_condition> => 'table_field evaluator condition',

<audit_column> => 'field to audit',

<statement_types> => 'SELECT,INSERT,UPDATE,DELETE');

END;

Where:

<object_schema> is the name of the user account creating the policy (e.g. rwilson).

<object_name> is the database table or view to be audited (e.g. EMP).

<policy_name> is the name of the policy (e.g. SALARY_CHK_AUDIT1).

<audit_condition> is the condition determining when the policy will generate logs (e.g. SAL>500000).

<audit_column> is the applicable column or field (e.g. SAL).

<statement_types> are the statements with which the table or view will be audited.

Tip 

For optimal performance, you should regularly purge old log messages from the Oracle views cited in Maintaining your Oracle Auditing Views. 

Next

If you are performing initial configuration of your TLC environment, see Configuring your TLC Environment.

Otherwise, see Adding a Monitored Asset for a new Log Source.

Maintaining your Oracle Auditing Views

If you have a Monitored Asset that represents an Oracle database, you should complete the steps below whenever the total number of log messages in one of the following views exceeds 1,000,000.

sys.dba_audit_trail

sys.dba_common_audit_trail

sys.dba_fga_audit_trail

Note 

For this procedure, the Audit Logger must be assigned as an Output Destination in the properties dialog of the Oracle Asset. For more information, see Changing the Output Destinations for a Monitored Asset.

When the number of log messages in one of these views exceeds 1,000,000, complete the following steps: 

1. In the Audit Logger:
a. Run a search for 'raw' log messages collected from the Oracle database by specifying the database with the Monitored Assets drop-downs in the Query Criteria tab (see Querying the Audit Logger for 'Raw' Log Messages).
b. In the search results presented in the Raw Logs tab, make a note of the Timestamp for the oldest log message. Typically, the oldest log message appears as the first entry in the search results.
2. Delete all log messages from the view that are older than the Timestamp.