oracle 触发器_详解Oracle审计4大触发器--会话登录、登出、DDL事件、表修改事件...

概述

今天主要介绍一下关于Oracle安全的核心4大触发器，主要是做审计用，仅供参考。

---

1、会话登陆事件审计触发器(数据库级别)

CREATE OR REPLACE TRIGGER 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 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;

---

2、会话登离事件审计触发器(数据库级别)

CREATE OR REPLACE TRIGGER 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 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;

---

3、DDL事件审计触发器(数据库级别)

CREATE OR REPLACE TRIGGER 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 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;

---

4、核心表删除事件审计触发器(表级别)

CREATE OR REPLACE TRIGGER delete_log_trigger AFTER DELETE ON rfuser.io_log_detail/*||名称：核心表删除事件审计触发器||说明：*/ FOR EACH ROWBEGIN INSERT INTO record_log SELECT :old.ERR_DESC, USER, SYSDATE, sys_context('userenv', 'ip_address'), q.sql_text, :OLD.START_TIME FROM v$sql q, v$session s WHERE s.audsid = (SELECT userenv('SESSIONID') FROM dual) AND s.sql_id = q.sql_id;END delete_log_trigger;

---

觉得有用的朋友多帮忙转发哦！后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~