Stored procedure auditing enables users to audit and approve changes to stored procedures on monitored databases. Oracle Database Firewall connects to the database server at scheduled intervals and determines which changes or additions (if any) have been made to stored procedures. Stored Procedure Auditing is supported for Oracle, Microsoft SQL Server, Sybase ASE, Sybase SQL Anywhere, and IBM DB2 (Microsoft Windows, UNIX, and Linux) databases.
This chapter explains how to configure a Database Firewall-protected database so that users can audit changes to stored procedures. Instructions for performing the audit are in Oracle Database Firewall Security Management Guide.
For information about ArcSight syslog messages that capture stored procedure audits, see "DBFW:8 (Database Audit)".
Setting Stored Procedure Auditing User Permissions on Oracle Databases
Setting Stored Procedure Auditing User Permissions for SQL Server Databases
Setting Stored Procedure Auditing User Permissions for Sybase ASE Databases
Setting Stored Procedure Auditing User Permissions for Sybase SQL Anywhere Databases
Setting Stored Procedure Auditing User Permissions for IBM DB2 SQL Databases
To set up the user account for Oracle databases (all releases later than Oracle Database 8i):
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.
On this server, go to the database/spa
directory and uncompress the oracle
compressed file, preferably into a directory called oracle
.
Go to this oracle
directory and review the uncompressed file (spa_setup.sql
) so that you will understand its settings.
The spa_setup.sql
script contains settings for the following information:
$(username)
refers to the user account that will be responsible for stored procedure auditing. Ideally, this user account should be different from the user accounts specified for user role auditing and direct database interrogation (DDI) (for example, spa_auditor
).
$(password)
refers to the password for this user account.
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.
Run the spa_setup
script and answer the prompts.
For example:
SQL> @database/spa/oracle/spa_setup.sql username: as parameter 1: Enter value for 1: user_name password: as parameter 2: Enter value for 2: password
The spa_setup.sql
script grants the stored procedure auditing user account the following privileges:
CREATE SESSION
SELECT
on the sys.dba_objects
and sys.dba_source
system tables
To set up the user account for Microsoft SQL Server (2000, 2005, or 2008) databases:
Decide where you are going to run the scripts: on the database server (locally) or from another computer (remotely).
Ensure that the computer where you will run the scripts has the sqlcmd.exe
utility installed.
Choose a user name and password for the database user account that will be responsible for the stored procedure auditing.
You will create this user account and password later in this procedure.
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.
On this server, go to the database/spa
directory and uncompress the sqlserver
compressed file, preferably into a directory called sqlserver
.
Go to this sqlserver
directory and review the uncompressed files so that you will understand their settings.
The scripts contain settings for the following information:
$(username)
refers to the user account that will be responsible for stored procedure auditing. Ideally, this user account should be different from the user accounts specified for user role auditing and direct database interrogation (DDI) (for example, spa_auditor
).
$(password)
refers to the password for this user account.
$(database)
refers to the database that you want to audit.
As a user who has privileges to create users and set user permissions, run the spa_add_user.sql
script on the SQL Server database.
The syntax is as follows:
sqlcmd -S server_name -U sa -P sa_password -i spa_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 stored procedure auditing, specified by $(username)
in the scripts. Enclose this user name in double quotation marks.
password
: Enter the password for the stored procedure auditing user account, specified by $(password)
in the scripts. Enclose this password in double quotation marks.
Following are two command examples. (The lines wrap below, but you may see them on one line.)
sqlcmd -U sa -P sa_password -i spa_add_user.sql -v username="spa_auditor" password="abcd1234" sqlcmd -S my_server -U sa -P sa_password -i spa_add_user.sql -v username="spa_auditor" password="abcd1234"
Grant the user permissions by running the spa_db_permissions.sql
or spa_add_all_db_permissions.sql
script.
The following examples show how to run the scripts remotely, but if you are running the scripts locally, then omit the -S server_name
argument.
For permissions to a specific database, use the following syntax:
sqlcmd -S server_name -U sa -P sa_password -i spa_add_db_permissions.sql -v username="username" database="protected_database"
For the database="
protected_database
"
setting:
Enter the name of the database within this server that you want to audit, specified by $(database)
in the scripts.
Enclose this database name in double quotation marks.
For permissions to all databases, use this syntax:
sqlcmd -S server_name -U sa -P sa_password -i spa_add_all_db_permissions.sql -v username="username"
Below are two command examples. (The lines wrap below, but you may see each command on one line.)
sqlcmd -S my_server -U sa -P sa_password -i spa_add_db_permissions.sql -v username="jsmith" database="my_database" sqlcmd -S my_server -U sa -P sa_password -i spa_add_all_db_permissions.sql -v username="jsmith"
The scripts grant the stored procedure auditing user account the following privileges:
VIEW DEFINITION
and SELECT
on the sys.all_objects
and dbo.syscomments
system tables for product version greater than 8.
SELECT
on dbo.sysobjects
and dbo.syscomments
for product version less than Version 8.
To set up the Sybase Adaptive Server Enterprise user accounts:
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.
On this server, go to the database/spa
directory and uncompress the sybase
compressed file, preferably into a directory called sybase
.
As a user who has privileges to create users and set user permissions, run the spa_add_user.sql
script on the Sybase ASE database.
The syntax is as follows:
isql -S server_name -U sa -P sa_password -i spa_add_user.sql
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.
Examples:
isql -U sa -P sa_password -i spa_add_user.sql isql -S my_server -U sa -P sa_password -i spa_add_user.sql
This script adds a default stored procedure auditing user, dbfw_spa_user
, with a default password, defaultpassword
. It is good practice to change this password later.
Grant this user permissions by running the spa_add_db_permissions.sql
script.
The syntax is as follows:
isql -S server_name -U sa -P sa_password -i spa_add_db_permissions.sql
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.
This script grants the stored procedure auditing user account the following privileges:
SELECT
on dbo.sysdatabases
, dbo.sysobjects
, and dbo.syscomments
.
This section contains:
Before you can use Sybase SQL Anywhere, you must install the SQL Anywhere ODBC driver for Linux.
To install the ODBC driver for Linux:
As user root
, log in to the Database Firewall server.
From the Sybase SQL Anywhere installation media, copy the linux_x86_GA_sa1101_2044_l10n.tar.gz
to a temporary location on the Database Firewall server.
Expand this archive file.
tar zxvf linux_x86_GA_sa1101_2044_l10n.tar.gz
Run the setup
utility to begin the installation of the client.
./setup
When prompted, select to install only 3. Administration Tools
.
When prompted, install the client to the /var/sqlanywhere11
directory.
From the Sybase SQL Anywhere installation media, copy sa11_full_linux_x86+x64.1101_2420_ebf.tar.gz to the Database Firewall server.
Expand this archive file.
tar zxvf sa11_full_linux_x86+x64.1101_2420_ebf.tar.gz
Run the setup
utility to begin the installation of the client.
./setup
When prompted, select to install to the /var/sqlanywhere11
directory.
To set up the Sybase SQL Anywhere user accounts:
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.
On this server, go to the database/spa
directory and uncompress the sqlanywhere
compressed file, preferably into a directory called sqlanywhere
.
Go to this sqlanywhere
directory and review the uncompressed file (spa_setup.sql
) so that you will understand its settings.
The spa_setup.sql
script contains settings for the following information:
$(username)
refers to the user account that will be responsible for stored procedure auditing. Ideally, this user account should be different from the user accounts specified for user role auditing and direct database interrogation (DDI) (for example, spa_auditor
).
$(password)
refers to the password for this user account.
As a user who has privileges to create users and set user permissions, run the spa_setup.sql
script on the SQL Anywhere database.
The syntax is as follows:
isql -S server_name -U sa -P sa_password -i spa_setup.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.
username
: Enter the user account that you plan to create for stored procedure auditing, specified by $(username)
in the spa_setup.sql
script. Enclose this user name in double quotation marks.
password
: Enter the password for the stored procedure auditing user account, specified by $(password)
in the spa_setup.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 spa_setup.sql
script. Enclose this database name in double quotation marks.
For example:
isql -S my_server -U sa -P password -i spa_setup.sql -v username="spa_auditor" password="$(password)_password" database="sales_db"
The spa_setup.sql
script grants the stored procedure auditing user account the following privileges:
CONNECT
SELECT
on the sys.sysuser, sys.sysprocedure,
and sys.sysprocparm
system tables.
To set up an IBM DB2 user account, you do not need to run any scripts. Instead, you create a new user account or designate an existing user account to be used for the stored procedure auditing.
To set up the IBM DB2 user account:
Log in to the IBM DB2 Windows, UNIX, or Linux database that you want to audit.
Create a new user account or designate an existing user account to be used for the stored procedure auditing.
Grant the following privilege to this user:
grant select on syscat.routines to user
To enable stored procedure auditing:
Log in to the standalone Database Firewall or Management Server Administration Console.
See "Logging in to the Administration Console" for more information.
Click List in the Enforcement Points menu of the Monitoring page.
Choose an enforcement point to monitor the stored procedures, and click the Settings button.
All enforcement points also monitor SQL traffic to a nominated database server. If required, the enforcement point can monitor stored procedures in databases located on a different server.
Under Monitoring Settings, select the SPA check box.
Complete the SPA fields and options:
Database Address and Port: Specify the IP address of the server that holds the databases to be audited. For the port number, enter the port number used by the database. (For example, the default port number for Oracle databases is 1521. For Oracle databases, you can find this information in the tnsnames.ora
file.) If you are using a Domain Name Server (DNS), you can enter a hostname instead of an IP address.
The IP address of the protected database specified in the enforcement point is not automatically included in the audit.
Database Name: Name of the database. For Oracle databases, enter the service name, as defined in the tnsnames.ora
file.
User Name: Enter the user name of the user who was created by the spa_add_user.sql
script, described in "Setting User Permissions for Stored Procedure Auditing". (This user name is the user account specified by the $(username)
setting in the scripts.)
Change Password: If you want to change the password of the user, click the Change Password button and then enter a new password. (This field appears only if you are editing an existing configuration. The first time that you configure stored procedure auditing, the Password and Confirm Password fields appear.)
Database Connection (Test Now): Clicking Test Now checks that the specified user can log into the databases and has the required permissions.
First Run Time and Repeat Every: Specify the date and time to run the first audit and the frequency to repeat the audits. Select a time when the database is not busy, such as 2 a.m.
If you want to run an audit immediately, click List in the Enforcement Points menu, then the Manage button for the appropriate enforcement point, followed by Run Now.
You can disable stored procedure auditing. If you want to completely remove stored procedure auditing, see Oracle Database Firewall Installation Guide.
To disable stored procedure auditing:
Log in to the standalone Database Firewall or Management Server Administration Console.
See "Logging in to the Administration Console" for more information.
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.
Find the enforcement point for the stored procedure auditing that you want to disable.
Click the Settings button.
The Monitoring Settings page appears.
In the SPA area, clear the Activate Stored Procedure Auditing check box.
Scroll to the bottom of the Monitoring Settings page and click the Save button.