Question & Answer
Question
In DB2 V8 and V9.1, after I run the command "db2audit extract delasc", the file context.del is created in the security subdirectory of sqllib. And then, I am able to find the SQL statements that are issued in the context.del file. However, in DB2 V9.5, I will get the AUD0002N syntax error if I run the same command. How can I audit SQL statements in DB2 V9.5?
Answer
DB2 Audit has changed in version 9.5.
To configure auditing of SQL statements for a particular database or table, an "audit policy" must be created where the EXECUTE category (EXECUTE is new to V9.5) is specified. See documentation for "Introduction to DB2 audit facility" and "Audit Policy"
Example of auditing any access to a specific table:
Consider a company where the EMPLOYEE table contains extremely sensitive information and the company wants to audit any and all SQL access to the data in that table. The EXECUTE category can be used to track all access to a table; it audits the SQL statement, and optionally the input data value provided at execution time for that statement.
There are two steps to track activity on the table.
1) The security administrator creates an audit policy that specifies the EXECUTE category:
CREATE AUDIT POLICY SENSITIVEDATAPOLICY CATEGORIES EXECUTE STATUS BOTH ERROR TYPE AUDIT
COMMIT
2) The security administrator associates that policy with the table:
AUDIT TABLE EMPLOYEE USING POLICY SENSITIVEDATAPOLICY
COMMIT
After you run the command db2audit extract delasc from files, you need to create audit tables and load execute.del audit data into EXECUTE table. See documentation for "Creating tables to hold the DB2 audit data" and "Loading DB2 audit data into tables"
You will see all the SQL statements in the EXECUTE table.
In summary, below is an example of commands.
1) Grant the SECADM privilege to a user
db2 CONNECT TO SAMPLE user <another SYSADM userid>
db2 GRANT SECADM ON DATABASE TO USER <the userid that you use to run the following commands>
2) Connect as the SECADM user and create the audit policy
db2 CONNECT TO SAMPLE user <SECADM user>
db2 CREATE AUDIT POLICY SENSITIVEDATAPOLICY CATEGORIES EXECUTE STATUS BOTH ERROR TYPE AUDIT
db2 AUDIT TABLE EMPLOYEE USING POLICY SENSITIVEDATAPOLICY
3) Run SQL statement and extract the data
db2 connect to sample
db2 select * from employee
db2audit archive database sample
db2audit extract delasc from files db2audit.db.SAMPLE.log.0.*
4) Create the db2audit tables and load the extracted data.
db2 +o -tf sqllib/misc/db2audit.ddl
db2 LOAD FROM execute.del OF DEL MODIFIED BY DELPRIORITYCHAR LOBSINFILE INSERT INTO EXECUTE
5) Retrieve the SQL statements that were audited from the EXECUTE table.
db2 select * from EXECUTE
Related Information
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21313319