现在的位置: 首页 > 综合 > 正文

关于db2v95的审计

2017年11月15日 ⁄ 综合 ⁄ 共 2428字 ⁄ 字号 评论关闭

此篇文章严重参考   http://www.dw4e.com/?p=47大笑

老版本的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),因为我们只关心执行过的sql,存储过程等,对其他信息不关心,所以只创建execute policy:

    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. 使用以下命令,将审计文件装载到数据库,此命令组可写成脚本形式,定期执行,将审计日志文件装载进数据库,避免审计文件过大,其实下面的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_ARCHIVESYSPROC.AUDIT_DELIM_EXTRACT两个存储过程替代db2audit
    archive和db2audit extract命令。 

抱歉!评论已关闭.