目的:
测试DB2审计功能,本脚本摘录自DB2安装目录admin_scripts/audit.db2。
版本:Windows DB29.7
用户要求:
操作系统用户有DBADM,SECADM权限;操作系统用户joe,密码abcd1234。
操作步骤:
1,设置db2audit环境
2,创建、修改、删除审计策略
3,在不同对象上应用审计
4,操作数据库以产生审计数据
5,归档涉及数据到指定位置
6,从归档中提取审计信息
7,装载审计文件到审计表
8,清理测试环境
注:
为了在CLP交互模式下支持多行输入,可以使用"db2cmd db2 -t"进入交互模式。
----------------------------------------------------
1,设置db2audit环境
--建库
CREATE DATABASE BANKDB;
CONNECT TO BANKDB;
-- Create an 8K bufferpool.
CREATE BUFFERPOOL bpool8k SIZE 20000 PAGESIZE 8 K;
-- Create an 8K tablespace associating the bufferpool bpool8k.
CREATE TABLESPACE tbsp_8k
PAGESIZE 8 K
MANAGED BY DATABASE
USING (file '%DB2PATH%\audittbsps' 20000)
BUFFERPOOL bpool8k;
-- Grant the SECADM privilege to execute the audit statements.
GRANT SECADM ON DATABASE TO USER joe;
-- Grant the EXECUTE privilege to execute the audit routines.
GRANT EXECUTE ON FUNCTION SYSPROC.AUDIT_ARCHIVE TO USER joe;
GRANT EXECUTE ON PROCEDURE SYSPROC.AUDIT_ARCHIVE TO USER joe;
GRANT EXECUTE ON PROCEDURE SYSPROC.AUDIT_DELIM_EXTRACT TO USER joe;
GRANT EXECUTE ON FUNCTION SYSPROC.AUDIT_LIST_LOGS TO USER joe;
-- Connect to BANKDB as SECADM.
CONNECT TO BANKDB USER joe USING abcd1234;
-- AUDIT CATEGORY
CREATE TABLE DB2AUDIT.AUDIT ( ...);
-- CHECKING CATEGORY
CREATE TABLE DB2AUDIT.CHECKING ( ...);
-- OBJMAINT CATEGORY
CREATE TABLE DB2AUDIT.OBJMAINT ( ...);
-- SECMAINT CATEGORY
CREATE TABLE DB2AUDIT.SECMAINT ( ...);
-- SYSADMIN CATEGORY
CREATE TABLE DB2AUDIT.SYSADMIN ( ...);
-- VALIDATE CATEGORY
CREATE TABLE DB2AUDIT.VALIDATE ( ...);
-- CONTEXT CATEGORY
CREATE TABLE DB2AUDIT.CONTEXT ( ...);
-- EXECUTE CATEGORY
CREATE TABLE DB2AUDIT.EXECUTE ( ...);
-- Configure the datapath and archivepath for audit purpose.
! db2audit CONFIGURE datapath "%DB2PATH%"
archivepath "%DB2PATH%";
--测试用表
CREATE TABLE TRANSACTION ( AccNo INT NOT NULL,
TrDate DATE,
TrType VARCHAR(10),
Amount DECIMAL(7,2),
Remarks VARCHAR(50) );
CREATE TABLE ACCOUNT ( CName VARCHAR(30),
AccType VARCHAR(10) NOT NULL,
AccNo INT NOT NULL PRIMARY KEY,
CType VARCHAR(10),
TrDate DATE,
Balance DECIMAL(9,2),
Remarks VARCHAR(50) );
CREATE TABLE PERSONALINFO ( CName VARCHAR(30),
CType VARCHAR(10),
CPhone VARCHAR(15),
CAddress VARCHAR(100),
LoanInfo VARCHAR(50),
Remarks VARCHAR(50) );
2,创建、修改、删除审计策略
-- Create TRANSACTIONPOLICY to generate the audit records to show the
-- execution of SQL statements.
CREATE AUDIT POLICY TRANSACTIONPOLICY
CATEGORIES EXECUTE STATUS BOTH
ERROR TYPE AUDIT;
-- Create ACCOUNTPOLICY to generate the audit records for any thing happening
-- on the table.
CREATE AUDIT POLICY ACCOUNTPOLICY
CATEGORIES ALL STATUS BOTH
ERROR TYPE AUDIT;
-- Create PERSONALADMINPOLICY to generate the audit records when
-- creating or dropping data objects,
-- granting or revoking object privileges,
-- granting or revoking database privileges,
-- granting or revoking DBADM authority,
-- authenticating users or retrieving system
-- security information related to a user.
CREATE AUDIT POLICY PERSONALADMINPOLICY
CATEGORIES OBJMAINT STATUS BOTH,
SECMAINT STATUS BOTH,
SYSADMIN STATUS BOTH,
VALIDATE STATUS FAILURE
ERROR TYPE AUDIT;
COMMIT;
-- Alter the current policy TRANSACTIONPOLICY to audit all the information.
ALTER AUDIT POLICY TRANSACTIONPOLICY
CATEGORIES ALL STATUS BOTH
ERROR TYPE AUDIT;
COMMIT;
-- Drop the the current policy TRANSACTIONPOLICY
-- DROP AUDIT POLICY TRANSACTIONPOLICY;
-- COMMIT;
3,在不同对象上应用审计
-- Audit the database using the TRANSACTIONPOLICY policy.
AUDIT DATABASE USING POLICY TRANSACTIONPOLICY;
-- Audit the table ACCOUNT using ACCOUNTPOLICY policy.
AUDIT TABLE ACCOUNT USING POLICY ACCOUNTPOLICY;
-- Audit the SYSADM, SYSMAINT, SECADM, DBADM activities using
-- PERSONALADMINPOLICY policy.
AUDIT SYSADM, SYSMAINT, SECADM, DBADM USING POLICY PERSONALADMINPOLICY;
4,操作数据库以产生审计数据
-- Run transactions on table TRANSACTION
-- Insert records into the table TRANSACTION
--
INSERT INTO TRANSACTION VALUES ( 1000, '2007-01-20', 'CREDIT', 2000.00, '2000$
got credited from AccNo 01232 Citi Bank');
INSERT INTO TRANSACTION VALUES ( 1000, '2007-01-20', 'CREDIT', 1050.00, '1050$
got credited from AccNo 98211 AMEX Bank');
COMMIT;
-- Select the records from TRANSACTION
SELECT TrDate, TrType, Amount, Remarks FROM TRANSACTION;
-- Insert records into the table ACCOUNT
INSERT INTO ACCOUNT VALUES ( 'MOHAN SARASWATIPURA', 'SALARY', 1000, 'GOLD',
'2007-01-30', 20000.00, 'Balance amount
is 20000$');
INSERT INTO ACCOUNT VALUES ( 'PRAVEEN SOGALAD', 'SALARY', 1010, 'GOLD',
'2007-01-30', 20200.00, 'Balance amount is 20200$');
INSERT INTO ACCOUNT VALUES ( 'SANJAY KUMAR', 'SALARY', 1020, 'GOLD','2007-02-13',
30010.00, 'Balance amount is 30010$');
COMMIT;
-- Select the records from ACCOUNT
SELECT CName, AccType, AccNo, TrDate, Balance FROM ACCOUNT;
-- Insert records into the table PERSONALINFO
INSERT INTO PERSONALINFO VALUES ( 'MOHAN SARASWATIPURA', 'GOLD', '9880012396',
'VIJAYNAGAR, BANGALORE', 'PERSONAL LOAN PLoanNo:
1233812', 'GOLD CUSTOMER');
INSERT INTO PERSONALINFO VALUES ( 'PRAVEEN SOGALAD', 'GOLD', '9881212391',
'VIJAYNAGAR, BANGALORE', 'HOME LOAN HLoanNo:
0129111', 'GOLD CUSTOMER');
COMMIT;
5,归档审计数据到指定位置
-- 方法: 1
-- ! db2audit ARCHIVE DATABASE BANKDB TO "%DB2PATH%";
-- 方法: 2
! db2 "CONNECT TO BANKDB USER joe USING abcd1234";
! db2 "CALL SYSPROC.AUDIT_ARCHIVE('%DB2PATH%',0)";
-- Parameter '%DB2PATH%' specifies the archive location.
-- -2 instead of 0 enables the archive on all the nodes in case of MPP setup.
6,从归档中提取审计信息
! db2 "CONNECT TO BANKDB USER joe USING abcd1234";
! db2 "CALL SYSPROC.AUDIT_DELIM_EXTRACT (';','%DB2PATH%','%DB2PATH%',
'db2audit.db.BANKDB.log.%.20%',' ')";
-- Parameter ';' specifies the delimiter in extracted log files.
-- Parameter '%DB2PATH%' specifies the audit logs extract location.
-- Parameter '%DB2PATH%' specifies the archive logs location.
-- Parameter 'db2audit.db.BANKDB.log.%.20%' specifies the log name.
-- Parameter ' ' directs stored procedure to extract all the activities.
-- One can specify 'execute status failure' to extract only the execute
-- failures.
7,装载审计文件到审计表
--装载涉及数据
! db2stop force;
! db2start;
! db2 "CONNECT TO BANKDB USER joe USING abcd1234";
! db2 "SET SCHEMA DB2AUDIT";
! db2 "IMPORT FROM %DB2PATH%/audit.del OF DEL REPLACE INTO AUDIT";
! db2 "IMPORT FROM %DB2PATH%/checking.del OF DEL REPLACE INTO CHECKING";
! db2 "IMPORT FROM %DB2PATH%/objmaint.del OF DEL REPLACE INTO OBJMAINT";
! db2 "IMPORT FROM %DB2PATH%/secmaint.del OF DEL REPLACE INTO SECMAINT";
! db2 "IMPORT FROM %DB2PATH%/sysadmin.del OF DEL REPLACE INTO SYSADMIN";
! db2 "IMPORT FROM %DB2PATH%/validate.del OF DEL REPLACE INTO VALIDATE";
! db2 "IMPORT FROM %DB2PATH%/context.del OF DEL REPLACE INTO CONTEXT";
! db2 "IMPORT FROM %DB2PATH%/execute.del OF DEL REPLACE INTO EXECUTE";
--查看审计数据
CONNECT TO BANKDB USER joe USING abcd1234;
SET SCHEMA DB2AUDIT;
SELECT * FROM AUDIT;
SELECT * FROM CHECKING;
SELECT * FROM OBJMAINT;
SELECT * FROM SECMAINT;
SELECT * FROM SYSADMIN;
SELECT * FROM VALIDATE;
SELECT * FROM CONTEXT;
SELECT * FROM EXECUTE;
8,清理测试环境
-- /***************************************************************************/
-- /* CLEAN UP */
-- /***************************************************************************/
! db2stop force;
! db2start;
DROP DB BANKDB;
!del %DB2PATH%\audit.del;
!del %DB2PATH%\checking.del;
!del %DB2PATH%\context.del;
!del %DB2PATH%\execute.del;
!del %DB2PATH%\objmaint.del;
!del %DB2PATH%\secmaint.del;
!del %DB2PATH%\sysadmin.del;
!del %DB2PATH%\validate.del;
!cd %DB2PATH%;
!del db2audit.db.BANKDB.*;
!del %DB2PATH%\auditlobs;
TERMINATE;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/18922393/viewspace-701934/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/18922393/viewspace-701934/