7 Configuring and Using Local Monitoring

This chapter contains:

• About Local Monitoring

• Installing Local Monitoring

• Enabling Local Monitoring

• Disabling Local Monitoring

About Local Monitoring

The local monitoring software enables an enforcement point to monitor (but not block) SQL traffic that originates from sources that have direct access to the database, such as console users or batch jobs that run on the database server. Local monitoring does not send traffic across the network. You install the Oracle Database Firewall local monitoring software directly into the database that you are monitoring. Local monitoring uses an additional table in the database, which logs:

• The last statement sent to the database by a console user or other process.

• All statements originating from console users or processes that affect the data in the database, such as ALTER TABLE and DROP TABLE operations. Mechanisms including triggers (Oracle and Sybase) and event notifications (Microsoft SQL Server) log such statements.

The enforcement point collects the data by querying the database at regular intervals, then uses the data in the same manner as statements originating from database clients. Depending on the design of the policy, the statements may be logged or produce warnings, but since local monitoring is not inline between the traffic and database, the statements cannot be blocked. Logged traffic from the Local Monitor has its source IP address as 0.0.0.0.

Oracle Database Firewall supports local monitoring for Oracle, SQL Server, and Sybase ASE databases, but not for Sybase SQL Anywhere. For a full list of supported database products, see Oracle Database Firewall Installation Guide.

Note the following guidelines:

• When using a Microsoft SQL Server 2005 or later database, ensure that the database uses mixed-mode authentication.

• Local monitoring uses a source IP address of 0.0.0.0 for statements originating from a console user or other process. The destination address (displayed in the traffic log) is the same as one of the protected databases being monitored.

• Local monitoring does not record duplicate SQL statements. It only records the last SQL statement in a set of duplicate SQL statements.

Installing Local Monitoring

This section contains:

• Accessing the Scripts Required to Install Local Monitoring

• Database Accounts Created for Local Monitoring

• Installing Local Monitoring in an Oracle Database

• Installing Local Monitoring in a Microsoft SQL Server Database

• Installing Local Monitoring in a Sybase ASE Database

Accessing the Scripts Required to Install Local Monitoring

The scripts that you use to install the local monitoring components on the protected database are located in the Oracle Database Firewall Utilities 5.0 disc, in the database\localmonitor folder. Separate scripts are provided for Oracle, Sybase, and Microsoft SQL databases.

If the database is on a Windows platform, obtain the scripts from the .zip archive file. If you are using a Linux platform, unpack the .tar archive file as follows:

mkdir localmonitoring 
cd localmonitoring
tar -xvf oracle.tar

Database Accounts Created for Local Monitoring

The installation process automatically creates the following two database accounts, with the necessary privileges:

• DBFW_CONSOLE_ACCESS owns the schema objects.

• DBFW_CONSOLE_ACCESS_QRY is used by Oracle Database Firewall to query the database.

The DBFW_CONSOLE_ACCESS account has the following privileges:

• CREATE SESSION

• ADMINISTER DATABASE TRIGGER

• CREATE PROCEDURE

• CREATE SEQUENCE

• CREATE TABLE

• CREATE TRIGGER

The DBFW_CONSOLE_ACCESS_QRY account has the following privilege:

• CREATE SESSION

Installing Local Monitoring in an Oracle Database

To install the local monitoring components on an Oracle database:

1. From the Oracle Database Firewall Product CD (Oracle Database Firewall Utilities 5.0), copy the database directory to the server where you plan to run the scripts.

2. On this server, go to the database/localmonitor directory and uncompress the oracle compressed file, preferably into a directory called oracle.

3. Go to this oracle directory and review the uncompressed files so you will understand the privileges that the users in these scripts will have.

4. Log in to Oracle Database as a user who has privileges to create users and set user permissions.

   For example:

   sqlplus sys/as sysdba
   Enter password: password
   Connected. 
   SQL> 
   

   If the database has been enabled with Oracle Database Vault, then log in as a user who has been granted the DV_ACCTMGR role.

5. Run the script named dcam_new_user to create the accounts.

   For example:

   SQL> @dcam_new_user password for DBFW_CONSOLE_ACCESS password for DBFW_CONSOLE_ACCESS_QRY
   

   If you omit the passwords for the new accounts, you will be prompted for the password. For better security, allow the script to prompt you for the passwords.

6. Run the script named dcam_setup as the DBFW_CONSOLE_ACCESS account:

   connect DBFW_CONSOLE_ACCESS
   Enter passowrd: password
   SQL> @dcam_setup
   

   This script creates the tables and triggers that the monitoring system uses, and then grants access to the table to DBFW_CONSOLE_ACCESS_QRY.

7. Disable the DBFW_CONSOLE_ACCESS account.

   The DBFW_CONSOLE_ACCESS account is only needed during this setup process. You do not need this account afterwards.

