9 Configuring and Using Direct Database Interrogation

This chapter contains:

About Direct Database Interrogation

Direct database interrogation enables you to interrogate a monitored database to obtain the name of the database user, operating system, and client program that originated a SQL statement, if this information is not available from the statement itself. This information then is made available in the Database Firewall reports. You can use direct database interrogation on Microsoft SQL Server and Sybase SQL Anywhere databases only.

To configure direct database interrogation, you must run a provided script to grant privileges to an existing user account in the protected database, and then enable the direct database interrogation functionality from the Database Firewall that manages the protected database.

Setting User Permissions for Direct Database Interrogation

This section contains:

Setting Direct Database Interrogation User Permissions in a SQL Server Database

To set up the user account for a Microsoft SQL Server (versions 2005 or 2008) 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 script.

  2. Ensure that the computer where you will run the scripts has the sqlcmd.exe utility installed.

  3. On this server, go to the database/ddi directory and uncompress the sqlserver compressed file, preferably into a directory called sqlserver. This directory will contain the uncompressed file ddi_add_user.sql.

    The ddi_add_user.sql script contains settings for the following information:

    • $(username) refers to the user account that will be responsible for direct database interrogation. Ideally, this user account should be different from the user accounts specified for stored procedure and user role auditing (for example, ddi_auditor).

    • $(password) refers to the password for this user account.

  4. As a user who has privileges to create users and set user permissions, run the ddi_add_user.sql script on the SQL Server database.

    The syntax is as follows:

    sqlcmd -S server_name -U sa -P sa_password -i ddi_add_user.sql 
-v username="username" password="password"

    In this specification:

    • server_name: Enter the name or the IP address of the database server where the protected database resides. Only use this argument if you are running the script from a remote server. You can omit it if you are running the script locally.

    • sa_password: Enter the system administrator password.

    • username: Enter the user account that you plan to create for direct database interrogation, specified by $(username) in the ddi_add_user.sql script. Enclose this user name in double quotation marks.

    • password: Enter the password for the direct database interrogation user account, specified by $(password) in the ddi_add_user.sql script. Enclose this password in double quotation marks.

    Examples:

    sqlcmd -U sa -P sa_password -i ddi_add_user.sql  -v username="ddi_auditor" password="abcd1234"

sqlcmd -S my_server -U sa -P sa_password -i ddi_add_user.sql 
-v username="ddi_auditor" password="abcd1234"

The ddi_add_user.sql script grants the direct database interrogation user account the following privileges:

  • VIEW ANY DEFINITION and VIEW SERVER STATE for SQL Server 2005 and later

  • SELECT on the master.dbo.sysdatabases table:

Setting Direct Database Interrogation User Permissions in a Sybase SQL Anywhere Database

To set user permissions for direct database interrogation in 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 script.

  2. On this server, go to the database/ddi directory and uncompress the sqlanywhere compressed file, preferably into a directory called sqlanywhere. This directory contains the uncompressed file ddi_add_user.sql.

    This script contain settings for the following information:

    • $(username) refers to the user account that will be responsible for direct database interrogation. Ideally, this user account should be different from the user accounts specified for stored procedure and user role auditing (for example, ddi_auditor).

    • $(password) refers to the password for this user account.

  3. As a user who has privileges to create users and set user permissions, run the ddi_add_user.sql script on the SQL Anywhere database.

    The syntax is as follows:

    isql -S server_name -U sa -P sa_password -i ddi_add_user.sql
-v username="username" password="password" database="protected_database"

    In this specification:

    • server_name: Only use this argument if the database is remote. You can enter the name of the server or its IP address. If you are running the script locally, then you can omit the -S server_name argument.

    • sa_password: Enter the system administrator password.

    • username: Enter the user account that you plan to create for direct database interrogation, specified by $(username) in the ddi_add_user.sql script. Enclose this user name in double quotation marks.

    • password: Enter the password for the direct database interrogation user account, specified by $(password) in the ddi_add_user.sql script. Enclose this password in double quotation marks.

    • database="protected_database": Enter the name of the database within this server that you want to protect, specified by $(database) in the ddi_add_user.sql script. Enclose this database name in double quotation marks.

    Examples:

    isql -U sa -P sa_password -i sddi_add_user.sql
-v username="ddi_auditor" password="abcd1234" database="sales_db"

isql -S my_server -U sa -P sa_password -i sddi_add_user.sql
-v username="ddi_auditor" password="abcd1234" database="sales_db"

The ddi_add_user.sql script grants the direct database interrogation user account the following privileges:

  • CONNECT

  • SELECT on these system tables:

    sys.sysuser
sys.sysuserauthority
sys.sysremoteuser
sys.sysloginmap
sys.sysgroup

Enabling Direct Database Interrogation

To enable direct database interrogation in a Database Firewall:

  1. Log in to the Management Server Administration Console.

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

  2. Click the Monitoring tab.

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

  3. Find the enforcement point that you want to use for the remote monitor, and then click the Settings button for that enforcement point.

    The Monitor Settings page appears.

  4. Scroll down to the Database Interrogation area and click the Activate Database Interrogation check box.

    The Activate Database Interrogation area expands to enable you to complete the necessary authentication details.

    Description of image101.jpg follows
    Description of the illustration image101.jpg

  5. Scroll to the end of the Monitor Settings page, and then click the Save button.

Disabling Direct Database Interrogation

You can temporarily disable direct database interrogation. Oracle Database Firewall saves the configuration information that you have created for the next time that you want to enable it. If you want to remove the direct database interrogation configuration and software, see Oracle Database Firewall Installation Guide.

To disable direct database interrogation:

  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.

  3. Click the Settings button for the enforcement point that you want to use for the remote monitor.

    The Monitor Settings page appears.

  4. Scroll down to the Direct Database Interrogation area.

  5. Clear the Activate Database Interrogation check box.

  6. Scroll to the end of the Monitor Settings page, and then click the Save button.