DB2_審計

目的：
測試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;