The new version of DB2 9.5 has enhanced DB2 audit facility significantly and now it makes a perfect sense for the DBAs to start using it. The traditional notion on any audit (DB2 or Oracle) is that it is very expensive. That is not the case now as you have the options to configure audit the way you want.
DB2 audit has two work play.
- System Administrator do audits at the instance level
- Security Administrator do audits at the database level
The SYSADMs used to be very powerful in DB2 but not any more. Their powers (SYSADM and DBADM) have been curtailed as it is becoming more and more important to be able to audit what SYSADM or DBADM do in DB2.
I will explain DB2 audit by giving an example that you can do in your DB2 on your laptop. Once you understand it well, you can implement it on your DB2.
Steps for DB2 Audit
- Configure db2 audit active and archive path
- Create Audit tables
- Create a SECADM user
- SYSADM grants SECADM to the new user.
- SECADM creates policies to audit SYSADM and DBAs
Copy this sample script on your Windows laptop to learn DB2 audit
AuditSetup.CMD file – Download scripts here
TITLE %~n0.CMD - Learn Audit
SET OUTFILE=%~n0_OUTPUT.TXT
@ECHO off
cls
ECHO Executed by: %USERNAME% Machine: %COMPUTERNAME% On %OS% %DATE%
ECHO Executed by: %USERNAME% Machine: %COMPUTERNAME% On %OS% %DATE% > %OUTFILE%
ECHO. >> %OUTFILE%
set db2instance=db2
REM -------------------------------------------------------------------
REM Security Lab 04 - Audit Facility Setup
REM -------------------------------------------------------------------
REM Create C:\db2audit directory
IF EXIST C:\db2audit\nul (
rmdir "C:\db2audit" /s/q > NUL
)
IF NOT EXIST C:\db2audit\nul (
mkdir C:\db2audit > NUL 2>&1
)
REM Create C:\db2auditArchive directory
IF EXIST C:\db2auditArchive\nul (
rmdir "C:\db2auditArchive" /s/q > NUL
)
IF NOT EXIST C:\db2auditArchive\nul (
mkdir C:\db2auditArchive > NUL 2>&1
)
REM Create C:\db2auditExtract directory
IF EXIST C:\db2auditextract\nul (
rmdir "C:\db2auditExtract" /s/q > NUL
)
IF NOT EXIST C:\db2auditExtract\nul (
mkdir C:\db2auditExtract > NUL 2>&1
)
ECHO. >> %OUTFILE%
ECHO --------------------------------------------------------------------- >> %OUTFILE%
ECHO Change Audit and Archive Directory >> %OUTFILE%
ECHO --------------------------------------------------------------------- >> %OUTFILE%
ECHO. >> %OUTFILE%
db2audit configure datapath "C:\db2audit" archivepath "C:\db2auditArchive" >> %OUTFILE%
ECHO. >> %OUTFILE%
ECHO --------------------------------------------------------------------- >> %OUTFILE%
ECHO Creating Audit Tables to hold the DB2 audit data >> %OUTFILE%
ECHO --------------------------------------------------------------------- >> %OUTFILE%
ECHO. >> %OUTFILE%
db2 CONNECT TO SAMPLE > NUL
db2 CREATE SCHEMA AUDIT > NUL
db2 SET CURRENT SCHEMA = 'AUDIT' > NUL
db2 drop table audit.audit > NUL
db2 drop table audit.checking > NUL
db2 drop table audit.objmaint > NUL
db2 drop table audit.secmaint > NUL
db2 drop table audit.sysadmin > NUL
db2 drop table audit.validate > NUL
db2 drop table audit.context > NUL
db2 drop table audit.execute > NUL
db2 drop table audit.audit > NUL
db2 -tvf "%DB2PATH%\MISC\db2audit.ddl" >> %OUTFILE%
db2 CONNECT RESET > NUL
ECHO. >> %OUTFILE%
ECHO --------------------------------------------------------------------- >> %OUTFILE%
ECHO Create a Security Administrator User SECPOT (SECADM authority) >> %OUTFILE%
ECHO --------------------------------------------------------------------- >> %OUTFILE%
ECHO. >> %OUTFILE%
CALL :CreateUser SECPOT
ECHO. >> %OUTFILE%
ECHO --------------------------------------------------------------------- >> %OUTFILE%
ECHO As SYSADM, Grant SECADM to user SECPOT who is Security Administrator >> %OUTFILE%
ECHO --------------------------------------------------------------------- >> %OUTFILE%
ECHO. >> %OUTFILE%
db2 CONNECT TO SAMPLE >> %OUTFILE%
db2 -v grant secadm on database to user SECPOT >> %OUTFILE%
db2 CONNECT RESET >> %OUTFILE%
ECHO. >> %OUTFILE%
ECHO --------------------------------------------------------------------- >> %OUTFILE%
ECHO As SECADM, Create Audit policies on SAMPLE Database >> %OUTFILE%
ECHO --------------------------------------------------------------------- >> %OUTFILE%
ECHO. >> %OUTFILE%
db2 CONNECT TO SAMPLE USER SECPOT USING SECPOT12345 >> %OUTFILE%
db2 drop audit policy CHECK_POLICY > NUL
db2 drop audit policy SENSITIVE_DATA_POLICY > NUL
db2 -v CREATE AUDIT POLICY CHECK_POLICY CATEGORIES ^
VALIDATE STATUS BOTH, ^
CHECKING STATUS FAILURE, ^
OBJMAINT STATUS BOTH, ^
SECMAINT STATUS BOTH, ^
CONTEXT STATUS NONE, ^
AUDIT STATUS BOTH, ^
SYSADMIN STATUS BOTH ^
ERROR TYPE AUDIT >> %OUTFILE%
db2 -v CREATE AUDIT POLICY SENSITIVE_DATA_POLICY ^
CATEGORIES EXECUTE WITH DATA STATUS BOTH ERROR TYPE AUDIT >> %OUTFILE%
db2 commit > NUL
db2 CONNECT RESET > NUL
GOTO :EOF
:CreateUser
setlocal
REM Create user, if it does not exists
net user %1 2>NUL > NUL
IF %ERRORLEVEL% EQU 2 (
ECHO CREATING USER %1
NET USER /ADD %1 %112345 2>NUL > NUL
) ELSE (
ECHO User %1 already exists
)
ECHO.
endlocal
After you have run AuditSetup.CMD to setup audits, I will show how SYSADM does auditing at the instance level and how SECADM does auditing at the database level.
Instance Level Auditing by SYSADM
InstanceAudit.CMD file – Download scripts here
TITLE %~n0.CMD - Instance Auditing using db2audit tool
SET OUTFILE=%~n0_OUTPUT.TXT
@ECHO off
cls
ECHO Executed by: %USERNAME% Machine: %COMPUTERNAME% On %OS% %DATE%
ECHO Executed by: %USERNAME% Machine: %COMPUTERNAME% On %OS% %DATE% > %OUTFILE%
ECHO. >> %OUTFILE%
set db2instance=db2
del "C:\db2audit\*.*" /s/q/f >: NUL 2>&1
del "C:\db2auditArchive\*.*" /s/q/f >: NUL 2>&1
del "C:\db2auditExtract\*.del" /s/q/f >: NUL 2>&1
db2audit configure scope validate status both errortype audit > NUL
db2audit configure scope checking status failure errortype audit > NUL
db2audit configure scope objmaint status both errortype audit > NUL
db2audit configure scope secmaint status both errortype audit > NUL
db2audit configure scope context status none errortype audit > NUL
db2audit configure scope audit status both errortype audit > NUL
db2audit configure scope sysadmin status both errortype audit > NUL
db2audit start > NUL
ECHO. >> %OUTFILE%
ECHO --------------------------------------------------------------------- >> %OUTFILE%
ECHO Do some SQL activity >> %OUTFILE%
ECHO --------------------------------------------------------------------- >> %OUTFILE%
ECHO. >> %OUTFILE%
db2 connect to sample > NUL
db2 "values current timestamp" > NUL
db2 "create table test (c1 int not null primary key, c2 int)" > NUL
db2 "insert into test values (1,2),(2,3),(3,4),(4,5),(5,6),(6,7)" > NUL
db2 "update test set c2 = 100 where c1 in (1,2,3)" > NUL
db2 "delete from test where c2 = 100" > NUL
db2 "drop table test" > NUL
db2 "select firstnme, lastname, salary from employee" > NUL
db2 "update employee set salary = 200000 where empno = '000010'" > NUL
db2 "grant dbadm on database to user secpot" > NUL
db2 "revoke dbadm on database from user secpot" > NUL
db2 "connect to sample user secpot using 12345" > NUL
db2 "connect to sample user secpot using 34567" > NUL
db2 "connect to sample user secpot using 37645" > NUL
db2 terminate > NUL
db2audit flush > NUL
db2audit stop > NUL
ECHO. >> %OUTFILE%
ECHO --------------------------------------------------------------------- >> %OUTFILE%
ECHO Archive Audit data and extract >> %OUTFILE%
ECHO --------------------------------------------------------------------- >> %OUTFILE%
ECHO. >> %OUTFILE%
for /f "Tokens=2-4 Delims=/ " %%a in ('date /t /n') do set curdate=%%c%%a%%b
db2audit archive >> %OUTFILE%
db2audit archive database sample >> %OUTFILE%
db2audit extract delasc to C:\db2auditextract from ^
files c:\db2auditarchive\*%curdate%* >> %OUTFILE%
ECHO. >> %OUTFILE%
ECHO --------------------------------------------------------------------- >> %OUTFILE%
ECHO LOAD Extracted Audit data in AUDIT Tables >> %OUTFILE%
ECHO --------------------------------------------------------------------- >> %OUTFILE%
ECHO. >:>: %OUTFILE%
db2 -tf LoadAuditTables.SQL > NUL
ECHO. >> %OUTFILE%
ECHO --------------------------------------------------------------------- >> %OUTFILE%
ECHO Reports from Audit Tables >> %OUTFILE%
ECHO --------------------------------------------------------------------- >> %OUTFILE%
ECHO. >> %OUTFILE%
db2 -tf AuditReports.SQL >> %OUTFILE%
LoadAuditTable.SQL file – Download scripts here
--#SET TERMINATOR @
CONNECT TO SAMPLE @
ECHO -------------------------------------------------@
ECHO LOAD DATA IN AUDIT TABLES in SAMPLE Database @
ECHO -------------------------------------------------@
LOAD FROM 'C:\db2auditextract\audit.del' OF DEL MODIFIED BY
DELPRIORITYCHAR LOBSINFILE
REPLACE INTO audit.AUDIT
NONRECOVERABLE
@
LOAD FROM 'C:\db2auditextract\checking.del' OF DEL MODIFIED BY
DELPRIORITYCHAR LOBSINFILE
REPLACE INTO audit.CHECKING
NONRECOVERABLE
@
LOAD FROM 'C:\db2auditextract\objmaint.del' OF DEL MODIFIED BY
DELPRIORITYCHAR LOBSINFILE
REPLACE INTO audit.OBJMAINT
NONRECOVERABLE
@
LOAD FROM 'C:\db2auditextract\secmaint.del' OF DEL MODIFIED BY
DELPRIORITYCHAR LOBSINFILE
REPLACE INTO audit.SECMAINT
NONRECOVERABLE
@
LOAD FROM 'C:\db2auditextract\sysadmin.del' OF DEL MODIFIED BY
DELPRIORITYCHAR LOBSINFILE
REPLACE INTO audit.SYSADMIN
NONRECOVERABLE
@
LOAD FROM 'C:\db2auditextract\validate.del' OF DEL MODIFIED BY
DELPRIORITYCHAR LOBSINFILE
REPLACE INTO audit.VALIDATE
NONRECOVERABLE
@
LOAD FROM 'C:\db2auditextract\context.del' OF DEL MODIFIED BY
DELPRIORITYCHAR LOBSINFILE
REPLACE INTO audit.CONTEXT
NONRECOVERABLE
@
LOAD FROM 'C:\db2auditextract\execute.del' OF DEL MODIFIED BY
DELPRIORITYCHAR LOBSINFILE
REPLACE INTO audit."EXECUTE"
NONRECOVERABLE
@
CONNECT RESET@
AuditReports.SQL file – Download scripts here
--#SET TERMINATOR @
CONNECT TO SAMPLE @
ECHO -------------------------------------------------@
ECHO List of Successful Connections @
ECHO -------------------------------------------------@
SELECT TIMESTAMP,
SUBSTR(USERID,1,18) USERID,
SUBSTR(EXECID,1,18) EXECID,
SUBSTR(PLUGINNAME,1,18) PLUGINNAME
FROM AUDIT.VALIDATE
WHERE EVENT = 'AUTHENTICATION'
AND AUTHID IS NOT NULL
ORDER BY USERID
@
ECHO -------------------------------------------------@
ECHO List of Failed Connections @
ECHO -------------------------------------------------@
SELECT TIMESTAMP,
SUBSTR(USERID,1,18) USERID,
SUBSTR(EXECID,1,18) EXECID,
SUBSTR(PLUGINNAME,1,18) PLUGINNAME
FROM AUDIT.VALIDATE
WHERE EVENT = 'AUTHENTICATION'
AND AUTHID IS NULL
ORDER BY USERID
@
ECHO -------------------------------------------------@
ECHO List of Objects Created in SAMPLE Database @
ECHO -------------------------------------------------@
SELECT TIMESTAMP,
SUBSTR(USERID,1,18) USERID,
SUBSTR(AUTHID,1,18) AUTHID,
SUBSTR(OBJNAME,1,18) OBJNAME,
SUBSTR(OBJTYPE,1,18) OBJTYPE
FROM AUDIT.OBJMAINT
WHERE EVENT = 'CREATE_OBJECT'
ORDER BY USERID
@
ECHO -------------------------------------------------@
ECHO List of Objects Dropped in SAMPLE Database @
ECHO -------------------------------------------------@
SELECT TIMESTAMP,
SUBSTR(USERID,1,18) USERID,
SUBSTR(AUTHID,1,18) AUTHID,
SUBSTR(OBJNAME,1,18) OBJNAME,
SUBSTR(OBJTYPE,1,18) OBJTYPE
FROM AUDIT.OBJMAINT
WHERE EVENT = 'DROP_OBJECT'
ORDER BY USERID
@
ECHO -------------------------------------------------@
ECHO Who is accessing/updating EMPLOYEE TABLE @
ECHO -------------------------------------------------@
SELECT TIMESTAMP,
SUBSTR(USERID,1,18) USERID,
SUBSTR(SESSNAUTHID,1,18) SESSIONID,
SUBSTR(STMTTEXT,1,255) SQL
FROM AUDIT."EXECUTE"
WHERE EVENT = 'STATEMENT'
AND DATABASE = 'SAMPLE'
AND STMTTEXT IS NOT NULL
ORDER BY USERID
@
CONNECT RESET@
Database Level Auditing by SECADM
InstanceAudit.CMD file – Download scripts here
TITLE %~n0.CMD - Database Auditing using SECADM Policies
SET OUTFILE=%~n0_OUTPUT.TXT
@ECHO off
cls
ECHO Executed by: %USERNAME% Machine: %COMPUTERNAME% On %OS% %DATE%
ECHO Executed by: %USERNAME% Machine: %COMPUTERNAME% On %OS% %DATE% > %OUTFILE%
ECHO. >> %OUTFILE%
set db2instance=db2
del "C:\db2audit\*.*" /s/q/f > NUL 2>&1
del "C:\db2auditArchive\*.*" /s/q/f > NUL 2>&1
del "C:\db2auditExtract\*.del" /s/q/f > NUL 2>&1
ECHO. >> %OUTFILE%
ECHO --------------------------------------------------------------------- >> %OUTFILE%
ECHO Start Auditing using 2 policies >> %OUTFILE%
ECHO --------------------------------------------------------------------- >> %OUTFILE%
ECHO. >> %OUTFILE%
db2 -v CONNECT TO SAMPLE USER SECPOT USING SECPOT12345 >> %OUTFILE%
db2 -v AUDIT DATABASE USING POLICY CHECK_POLICY >> %OUTFILE%
db2 -v AUDIT TABLE %USERNAME%.EMPLOYEE USING POLICY SENSITIVE_DATA_POLICY >> %OUTFILE%
db2 -v AUDIT SYSADM, DBADM USING POLICY CHECK_POLICY >> %OUTFILE%
db2 COMMIT > NUL
db2 CONNECT RESET > NUL
ECHO. >> %OUTFILE%
ECHO --------------------------------------------------------------------- >> %OUTFILE%
ECHO Do some SQL activity >> %OUTFILE%
ECHO --------------------------------------------------------------------- >> %OUTFILE%
ECHO. >> %OUTFILE%
db2 connect to sample > NUL
db2 "values current timestamp" > NUL
db2 "create table test (c1 int not null primary key, c2 int)" > NUL
db2 "insert into test values (1,2),(2,3),(3,4),(4,5),(5,6),(6,7)" > NUL
db2 "update test set c2 = 100 where c1 in (1,2,3)" > NUL
db2 "delete from test where c2 = 100" > NUL
db2 "drop table test" > NUL
db2 "select firstnme, lastname, salary from employee" > NUL
db2 "update employee set salary = 200000 where empno = '000010'" > NUL
db2 "grant dbadm on database to user secpot" > NUL
db2 "revoke dbadm on database from user secpot" > NUL
db2 "connect to sample user secpot using 12345" > NUL
db2 "connect to sample user secpot using 34567" > NUL
db2 "connect to sample user secpot using 37645" > NUL
db2 terminate > NUL
ECHO. >> %OUTFILE%
ECHO --------------------------------------------------------------------- >> %OUTFILE%
ECHO Stop Auditing using 2 policies, archive and extract audit data >> %OUTFILE%
ECHO --------------------------------------------------------------------- >> %OUTFILE%
ECHO. >> %OUTFILE%
for /f "Tokens=2-4 Delims=/ " %%a in ('date /t /n') do set curdate=%%%%c%%a%%b%%
db2 -v CONNECT TO SAMPLE USER SECPOT USING SECPOT12345 > NUL
db2 -v AUDIT DATABASE REMOVE POLICY >> %OUTFILE%
db2 -v AUDIT TABLE %USERNAME%.EMPLOYEE REMOVE POLICY >> %OUTFILE%
db2 -v AUDIT SYSADM, DBADM REMOVE POLICY >> %OUTFILE%
db2 COMMIT > NUL
db2 CALL SYSPROC.AUDIT_ARCHIVE( 'C:\db2auditArchive', -2 ) > NUL
db2 CALL sysproc.audit_delim_extract(NULL,'C:\db2auditextract', ^
null,'db2audit.db.SAMPLE.log.%curdate%',null)
db2 CONNECT RESET > NUL
ECHO. >> %OUTFILE%
ECHO --------------------------------------------------------------------- >> %OUTFILE%
ECHO LOAD Extracted Audit data in AUDIT Tables >> %OUTFILE%
ECHO --------------------------------------------------------------------- >> %OUTFILE%
ECHO. >> %OUTFILE%
db2 -tf LoadAuditTables.SQL > NUL
ECHO. >> %OUTFILE%
ECHO --------------------------------------------------------------------- >> %OUTFILE%
ECHO Reports from Audit Tables >> %OUTFILE%
ECHO --------------------------------------------------------------------- >> %OUTFILE%
ECHO. >> %OUTFILE%
db2 -tf AuditReports.SQL >> %OUTFILE%
Run InstanceAudit.CMD to do the audits at the instance level. When you see the output file, you will notice that the SYSADM can only see the connections but not the database level activity as that action can only be performed by the SECADM. When you run DatabaseAudit.CMD, you will see that the output only shows the database level activity but not the instance level. Also, SECADM can also see what SYSADM is doing at the instance or at the database level.
How new DB2 audit is different than the previous versions.
- Audit functions are separated between SYSADM and SECADM
- SECADM can monitor SYSADM and DBAs
- Database auditing is separate at each database within one instance.
- You can audit a single table
- SECADM can audit who is creating or dropping tables
- SECADM can audit all SQL statements
If you are looking for 3rd party solution to analyze your DB2 audit logs, you can look for these solutions: