# 启用审计
db2audit configure scope all status both;

# 启动审计
db2audit start;

# 查看审计配置
db2audit describe;

# 配置审计日志路径
db2audit configure datapath /db2/audit;

# 配置审计日志大小
db2audit configure archivesize 1000;

# 查看审计状态
db2audit status;

# 停止审计
db2audit stop;

# 清空审计日志
db2audit flush;

# 配置审计范围
# 审计所有安全相关事件
db2audit configure scope security status both;

# 审计所有对象访问
db2audit configure scope objmaint status both;

# 审计所有SQL语句
db2audit configure scope execute status both;

# 审计权限变更
db2audit configure scope checking status both;

# 审计上下文信息
db2audit configure scope context status both;

# 审计所有范围
db2audit configure scope all status both;

# 查看审计配置
db2audit describe;

# 创建审计策略表
CREATE TABLE AUDIT_POLICY (
POLICY_ID INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
POLICY_NAME VARCHAR(100) NOT NULL,
OBJECT_TYPE VARCHAR(50) NOT NULL,
OBJECT_SCHEMA VARCHAR(128),
OBJECT_NAME VARCHAR(128),
AUDIT_ACTIONS VARCHAR(500),
ENABLED VARCHAR(1) DEFAULT ‘Y’,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP
);

# 插入审计策略
INSERT INTO AUDIT_POLICY VALUES
(1, ‘AUDIT_ORDERS’, ‘TABLE’, ‘FGEDB’, ‘ORDERS’, ‘SELECT,INSERT,UPDATE,DELETE’, ‘Y’, CURRENT TIMESTAMP),
(2, ‘AUDIT_CUSTOMER’, ‘TABLE’, ‘FGEDB’, ‘CUSTOMER’, ‘SELECT,INSERT,UPDATE,DELETE’, ‘Y’, CURRENT TIMESTAMP),
(3, ‘AUDIT_SENSITIVE’, ‘TABLE’, ‘FGEDB’, ‘SENSITIVE_DATA’, ‘ALL’, ‘Y’, CURRENT TIMESTAMP);

# 创建审计触发器
CREATE TRIGGER TRG_AUDIT_ORDERS_INSERT
AFTER INSERT ON ORDERS
REFERENCING NEW AS N
FOR EACH ROW
BEGIN
INSERT INTO AUDIT_LOG (
AUDIT_TIME, USER_NAME, TABLE_NAME, ACTION, RECORD_ID, NEW_VALUES
) VALUES (
CURRENT TIMESTAMP, CURRENT_USER, ‘ORDERS’, ‘INSERT’, N.ORDER_ID,
‘ORDER_ID=’ || N.ORDER_ID || ‘,CUSTOMER_ID=’ || N.CUSTOMER_ID
);
END;

CREATE TRIGGER TRG_AUDIT_ORDERS_UPDATE
AFTER UPDATE ON ORDERS
REFERENCING OLD AS O NEW AS N
FOR EACH ROW
BEGIN
INSERT INTO AUDIT_LOG (
AUDIT_TIME, USER_NAME, TABLE_NAME, ACTION, RECORD_ID, OLD_VALUES, NEW_VALUES
) VALUES (
CURRENT TIMESTAMP, CURRENT_USER, ‘ORDERS’, ‘UPDATE’, N.ORDER_ID,
‘STATUS=’ || O.ORDER_STATUS,
‘STATUS=’ || N.ORDER_STATUS
);
END;

CREATE TRIGGER TRG_AUDIT_ORDERS_DELETE
AFTER DELETE ON ORDERS
REFERENCING OLD AS O
FOR EACH ROW
BEGIN
INSERT INTO AUDIT_LOG (
AUDIT_TIME, USER_NAME, TABLE_NAME, ACTION, RECORD_ID, OLD_VALUES
) VALUES (
CURRENT TIMESTAMP, CURRENT_USER, ‘ORDERS’, ‘DELETE’, O.ORDER_ID,
‘ORDER_ID=’ || O.ORDER_ID
);
END;

# 创建用户审计表
CREATE TABLE USER_AUDIT_LOG (
LOG_ID BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
USER_NAME VARCHAR(128) NOT NULL,
ACTION_TYPE VARCHAR(50) NOT NULL,
ACTION_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CLIENT_IP VARCHAR(50),
APPLICATION_NAME VARCHAR(100),
DETAILS VARCHAR(1000)
);

