杨文云
作者杨文云2019-09-03 15:30
数据库管理员, GBS

db2audit policy跟踪对某表所执行的DML操作

字数 5163阅读 1296评论 0赞 4

使用 db2audit 跟踪对某表所执行的动态 JAVA DML 操作

文档实验环境

系统 DB2 version INSTACE NAME DATABASE NAME 作者
Redhat 7.4 V11.1.1.4 DB2INST1 CSDW IBM 杨文云

本文使用db2audit policy 代替旧的db2audit configure可以在表级别粒度审计数据已避免占用太多系统资源造成系统整体性能下降。下文将介绍怎样利用AUDIT POLICY开启审计。

本文的初衷是帮助某应用抓取一个循环动态 JAVA SQL 的变量值帮助应用快速定位具体变量造成死循环问题在此之前系统维护执行 db2pd,db2top,db2 get snapshot 和 event monitor 只能取出没有具体变量值的 SQL 如 insert into db2inst1.or_test values(?,?) , 笔者接到运维团队的需求,希望能找出 SQL 执行变量值,执行测试步骤如下。

1.首先查看拥有 security admin 权限的 ID ,本文作者用实列 id 创建,这步只是提醒实际维护过程中是否没有实列 ID 的维护人员需要获得 SECURITYADMAUTH 权限,如没有先让执行审计的 ID 取得权限 , 需要 DBA 在 DATABASE 级别授权 GRANT SECADM ON DATABASE TO user userid

db2 "select GRANTEE,SECURITYADMAUTH from syscat.dbauth"  
GRANTEE SECURITYADMAUTH  
---------------------------------------------------------------------  
SECTEST Y  
PUBLIC N

2.创建测试表

db2 "create table db2inst1.or_test (A char(5),B char(9))"  
DB20000I The SQL command completed successfully.  
db2 describe table db2inst1.or_test

检查 AUDIT 数据存放路径和数据归档路径

如果 AUDIT ACTIVE /SQLCA ON AUDIT ERROR 是 FALSE 这表明没有开启 AUDIT, 对 AUDIT 数据存放路径和归档路径最好不要为空,为空将用 INSTANCE 缺省路径 sqllib/security/auditdata

为了避免冲突,笔者自己创建了审计 AUDIT 数据存放路径如下

mkdir -p /home/db2inst1/db2backup/audit/data/

mkdir -p /home/db2inst1/db2backup/audit/archive/

db2audit configure datapath /home/db2inst1/db2backup/audit/data/ archivepath /home/db2inst1/db2backup/audit/archive/

3.创建 policy

db2 "create audit policy AUDIT_TABLE1 categories execute with data status both error type normal"

DB20000I The SQL command completed successfully.

db2 commit;

DB20000I The SQL command completed successfully.

db2audit start

AUD0000I Operation succeeded.

执行 db2auit descirbe 检查审计环境是否建立

4.对表 or_test 进行 audit

db2 "audit table db2inst1.or_test using policy AUDIT_TABLE1"

DB20000I The SQL command completed successfully.

db2 commit

DB20000I The SQL command completed successfully.

db2 "select * from syscat.audituse"

5.用 java 在 db2inst1.or_test 表上执行 DML 操作

db2 "insert into db2inst1.or_test values('xxxx',’xxxxx’)"

public static void main(String[] args) throws Exception {

// TODO Auto-generated method stub

//countall();

addwork();

// checkdata();

}

public static void addwork() {

try {

dowork();

} catch (Exception e) {

e.printStackTrace();

}

}

public static void countall() throws SQLException {

OPERCHIS db2d = new OPERCHIS();

String SQL = "SELECT COUNT(*) FROM db2inst1.or_test";

ResultSet rs = db2d.executeQuery(SQL, "111");

while (rs.next()) {

System.out.println("Total count: " + rs.getString(1));

}

}

