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

来源：互联网发布：手机淘宝代卖编辑：程序博客网时间：2024/06/02 00:26

原创于2008年11月19日，2009年10月19日迁移至此。

数据库审计方案(包括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_TABLESWHERE 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_TABLESWHERE 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_TABLESWHERE 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_TABLESWHERE WNER='INXITE'

--需要在以上生成的语句前加上BEGIN，END；语句，以补充完全

参考文档：
细粒度审计(FGA) http://www.itpub.net/viewthread.php?tid=239693&extra=page%3D2%26amp%3Bfilter%3Ddigest
Oracle Audit Vault http://www.oracle.com/technology/global/cn/products/audit-vault/index.html
创建监控表的DML的触发器--Oracle审计http://203.208.33.101/search?q=cache:MueGFCXzT6wJ:blog.oracle.com.cn/index.php/181019/viewspace-30154+Oracle+AFTER+DML+ON+DATABASE&hl=zh-CN&ct=clnk&cd=3&gl=cn&lr=lang_zh-CN&st_usg=ALhdy28qAdXB_OiidITLXDsOWWwjr0WCLw
数据库审计事件记录脚本宇野,抱歉找不到原地址链接了