Configuring an Oracle Database Server

Versions: Oracle 10g and 11g

CollectorA TLC module that gathers or receives log messages from Log Sources.: Oracle Database CollectorA type of Collector that gathers log messages from Oracle database audit logs.

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 AssetAn object in the TLC Configuration Manager that represents a Log SourceAny log-generating application, operating-system service, database instance, or device from which TLC collects log messages. from which TLC collects log messages directly. 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 LogCenter package to the Oracle database:

a.

Open the following file in the TLC ManagerTripwire LogCenter Manager is the core software in your TLC environment. TLC Manager collects and processes log messages from a wide variety of systems and devices. 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 environmentConsists of all TLC software, Managers, Log Sources, Monitored Assets, Collectors, and data in your TLC installation., 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 LoggerThe TLC Console1. Tripwire LogCenter Console is the software for the TLC graphic user interface (GUI), or 2. The Tripwire LogCenter GUI. Through the TLC Console, you can configure TLC, oversee your TLC environment, and manage log and event data. component in which you can work with the log messages collected by TLC. must be assigned as an Output DestinationAssigned to a Monitored Asset, an Output Destination is either the Audit Logger, an Event1. Either a log message that a Manager has standardized (i.e. normalized) for use by TLC (a.k.a. Normalized Messages), or an event or vulnerability imported from a scanner. 2. An 'event message' collected from a Log Source.-Management Database, or a CorrelationThe examination of Normalized Messages for events of interest, along with the ability to initiate appropriate responses; for example, sending an email notification to specified recipients. Engine that correlates Normalized Messages. 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 messageA data record generated by a Log Source and collected by TLC.. 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.