数据库审计方案(包括login,logout,DML,DDL等)

数据库审计方案(包括login,logout,DML,DDL等)

/*===============================================================================
||文件名：数据库审计事件记录脚本
||说明：为了对数据库事件进行审计，而进行相关设计
  ==============================================================================*/

/* ========================= 审计记录表 ========================= */

/* -------- 系统会话事件审计表 -------- */
CREATE TABLE SYSTEM.Audit_Login_DB(
/*
||名称：审计数据库会话登陆事件记录表
||说明：
*/
  Session_Id   NUMBER,          /* 会话ID */
  OS_User      VARCHAR2(200),   /* 终端OS用户 */
  IP_Address   VARCHAR2(200),   /* 终端IP地址 */
  Terminal     VARCHAR2(200),   /* 终端 */
  Host         VARCHAR2(200),   /* 终端主机名 */
  User_Name    VARCHAR2(30),    /* ORACLE 用户名*/
  LogOn_Date   DATE,            /* 登陆时间 */
  LogOff_Date  DATE,            /* 登离时间 */
  Elapsed_Minutes   NUMBER      /* 在线时间 */
)
TABLESPACE TOOLS;

/* -------- 会话事件审计表索引 --------- */
CREATE INDEX IX_AUDIT_LOGIN_SESSIONID ON SYSTEM.AUDIT_LOGIN_DB(SESSION_ID)
TABLESPACE INDX;

CREATE INDEX IX_AUDIT_LOGIN_LOGONDATE ON SYSTEM.AUDIT_LOGIN_DB(LOGON_DATE)
TABLESPACE INDX;


/* -------- DDL事件审计表 -------- */
CREATE TABLE system.Audit_DDL_OBJ(
/*
||名称：审计针对数据库对象的DDL记录表
||说明：
*/
  Opr_Time     DATE,            /* 操作时间 */
  Session_Id   NUMBER,          /* 会话ID */
  OS_User      VARCHAR2(200),   /* 终端OS用户 */
  IP_Address   VARCHAR2(200),   /* 终端IP地址 */
  Terminal     VARCHAR2(200),   /* 终端 */
  Host         VARCHAR2(200),   /* 终端主机名 */
  User_Name    VARCHAR2(30),    /* ORACLE 用户名*/
  DDL_Type     VARCHAR2(30),    /* DDL操作类型 */
  DDL_Sql      VARCHAR2(2000),  /* DDL语句 */
  Object_Type  VARCHAR2(18),    /* 操作对象类型 */
  Owner        VARCHAR2(30),    /* 对象拥有者 */
  Object_Name  VARCHAR2(128)    /* 对象名称 */
)
;

/* -------- DDL事件审计表索引 --------- */
CREATE INDEX IX_AUDIT_DDL_SESSIONID ON SYSTEM.AUDIT_DDL_OBJ(SESSION_ID)
TABLESPACE INDX;

CREATE INDEX IX_AUDIT_DDL_OPRTIME ON SYSTEM.AUDIT_DDL_OBJ(OPR_TIME)
TABLESPACE INDX;


/* ========================= 审计触发器 ========================= */

/* ------- AFTER LOGON ON DATABASE 触发器 -------*/
CREATE OR REPLACE TRIGGER SYS.Login_Audit_Trigger
AFTER LOGON ON DATABASE
/*
||名称：会话登陆事件审计触发器
||说明：
*/
DECLARE

   Session_Id_Var   NUMBER;          /* 会话ID */
   Os_User_Var      VARCHAR2(200);   /* 终端OS用户 */
   IP_Address_Var   VARCHAR2(200);   /* 终端IP */
   Terminal_Var     VARCHAR2(200);   /* 终端 */
   Host_Var         VARCHAR2(200);   /* 终端主机名 */