public static void dowork() throws SQLException {

OPERCHIS sconn = new OPERCHIS();

String insersql = "insert into db2inst1.or_test values(?,?)";

ps = sconn.prepareStatement(insersql, "999");

try {

while (allcount < TOTAL) {

subdata(1000);

adddata();

Thread.sleep(5);

}

subdata(0);

} catch (Exception e) {

e.printStackTrace();

}

}

6.执行 db2audit archive 操作生成 db2audit 数据 , 执行 db2audit flush 刷新审计日志到磁盘

归档实例审计日 志 ( 默认 ) 实列范围

db2audit archive

推荐执行完这步后再执行

归档数据库审计日 志 db2audit archive database dbname

db2audit archive database CSDW

Node AUD Archived or Interim Log File

做完以上两步后你讲在审计归档目录中发现审计文件

7.从 db2audit log 文件中抽取数据

db2audit extract delasc from files db2audit.instance.log.0.20190901195426 AUD0000I

Operation succeeded.

db2audit extract delasc from files db2audit.db.CSDW.log.0.20190901195821

执行完审计数据抽取后讲生成下列数据

audit.del checking.del execute.del objmaint.del sysadmin.del

auditlobs context.del instance.aud secmaint.del validate.del

预览执行 SQL 概况

more execute.del


$

8.利用 misc 目录中的 db2audit.ddl 创建存放 audit 数据的表

pwd /home/db2inst1/sqllib/misc

db2 -tvf db2audit.ddl

9.将 audit 数据加载到表中

db2 "LOAD FROM checking.del OF DEL LOBS FROM /home/db2inst1/db2backup/audit/archive MODIFIED BY DELPRIORITYCHAR lobsinfile INSERT INTO CHECKING"

db2 "LOAD FROM objmaint.del OF DEL LOBS FROM /home/db2inst1/db2backup/audit/archive MODIFIED BY DELPRIORITYCHAR lobsinfile INSERT INTO OBJMAINT"

db2 "LOAD FROM secmaint.del OF DEL LOBS FROM /home/db2inst1/db2backup/audit/archive MODIFIED BY DELPRIORITYCHAR lobsinfile INSERT INTO SECMAINT"

db2 "LOAD FROM sysadmin.del OF DEL LOBS FROM /home/db2inst1/db2backup/audit/archive MODIFIED BY DELPRIORITYCHAR lobsinfile INSERT INTO SYSADMIN"

db2 "LOAD FROM validate.del OF DEL LOBS FROM /home/db2inst1/db2backup/audit/archive MODIFIED BY DELPRIORITYCHAR lobsinfile INSERT INTO VALIDATE"

db2 "LOAD FROM context.del OF DEL LOBS FROM /home/db2inst1/db2backup/audit/archive MODIFIED BY DELPRIORITYCHAR lobsinfile INSERT INTO CONTEXT"

db2 "LOAD FROM execute.del OF DEL LOBS FROM /home/db2inst1/db2backup/audit/archive MODIFIED BY DELPRIORITYCHAR lobsinfile INSERT INTO EXECUTE"

加载数据到表后我们可以执行 SQL 提取变量值参照如下 SQL ,在 SQL 正文后面的行是具体变量值。

db2 "select substr(char(stmttext),1,100)),substr(char( STMTVALDATA),100) from execute"

验证审计数据

抓取数据后关闭 AUDIT db2audit stop

https://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.sec.doc/doc/c0005483.html

https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.admin.sec.doc/com.ibm.db2.luw.admin.sec.doc-gentopic11.html

https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.admin.sec.doc/doc/c0052328.html

感谢:

感谢我的同事倪春梅和 James Huang , 代华凌 在实现此验证实验中协助我做了大量的工作。感谢我的 PM 车铃的支持,让我能迅速或得实验环境。

如果觉得我的文章对您有用,请点赞。您的支持将鼓励我继续创作!

4

添加新评论0 条评论

Ctrl+Enter 发表

作者其他文章

关于TWT  使用指南  社区专家合作  厂商入驻社区  企业招聘  投诉建议  版权与免责声明  联系我们
© 2019  talkwithtrend — talk with trend,talk with technologist 京ICP备09031017号-30