8. Provide the password of the DBFW_CONSOLE_ACCESS_QRY account to the person who is to enable local monitoring from the Administration Console.

Installing Local Monitoring in a Microsoft SQL Server Database

To install the local monitoring components on a Microsoft SQL Server database running mixed-mode authentication:

1. From the Oracle Database Firewall Product CD (Oracle Database Firewall Utilities 5.0), copy the database directory to the server where you plan to run the scripts.

2. On this server, go to the database/localmonitor directory and uncompress the sqlserver compressed file, preferably into a directory called sqlserver.

3. Go to this sqlserver directory and review the uncompressed files so you will understand the privileges that the users in these scripts will have.

4. Log in to the SQL Server as a user who has privileges to create users and set user permissions.

   sqlcmd -S server_name -U sa -P password 
   

5. Run the script named dcam_new_user to create the accounts.

   1> :r dcam_new_user.sql
   

   The script creates the DBFW_CONSOLE_ACCESS and DBFW_CONSOLE_ACCESS_QRY accounts with default passwords.

6. Change the passwords for the two accounts.

   1> alter login DBFW_CONSOLE_ACCESS with password = 'new password';
   2> go
   1> alter login DBFW_CONSOLE_ACCESS_QRY with password = 'new password';
   2> go
   

7. Run the script named dcam_setup.sql.

   1> :r dcam_setup.sql
   

   This script creates the tables and event notification framework that the monitoring system uses, and then grants access to the table to DBFW_CONSOLE_ACCESS_QRY.

8. Provide the password of the DBFW_CONSOLE_ACCESS_QRY account to the person who is to enable local monitoring from the Administration Console.

Installing Local Monitoring in a Sybase ASE Database

To install the local monitoring components on a Sybase ASE database (not supported with a Sybase SQL Anywhere database):

1. From the Oracle Database Firewall Product CD (Oracle Database Firewall Utilities 5.0), copy the database directory to the server where you plan to run the scripts.

2. On this server, go to the database/localmonitor directory and uncompress the sybase compressed file, preferably into a directory called sybase.

3. Go to this sybase directory and review the uncompressed files so you will understand the privileges that the users in these scripts will have.

4. As a user who has administrative privileges and privileges to create users, run the following script on the Sybase ASE database:

   isql -S server_name -U sa -i dcam_sa_setup.sql
   

   This script creates the dbfw_console_access_qry account with a default password.

5. Change the password for the dbfw_console_access_qry account.

   isql -S server_name -U sa
   1> sp_password "sa password", new password, dbfw_console_access_qry
   2> go
   

   Enter the dbfw_console_access_qry account name in lower case only. This account name is case sensitive.

6. Run the following scripts on the Sybase ASE database:

   isql -S server_name -U dbfw_console_access_qry -i dcam_setup.sql
   isql -S server_name -U sa -i dcam_sa_setup_global_trigger.sql
   

   These scripts create the tables and global triggers that the monitoring system uses, and then grant access to the tables to the dbfw_console_access_qry account.

7. Provide the password that you created in Step 5 for the dbfw_console_access_qry account to the person who is to enable local monitoring from the Administration Console.

8. Restart Sybase ASE.

   You must restart Sybase ASE after you run the dcam_sa_setup.sql script.

Enabling Local Monitoring

To enable local monitoring for a SQL database:

1. Log in to the Management Server Administration Console.

   See "Logging in to the Administration Console" for more information.

2. Click the Monitoring tab.

3. Click the Settings button for the appropriate enforcement point.

4. Select Activate Local Monitor in the Monitoring Settings page.

   The Activate Local Monitor area expands as follows:

   
   Description of the illustration activate_loc_mon.gif
   
   

5. Specify the following settings:

   • Database Name, Database Address, and Port: Specify the database name, IP address or name of the database machine, and the port number. If you are using a Domain Name Server (DNS), you can enter a hostname instead of an IP address.

   • Password and Password Confirmation: The password of the DBFW_CONSOLE_ACCESS_QRY account specified during the installation of the software at the protected database.

6. Click Save.

You can test local monitoring by performing an appropriate query in the database server and ensuring Oracle Database Firewall logs it.

Disabling Local Monitoring

You can disable local monitoring. If you want to completely remove local monitoring, see Oracle Database Firewall Installation Guide.

To disable local monitoring:

1. Log in to the Management Server Administration Console.

   See "Logging in to the Administration Console" for more information.

2. Select the Monitoring tab.

   By default, the Enforcement Points page appears. If it does not, then click the List button in the Enforcement Points menu on the left side of the page.

3. Find the enforcement point whose local monitoring you want to disable.

4. Click the Settings button.

   The Monitoring Settings page appears.

5. In the Local Monitoring area, clear the Activate Local Monitoring check box.

6. Scroll to the bottom of the Monitoring Settings page and click the Save button.