BEGIN

   /* 获取登陆用户信息 */
   SELECT   SYS_CONTEXT('USERENV','SESSIONID'),
            SYS_CONTEXT('USERENV','OS_USER'),
            SYS_CONTEXT('USERENV','IP_ADDRESS'),
            SYS_CONTEXT('USERENV','TERMINAL'),
            SYS_CONTEXT('USERENV','HOST')
   INTO     Session_Id_Var,
            Os_User_Var,
            IP_Address_Var,
            Terminal_Var,
            Host_Var
   FROM     DUAL;

   /* 记录登陆审计信息 */
   INSERT INTO system.Audit_Login_DB(
               Session_Id,      /* 会话ID */
               OS_User,         /* 终端OS用户 */
               IP_Address,      /* 终端IP地址 */
               Terminal,        /* 终端 */
               Host,            /* 终端主机名 */
               User_Name,       /* ORACLE 用户名*/
               LogOn_Date,      /* 登陆时间 */
               LogOff_Date,     /* 登离时间 */
               Elapsed_Minutes  /* 在线时间 */
              )
       VALUES( Session_Id_Var,
               Os_User_Var,
               IP_Address_Var,
               Terminal_Var,
               Host_Var,
               USER,
               SYSDATE,
               NULL,
               NULL);

   COMMIT;
EXCEPTION
   WHEN OTHERS THEN
      NULL;
END Login_Audit_Trigger;
/


/* ------- BEFORE LOGOFF ON DATABASE 触发器 -------*/
CREATE OR REPLACE TRIGGER SYS.LogOff_Audit_Trigger
BEFORE LOGOFF ON DATABASE
/*
||名称：会话登离事件审计触发器
||说明：
*/
DECLARE

   Session_Id_Var   NUMBER;          /* 会话ID */

BEGIN

   /* 获取登陆用户信息 */
   SELECT   SYS_CONTEXT('USERENV','SESSIONID')
   INTO     Session_Id_Var
   FROM     DUAL;

   /* 更新会话审计记录信息 */
   UPDATE system.Audit_Login_DB
      SET LogOff_Date = SYSDATE,
          Elapsed_Minutes = ROUND((SYSDATE - LogOn_Date)* 1440)
   WHERE  Session_Id = Session_Id_Var;
   --WHERE  SYS_CONTEXT('USERENV','SESSIONID') = Session_Id;

   COMMIT;

EXCEPTION
   WHEN OTHERS THEN
      NULL;

END LogOff_Audit_Trigger;
/


/* ------- AFTER DDL ON DATABASE 触发器 -------*/
CREATE OR REPLACE TRIGGER SYS.DDL_Audit_Trigger
AFTER DDL ON DATABASE
/*
||名称：DDL事件审计触发器
||说明：
*/
DECLARE

   Session_Id_Var   NUMBER;          /* 会话ID */
   Os_User_Var      VARCHAR2(200);   /* 终端OS用户 */
   IP_Address_Var   VARCHAR2(200);   /* 终端IP */
   Terminal_Var     VARCHAR2(200);   /* 终端 */
   Host_Var         VARCHAR2(200);   /* 终端主机名 */
   Cut              NUMBER;          /* SQL列表长度 */
   Sql_Text         ORA_NAME_LIST_T; /* SQL_TEXT 列表 */
   L_Trace          NUMBER;          /* 循环执行条件 */
   DDL_Sql_Var      VARCHAR2(2000);  /* DDL语句 */

