13/25

# 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.