关于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原创文章,转载请注明出处

Leave a comment ?

12 Comments.

  1. Exactly where did you obtain this design?? It truly is dazzling!

  2. FYI, your webpage won’t seem to be perfect inside Firefox.

  3. Kudos from one genius to yet another.

  4. How much time have you been doing this?

  5. Nice!! Great Ifo. Great People. Great Blog. Thank you for all the great sharing that is being done here.

  6. Awesome Blog. I add this Blog to my bookmarks.

  7. I’m sure it’s in the manual somewhere…

  8. Hey dude, do have a newsletter? In the event you do not definately ought to get on that…this website has great info!

  9. I really favor in your blog!The great masterpiece with nice and informative post and topic that move me and enlarged my eyeline quite a lot.Thanks for sharing with us.Best regards!

  10. Después del coma etílico del fin de semana, vas y sales con esoatSerr.no, se podría hacer una tesis doctoral hablando de los besos.El primer beso, el beso en la frente, el beso de judas,los besos robados, besos dulces, amargos, frios, calientes, envenenados, besos de enamorados, de amantes,en fin, besos y más besos, asi que, un beso.

  11. Portia, once again you have made my morning coffee almost leave my body through my nostrils as I snorted in laughter mid-sip! Love the way you give yourself over 100% to the sniff testing. You and your friends ROCK the video sniff sessions!

Leave a Comment


NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>