BEGIN

   /* 获取操作用户信息 */
   SELECT   SYS_CONTEXT('USERENV','SESSIONID'),
            SYS_CONTEXT('USERENV','OS_USER'),
            SYS_CONTEXT('USERENV','IP_ADDRESS'),
            SYS_CONTEXT('USERENV','TERMINAL'),
            SYS_CONTEXT('USERENV','HOST')
   INTO     Session_Id_Var,
            Os_User_Var,
            IP_Address_Var,
            Terminal_Var,
            Host_Var
   FROM     DUAL;

   /* 获取DDL SQL语句 */
   BEGIN

      SELECT COUNT(*) INTO L_Trace FROM DUAL
       WHERE ORA_DICT_OBJ_NAME NOT LIKE 'MLOG%'
         AND ORA_DICT_OBJ_NAME NOT LIKE '%LOG'
         AND UTL_INADDR.GET_HOST_ADDRESS IS NOT NULL
         AND SYS_CONTEXT('USERENV','IP_ADDRESS') IS NOT NULL
         AND SYS_CONTEXT('USERENV','IP_ADDRESS') <> UTL_INADDR.GET_HOST_ADDRESS;

      IF L_Trace > 0 THEN

         Cut := ORA_SQL_TXT(Sql_Text);

         FOR i IN 1..Cut LOOP
            DDL_Sql_Var := SUBSTR(DDL_Sql_Var || Sql_Text(i),1,2000);
         END LOOP;
      END IF;

   EXCEPTION
      WHEN OTHERS THEN
         NULL;
   END;

   /* 记录登陆审计信息 */
   INSERT INTO system.Audit_DDL_OBJ(
               Opr_Time,     /* 操作时间 */
               Session_Id,   /* 会话ID */
               OS_User,      /* 终端OS用户 */
               IP_Address,   /* 终端IP地址 */
               Terminal,     /* 终端 */
               Host,         /* 终端主机名 */
               User_Name,    /* ORACLE 用户名*/
               DDL_Type,     /* DDL操作类型 */
               DDL_Sql,      /* DDL语句 */
               Object_Type,  /* 操作对象类型 */
               Owner,        /* 对象拥有者 */
               Object_Name   /* 对象名称 */
              )
       VALUES( SYSDATE,
               Session_Id_Var,
               Os_User_Var,
               IP_Address_Var,
               Terminal_Var,
               Host_Var,
               ORA_LOGIN_USER,
               ORA_SYSEVENT,
               DDL_Sql_Var,
               ORA_DICT_OBJ_TYPE,
               ORA_DICT_OBJ_OWNER,
               ORA_DICT_OBJ_NAME);

   COMMIT;

EXCEPTION
   WHEN OTHERS THEN
      NULL;

END DDL_Audit_Trigger;
/

/* ========================= 审计DML语句 ========================= */
/*
||名称：利用DBMS_FGA包的细粒度审计功能实现对DML语句的审计
||说明：
*/

CREATE SEQUENCE SEQ_SELECT_NUMBER MINVALUE 1 MAXVALUE 100000 START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE SEQ_INSERT_NUMBER MINVALUE 1 MAXVALUE 100000 START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE SEQ_UPDATE_NUMBER MINVALUE 1 MAXVALUE 100000 START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE SEQ_DELETE_NUMBER MINVALUE 1 MAXVALUE 100000 START WITH 1 INCREMENT BY 1;
SELECT 'DBMS_FGA.ADD_POLICY(OBJECT_SCHEMA => '''||OWNER||''', OBJECT_NAME => '''||TABLE_NAME||''',STATEMENT_TYPES => ''SELECT'',POLICY_NAME => ''CHK_SELECT_'||SEQ_SELECT_NUMBER.NEXTVAL||'''); ' FROM ALL_TABLES WHERE WNER='INXITE'
UNION
SELECT 'DBMS_FGA.ADD_POLICY(OBJECT_SCHEMA => '''||OWNER||''', OBJECT_NAME => '''||TABLE_NAME||''',STATEMENT_TYPES => ''INSERT'',POLICY_NAME => ''CHK_INSERT_'||SEQ_INSERT_NUMBER.NEXTVAL||'''); ' FROM ALL_TABLES WHERE WNER='INXITE'
UNION
SELECT 'DBMS_FGA.ADD_POLICY(OBJECT_SCHEMA => '''||OWNER||''', OBJECT_NAME => '''||TABLE_NAME||''',STATEMENT_TYPES => ''UPDATE'',POLICY_NAME => ''CHK_UPDATE_'||SEQ_UPDATE_NUMBER.NEXTVAL||'''); ' FROM ALL_TABLES WHERE WNER='INXITE'
UNION
SELECT 'DBMS_FGA.ADD_POLICY(OBJECT_SCHEMA => '''||OWNER||''', OBJECT_NAME => '''||TABLE_NAME||''',STATEMENT_TYPES => ''DELETE'',POLICY_NAME => ''CHK_DELETE_'||SEQ_DELETE_NUMBER.NEXTVAL||'''); ' FROM ALL_TABLES WHERE WNER='INXITE'