此篇文章严重参考 http://www.dw4e.com/?p=47
老版本的SQL命令,在CONTEXT审计事件STMTTEXT字段中,而新版本的SQL statement,在EXECUTE审计事件STMTTEXT字段中。以下是在DB2 v9.5版本中使用SQL审计的步骤:
- 准备工作:修改audit_buf_sz参数并重启实例,否则审计活动将严重影响数据库性能:
db2 update dbm cfg using audit_buf_sz 32
db2stop force
db2start - 将secadm权限授权给安全管理员用户,编辑sqllib/misc目录下的脚本db2audit.ddl,创建容纳审计数据的表:
db2 “GRANT SECADM ON DATABASE TO USER BI”
db2 create schema audit
db2 connect to mybi user bi using bipwd
db2 set current schema audit
db2 -tvf /db2home/db2inst1/sqllib/misc/db2audit.ddl - 创建EXECUTE类型的审计POLICY,在db2 -t命令行执行(注意此类命令必须要COMMIT),因为我们只关心执行过的sql,存储过程等,对其他信息不关心,所以只创建execute policy:
CREATE AUDIT POLICY STATEMENTS CATEGORIES EXECUTE WITH DATA
STATUS BOTH ERROR TYPE AUDIT;
COMMIT; - 启用该数据库审计,在db2 -t命令行执行:
AUDIT DATABASE USING POLICY STATEMENTS;
COMMIT; - 建立审计文件的备份目录和装载文件目录:
cd /db2home/db2inst1/sqllib/security
mkdir auditarchive
mkdir auditdelasc - 使用以下命令,将审计文件装载到数据库,此命令组可写成脚本形式,定期执行,将审计日志文件装载进数据库,避免审计文件过大,其实下面的load语句只有最有一个会产生数据,以为我们只定义了execute policy:
\rm /db2home/db2inst1/sqllib/security/auditarchive/*
\rm /db2home/db2inst1/sqllib/security/auditdelasc/*
db2audit archive database mybi to /db2home/db2inst1/sqllib/security/auditarchive
db2audit extract delasc to /db2home/db2inst1/sqllib/security/auditdelasc from files /db2home/db2inst1/sqllib/security/auditarchive/*
db2 connect to mybi user bi using bipwd
db2 “LOAD FROM /db2home/db2inst1/sqllib/security/auditdelasc/validate.del OF DEL MODIFIED BY DELPRIORITYCHAR LOBSINFILE INSERT INTO audit.validate”
db2 “LOAD FROM /db2home/db2inst1/sqllib/security/auditdelasc/context.del OF DEL MODIFIED BY DELPRIORITYCHAR LOBSINFILE INSERT INTO audit.context”
db2 “LOAD FROM /db2home/db2inst1/sqllib/security/auditdelasc/audit.del OF DEL MODIFIED BY DELPRIORITYCHAR LOBSINFILE INSERT INTO audit.audit”
db2 “LOAD FROM /db2home/db2inst1/sqllib/security/auditdelasc/checking.del OF DEL MODIFIED BY DELPRIORITYCHAR LOBSINFILE INSERT INTO audit.checking”
db2 “LOAD FROM /db2home/db2inst1/sqllib/security/auditdelasc/sysadmin.del OF DEL MODIFIED BY DELPRIORITYCHAR LOBSINFILE INSERT INTO audit.sysadmin”
db2 “LOAD FROM /db2home/db2inst1/sqllib/security/auditdelasc/objmaint.del OF DEL MODIFIED BY DELPRIORITYCHAR LOBSINFILE INSERT INTO audit.objmaint”
db2 “LOAD FROM /db2home/db2inst1/sqllib/security/auditdelasc/secmaint.del OF DEL MODIFIED BY DELPRIORITYCHAR LOBSINFILE INSERT INTO audit.secmaint”
db2 “LOAD FROM /db2home/db2inst1/sqllib/security/auditdelasc/execute.del OF DEL MODIFIED BY DELPRIORITYCHAR LOBSINFILE INSERT INTO audit.execute”
db2 terminate如果不是系统管理员而是安全审计员BI用户,则可以使用SYSPROC.AUDIT_ARCHIVE和SYSPROC.AUDIT_DELIM_EXTRACT两个存储过程替代db2audit archive和db2audit extract命令。