# 创建登录审计触发器
CREATE OR REPLACE PROCEDURE SP_LOG_USER_LOGIN(
IN p_user_name VARCHAR(128),
IN p_client_ip VARCHAR(50),
IN p_app_name VARCHAR(100)
)
LANGUAGE SQL
BEGIN
INSERT INTO USER_AUDIT_LOG (
USER_NAME, ACTION_TYPE, CLIENT_IP, APPLICATION_NAME, DETAILS
) VALUES (
p_user_name, ‘LOGIN’, p_client_ip, p_app_name, ‘User logged in’
);
END;

# 创建权限变更审计
CREATE TRIGGER TRG_AUDIT_GRANT
AFTER GRANT ON DATABASE
BEGIN
INSERT INTO USER_AUDIT_LOG (
USER_NAME, ACTION_TYPE, DETAILS
) VALUES (
CURRENT_USER, ‘GRANT’, ‘Privilege granted’
);
END;

# 查询用户审计日志
SELECT
USER_NAME,
ACTION_TYPE,
ACTION_TIME,
CLIENT_IP,
APPLICATION_NAME
FROM USER_AUDIT_LOG
WHERE ACTION_TIME >= CURRENT DATE
ORDER BY ACTION_TIME DESC;

# 归档审计日志
db2audit archive database FGEDB to /db2/audit/archive;

# 提取审计日志
db2audit extract delasc to /db2/audit/extract from /db2/audit/archive/audit.db2.node0000.catn0000;

# 导入审计日志到表
LOAD FROM /db2/audit/extract/audit.del OF DEL
INSERT INTO AUDIT_LOG_TABLE;

# 查询审计日志
SELECT
TIMESTAMP,
CATEGORY,
EVENT,
USERID,
APPNAME,
CORRELATOR
FROM AUDIT_LOG_TABLE
WHERE TIMESTAMP >= CURRENT DATE – 7 DAYS
ORDER BY TIMESTAMP DESC;

# 分析登录失败
SELECT
USERID,
COUNT(*) AS FAILED_COUNT,
MAX(TIMESTAMP) AS LAST_FAILED_TIME
FROM AUDIT_LOG_TABLE
WHERE EVENT = ‘LOGIN_FAILED’
AND TIMESTAMP >= CURRENT DATE – 7 DAYS
GROUP BY USERID
ORDER BY FAILED_COUNT DESC;

# 分析权限变更
SELECT
USERID,
EVENT,
OBJECTNAME,
TIMESTAMP
FROM AUDIT_LOG_TABLE
WHERE CATEGORY = ‘SECURITY’
AND EVENT IN (‘GRANT’, ‘REVOKE’)
AND TIMESTAMP >= CURRENT DATE – 30 DAYS
ORDER BY TIMESTAMP DESC;

# 创建审计报告存储过程
CREATE OR REPLACE PROCEDURE SP_GENERATE_AUDIT_REPORT(
IN p_start_date DATE,
IN p_end_date DATE
)
LANGUAGE SQL
BEGIN
DECLARE v_total_events INTEGER;
DECLARE v_failed_logins INTEGER;
DECLARE v_privilege_changes INTEGER;
DECLARE v_ddl_operations INTEGER;

SELECT COUNT(*) INTO v_total_events
FROM AUDIT_LOG_TABLE
WHERE DATE(TIMESTAMP) BETWEEN p_start_date AND p_end_date;

SELECT COUNT(*) INTO v_failed_logins
FROM AUDIT_LOG_TABLE
WHERE EVENT = ‘LOGIN_FAILED’
AND DATE(TIMESTAMP) BETWEEN p_start_date AND p_end_date;

SELECT COUNT(*) INTO v_privilege_changes
FROM AUDIT_LOG_TABLE
WHERE CATEGORY = ‘SECURITY’
AND EVENT IN (‘GRANT’, ‘REVOKE’)
AND DATE(TIMESTAMP) BETWEEN p_start_date AND p_end_date;

SELECT COUNT(*) INTO v_ddl_operations
FROM AUDIT_LOG_TABLE
WHERE CATEGORY = ‘OBJMAINT’
AND DATE(TIMESTAMP) BETWEEN p_start_date AND p_end_date;

INSERT INTO AUDIT_REPORT (
REPORT_DATE, START_DATE, END_DATE, TOTAL_EVENTS,
FAILED_LOGINS, PRIVILEGE_CHANGES, DDL_OPERATIONS
) VALUES (
CURRENT DATE, p_start_date, p_end_date, v_total_events,
v_failed_logins, v_privilege_changes, v_ddl_operations
);
END;

# 生成审计报告
CALL SP_GENERATE_AUDIT_REPORT(CURRENT DATE – 7 DAYS, CURRENT DATE);

# 查看审计报告
SELECT * FROM AUDIT_REPORT ORDER BY REPORT_DATE DESC;