Monthly Archives: March 2009

关于DB2v9.5的审计

如何进行DB2的审计,网上有很多文章介绍,基本上都是关于DB2版本8或者更老的。而新版的DB2审计功能,关于用户执行的SQL命令的记录,与老版本的有较大区别。

老版本的SQL命令,在CONTEXT审计事件STMTTEXT字段中,而新版本的SQL statement,在EXECUTE审计事件STMTTEXT字段中。以下是在DB2 v9.5版本中使用SQL审计的步骤:

  1. 准备工作:修改audit_buf_sz参数并重启实例,否则审计活动将严重影响数据库性能:

    db2 update dbm cfg using audit_buf_sz 32
    db2stop force
    db2start

  2. 将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

  3. 创建EXECUTE类型的审计POLICY,在db2 -t命令行执行(注意此类命令必须要COMMIT):

    CREATE AUDIT POLICY STATEMENTS CATEGORIES EXECUTE WITH DATA
       STATUS BOTH ERROR TYPE AUDIT;
    COMMIT;

  4. 启用该数据库审计,在db2 -t命令行执行:

    AUDIT DATABASE USING POLICY STATEMENTS;
    COMMIT;

  5. 建立审计文件的备份目录和装载文件目录:

    cd /db2home/db2inst1/sqllib/security
    mkdir auditarchive
    mkdir auditdelasc

  6. 使用以下命令,将审计文件装载到数据库:

    \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_ARCHIVESYSPROC.AUDIT_DELIM_EXTRACT两个存储过程替代db2audit archive和db2audit extract命令。


Data Warehouse For Ever原创文章,转载请注明出处