9 Configuring and Using Direct Database Interrogation

This chapter contains:

About Direct Database Interrogation (DDI)

Direct database interrogation (DDI) allows Database Firewall to interrogate certain supported databases for specific information. The information collected depends on the database type.

Using DDI to Interrogate SQL Server and SQL Anywhere Databases

You can use DDI to interrogate a monitored Microsoft SQL Server and Sybase SQL Anywhere 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.

To configure DDI for these two databases you must:

Using DDI to Monitor Oracle Databases That Use Oracle Advanced Security

If you are monitoring an Oracle Database that uses Oracle Advanced Security encryption, you must use DDI in order to decrypt statements sent to, and responses received from, that database so they can be analyzed.

Configuring DDI for an Oracle Database Using Oracle Advanced Security

To configure DDI for an Oracle Database with Oracle Advanced Security, you must:

Limitations on Decryption of Oracle Database Statements

Configuring Database Firewall to decrypt traffic with Oracle Advanced Security has the following limitations:

  • The supported Oracle Database versions are: 10.x, 11.1, 11.2

  • There is no statement substitution in Database Firewall when Oracle Advanced Security checksum is used.

  • There is no support for Oracle Advanced Security RC4 cipher.

Configuring DDI for SQL Server and Sybase SQL Anywhere Databases

This section contains:

Setting DDI User Permissions in a Microsoft 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.1), 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: Enter the system administrator user name.

    • 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 DDI 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.1), 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: Enter the system administrator user name.

    • 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 DDI in an Enforcement Point for SQL Server or SQL Anywhere Databases

Follow the procedure in "Enabling DDI" to complete the DDI setup for a Microsoft SQL Server or Sybase SQL Anywhere database.

Configuring DDI for an Oracle Database With Oracle Advanced Security

This section contains:

Applying the Specified Patch to the Oracle Database

You must apply the patch specified in this section to the Oracle Database that is using Oracle Advanced Security.

To apply the patch:

  1. Shut down the Oracle Database.

  2. Execute the command:

    $ORACLE_HOME/OPatch/opatch apply path_to_patchfile.zip

    The patch is identified by the bug number 13051081. So the patch file will be in the format: p13051081_OracleVersion_Platform.zip

  3. Start the Oracle Database.

  4. From the Oracle Database Firewall Product CD (Oracle Database Firewall Utilities 5.1), copy the database directory to a location from which you can connect to the Oracle Database being patched.

  5. In this location, go to the database/ddi directory and uncompress one of the two oracle compressed files (both contain the same content), preferably into a directory called oracle.

    This directory now contains the uncompressed file: advanced_security_integration.sql.

  6. Execute the following command as a user that has privileges to create users and grant privileges:

    sqlplus / as sysdba @advanced_security_integration schema password

    For schema, use the name of an existing schema or choose a name for a new schema. We do not recommend using SYSTEM or SYS as the target schema. If the schema does not exist, this procedure will create a user and a schema.

    This command grants the create session and resource privileges to the schema user.

    The password for the schema is set to password.

    A package supporting Oracle Advanced Security integration is installed into schema.

Providing a Public Key to the Oracle Database

You must provide a public key to the Oracle Database that is using Oracle Advanced Security in order for to decrypt database traffic using direct database interrogation.

To provide the public key to the Oracle Database:

  1. In the Administration Console of the Database Firewall that will be monitoring this Oracle Database, click the System tab, then Public Keys in the System menu.

    Description of public_key_aso.gif follows
    Description of the illustration public_key_aso.gif

  2. Copy the public key under Oracle Advanced Security Decryption and paste it into a text file, for example, dbfw_public_key.txt.

    Each Database Firewall has its own public key. In a case where you have Database Firewall high availability or Enforcement Point resiliency, when you have more than one Database Firewall monitoring this protected database, each Database Firewall public key must be copied and appended to the dbfw_public_key.txt file.

    Note: For security purposes the dbfw_public_key.txt file must have the same access permissions as the sqlnet.ora file on the Oracle Database server.

  3. Modify the sqlnet.ora file in the Oracle Database to include the public key and to require Oracle Advanced Security native traffic encryption:

    1. Put the file you created in Step 2 on the Oracle Database server, preferably in the same directory as the sqlnet.ora file.

    2. Open the sqlnet.ora file and append the following parameters (in this example the public key file is dbfw_public_key.txt):

      SQLNET.ENCRYPTION_TYPES_SERVER=AES256
SQLNET.DBFW_PUBLIC_KEY="/path_to_file/dbfw_public_key.txt"

    3. Save and close the sqlnet.ora file.

Enabling DDI in an Enforcement Point for an Oracle Database

Follow the procedure in "Enabling DDI" to complete the DDI setup for an Oracle Database that uses Oracle Advanced Security.

Enabling DDI

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 monitors the protected database that will be interrogated, and then click the Settings button for that enforcement point.

    The Monitoring 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 Monitoring Settings page, and then click the Save button.

Disabling DDI

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 monitors the database for which you want to disable DDI.

    The Monitoring 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 Monitoring Settings page, and then click the Save button.