其他
Oracle 登录触发器
作者 | JiekeXu
来源 |公众号 JiekeXu DBA之路（ID: JiekeXu_IT）
大家好，我是JiekeXu,很高兴又和大家见面了,今天和大家一起来聊聊 Oracle 登录触发器，欢迎点击上方蓝字关注我，标星或置顶，更多干货第一时间到达！
目 录
1、限制登录触发器
2、可用于停止特定程序和用户的登录 Oracle 登录触发器
3、可以用来停止特定主机名和操作系统用户的登录 Oracle 登录触发器
4、可用于跟踪特定用户的活动 Oracle 登录触发器
5、如何禁用 Oracle 登录触发器或系统触发器
6、使用触发器记录 Oracle 用户登陆信息
6.1、实现代码
6.2、 分区表实现
6.3、普通表结果示例
7、触发器官网示例
8、触发器常用操作
8.1、如何在Oracle中查看触发器状态
8.2、如何检查 Oracle 中是否存在触发器
8.3、如何在 Oracle 中检查触发器定义
8.4、如何在表中找到所有触发器
8.5、如何在 Oracle 中禁用触发器
8.6、如何禁用一个表上的所有触发器
8.7、如何在Oracle中启用触发器
8.8、如何启用一个表上的所有触发器
8.9、如何列出所有禁用的触发器
8.10、如何检查触发器是否在 oracle 中被触发
8.11、如何在 Oracle 中重新编译触发器
8.12、如何在 Oracle 中重命名触发器
9、参考链接
因安全需要，对于特定的 IP 才能够访问业务用户。那么就需要限制 IP 访问数据库，但是防火墙和 Oracle sqlnet.ora 文件均是对 IP 的限制，这样就会拒绝一部分 IP 无法访问数据库，更不用说访问特定用户了。所以，就需要触发器来实现了。
如下，列表中的 IP 做限制只能访问 prod_owner 中的四个用户，也就是说这四个用户只能通过下面列表中的 IP 才能访问，其他 IP 则是无法访问的，而其他用户也不受限制，可对数据库进行读写或者创建只读账号进行查询操作。
1、限制登录触发器
create or replace trigger logon_ip_controlAFTER logon on databasedeclare ip STRING(30); prod_owner STRING(30);BEGIN SELECT SYS_CONTEXT('USERENV','SESSION_USER') into prod_owner from dual; SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') into ip from dual; if prod_owner='PROD_C' or prod_owner='PROD_S' or prod_owner='PROD_M' or prod_owner='SCOTT' THEN IF ip not in ('192.168.14.201','192.168.17.30', '192.168.16.27') THEN raise_application_error(-20001,'User '||prod_owner||' is not allowed to connect from '||ip); END IF; END IF;end;如下错误 ORA-20001 不允许 IP 为 192.168.14.40 的地址连接 PROD_C 用户进行操作数据库。
The specified database user/password combination is rejected: [60000][604] ORA-00604: 递归 SQL 级别 1 出现错误 ORA-20001: User PROD_C is not allowed to connect from 192.168.14.40 ORA-06512: 在 line 10set lines 200column owner format a10column TRIGGER_NAME format a18column TRIGGERING_EVENT format a15column TRIGGER_TYPE format a15column STATUS format a15select owner, TRIGGER_NAME,TRIGGERING_EVENT,TRIGGER_TYPE,STATUS from dba_triggers where triggering_event like '%LOGON%';
select * from dba_triggers where triggering_event like '%LOGON%';2、可用于停止特定程序和用户的登录 Oracle 登录触发器
CREATE OR REPLACE TRIGGER program_restrictAFTER LOGON ON DATABASEBEGINFOR x IN (SELECT username, programFROM SYS.v_$sessionWHERE audsid = USERENV (‘sessionid’))LOOPIF LTRIM (RTRIM (x.username)) = ‘TEST’AND LTRIM (RTRIM (x.program)) IN (‘sqlplusw.exe’,‘TOAD.exe’)THENraise_application_error(-20999,‘Not authorized to use in the Productionenvironment!’);END IF;END LOOP;END program_restrict/3、可以用来停止特定主机名和操作系统用户的登录 Oracle 登录触发器
CREATE OR REPLACE TRIGGER SYSTEM.LOGON_DENYAFTER LOGONON DATABASEdeclareOSUSER varchar2 (200);HOSTNAME varchar2 (200);beginselect sys_context ('USERENV', 'OS_USER') into OSUSER from dual;select sys_context ('USERENV', 'HOST') into HOSTNAME from dual;if sys_context('USERENV','SESSION_USER')in ('HR','SCOTT','TECH')and sys_context ('USERENV', 'HOST') in ('TECH_USER1','TECH_USER2')thenraise_application_error(-20001,'Denied! You are not allowed to logon from host '||HOSTNAME|| ' using '|| OSUSER);end if;end;/4、可用于跟踪特定用户的活动 Oracle 登录触发器
CREATE OR REPLACE TRIGGER set_traceAFTER LOGON ON DATABASEWHEN (USER like '&USE')DECLARElcommand varchar(200);BEGINEXECUTE IMMEDIATE 'alter session set tracefile_identifier=''From_Trigger''';EXECUTE IMMEDIATE 'alter session set statistics_level=ALL';EXECUTE IMMEDIATE 'alter session set max_dump_file_size=UNLIMITED';EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12''';END set_trace;/5、如何禁用 Oracle 登录触发器或系统触发器
当 _SYSTEM_TRIG_ENABLED 设置为 TRUE (默认值)时，系统触发器被启用。因此，如果将该参数设置为 false 并重启数据库，则登录触发器或系统触发器将不会触发。
alter system set "_SYSTEM_TRIG_ENABLED" = false scope=spfile;shutdown immediatestartup6、使用触发器记录 Oracle 用户登陆信息
6.1、实现代码
--创建表用于存储登陆或登出的统计信息CREATE TABLE stats$user_log( user_id VARCHAR2 (30), session_id NUMBER (8), HOST VARCHAR2 (30), last_program VARCHAR2 (48), last_action VARCHAR2 (32), last_module VARCHAR2 (32), logon_day DATE, logon_time VARCHAR2 (10), logoff_day DATE, logoff_time VARCHAR2 (10), elapsed_minutes NUMBER (8));
--创建登陆之后的触发器CREATE OR REPLACE TRIGGER logon_audit_trigger AFTER LOGON ON DATABASEBEGIN INSERT INTO stats$user_log VALUES (USER, SYS_CONTEXT ('USERENV', 'SESSIONID'), SYS_CONTEXT ('USERENV', 'HOST'), NULL, NULL, NULL, SYSDATE, TO_CHAR (SYSDATE, 'hh24:mi:ss'), NULL, NULL, NULL);END;/
--创建登出之后的触发器CREATE OR REPLACE TRIGGER logoff_audit_trigger BEFORE LOGOFF ON DATABASEBEGIN -- *************************************************** -- Update the last action accessed -- *************************************************** UPDATE stats$user_log SET last_action = (SELECT action FROM v$session WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = audsid) WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = session_id;
--*************************************************** -- Update the last program accessed -- *************************************************** UPDATE stats$user_log SET last_program = (SELECT program FROM v$session WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = audsid) WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = session_id;
-- *************************************************** -- Update the last module accessed -- *************************************************** UPDATE stats$user_log SET last_module = (SELECT module FROM v$session WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = audsid) WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = session_id;
-- *************************************************** -- Update the logoff day -- *************************************************** UPDATE stats$user_log SET logoff_day = SYSDATE WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = session_id;
-- *************************************************** -- Update the logoff time -- *************************************************** UPDATE stats$user_log SET logoff_time = TO_CHAR (SYSDATE, 'hh24:mi:ss') WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = session_id;
-- *************************************************** -- Compute the elapsed minutes -- *************************************************** UPDATE stats$user_log SET elapsed_minutes = ROUND ( (logoff_day - logon_day) * 1440) WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = session_id;END;/6.2、 分区表实现
有人说建分区表比较好，对于用户量大、登录频繁的系统会变慢有瓶颈，这里测试环境选择建立以年为单位的自动分区表进行测试，大家可根据自己的情况设置季度分区表或月分区表。
CREATE TABLE sys.stats$user_log( user_id VARCHAR2 (30), session_id NUMBER (8), HOST VARCHAR2 (30), last_program VARCHAR2 (48), last_action VARCHAR2 (32), last_module VARCHAR2 (32), logon_day DATE, logon_time VARCHAR2 (10), logoff_day DATE, logoff_time VARCHAR2 (10), elapsed_minutes NUMBER (8)) PARTITION BY RANGE(logon_day) interval (numtoyMinterval (1,'YEAR')) (PARTITION P_YEAR2022 VALUES LESS THAN (to_date('2022-12-31','yyyy-mm-dd')));使用其他用户测试，然后修改系统时间为 2023 年然后再次测试，表已经自动创建分区，并插入一条数据。
--修改系统时间# date -s '2023-04-08 20:06:51'--查看系统自动生成的分区名称col PARTITION_NAME for a30select partition_name from DBA_TAB_PARTITIONS where table_owner='SYS' and TABLE_NAME='STATS$USER_LOG';--然后根据分区名查询数据select user_id, host, last_program, logon_day, logon_time, elapsed_minutes from sys.stats$user_log partition(P_YEAR2022);
select user_id, host, last_program, logon_day, logon_time, elapsed_minutes from sys.stats$user_log partition(SYS_P2140);
0:13:36 SYS@testogg> col PARTITION_NAME for a3020:13:54 SYS@testogg> select partition_name from DBA_TAB_PARTITIONS where table_owner='SYS' and TABLE_NAME='STATS$USER_LOG';
PARTITION_NAME------------------------------P_YEAR2022SYS_P2140
Elapsed: 00:00:00.0120:14:00 SYS@testogg> select user_id, host, last_program, logon_day, logon_time, elapsed_minutes20:14:19 2 from sys.stats$user_log partition(P_YEAR2022);
USER_ID HOST LAST_PROGRAM LOGON_DAY LOGON_TIME ELAPSED_MINUTES------------------------------ ------------------------------ ------------------------------------------------ ------------------- ---------- ---------------PROD_C O19cOGG sqlplus@O19cOGG (TNS V1-V3) 2022-04-08 20:05:10 20:05:10 1PROD_C O19cOGG sqlplus@O19cOGG (TNS V1-V3) 2022-04-08 20:06:15 20:06:15 0
Elapsed: 00:00:00.0120:14:19 SYS@testogg> select user_id, host, last_program, logon_day, logon_time, elapsed_minutes20:14:34 2 from sys.stats$user_log partition(SYS_P2140);
USER_ID HOST LAST_PROGRAM LOGON_DAY LOGON_TIME ELAPSED_MINUTES------------------------------ ------------------------------ ------------------------------------------------ ------------------- ---------- ---------------PROD_C O19cOGG sqlplus@O19cOGG (TNS V1-V3) 2023-04-08 20:07:07 20:07:07 66.3、普通表结果示例
--查看用户的登入登出信息SQL> select * from sys.stats$user_log where rownum<3;
USER_ID SESSION_ID HOST LAST_PROGRAM LAST_MODULE LOGON_DAY LOGON_TIME LOGOFF_DA LOGOFF_TIM ELP_MINS---------- ---------- --------------- ---------------- ---------------- --------- ---------- --------- ---------- --------GX_ADMIN 5409517 v2021DB01u JDBC Thin Client JDBC Thin Client 24-OCT-21 12:20:30 24-OCT-13 16:20:30 240GX_ADMIN 5409518 v2021DB02U JDBC Thin Client JDBC Thin Client 24-OCT-21 12:22:23 24-OCT-13 16:22:30 240
--汇总用户登陆时间 SQL> SELECT user_id, TRUNC (logon_day) logon_day, SUM (elapsed_minutes) total_time FROM sys.stats$user_log GROUP BY user_id, TRUNC (logon_day) ORDER BY 2;
USER_ID LOGON_DAY TOTAL_TIME------------------------------ --------- ----------GX_ADMIN 24-OCT-21 960SYS 24-OCT-21GX_ADMIN 25-OCT-21 2891GX_WEBUSER 25-OCT-21SYS 25-OCT-21GX_WEBUSER 26-OCT-21GX_ADMIN 26-OCT-21 2880SYS 26-OCT-21GX_WEBUSER 27-OCT-21GX_ADMIN 27-OCT-21 2640GX_WEBUSER 28-OCT-21--基于日期时间段的用户登陆数SQL> select trunc (logon_day) logon_day,substr(logon_time,1,2) hour,count(user_id) as number_of_logins from sys.stats$user_log group by trunc (logon_day) ,substr(logon_time,1,2) order by 1,2;
LOGON_DAY HOUR NUMBER_OF_LOGINS--------- ------ ----------------24-OCT-21 12 224-OCT-21 16 324-OCT-21 20 224-OCT-21 22 224-OCT-21 23 125-OCT-21 00 225-OCT-21 03 10425-OCT-21 04 225-OCT-21 06 225-OCT-21 10 225-OCT-21 14 2 .............7、触发器官网示例
如下展示了如何使用 CREATE TRIGGER 语句来创建触发器 EVAL_CHANGE_TRIGGER，每当
INSERT、UPDATE 或 DELETE 语句更改了 EVALUATIONS_LOG 表的 EVALUATIONS_LOG
时，该触发器就会向表中添加一行。
触发器在触发语句执行之后添加该行，并使用条件谓词 INSERTING、UPDATING 和DELETING 来确定三个可能的 DML 语句中的哪一个触发了触发器。
EVAL_CHANGE_TRIGGER 是一个语句级触发器和一个 AFTER 触发器。
创建 EVALUATIONS_LOG 和 EVAL_CHANGE_TRIGGER:
--创建表CREATE TABLE EVALUATIONS_LOG ( log_date DATE, action VARCHAR2(50));
--创建触发器CREATE OR REPLACE TRIGGER EVAL_CHANGE_TRIGGER AFTER INSERT OR UPDATE OR DELETE ON EVALUATIONSDECLARE log_action EVALUATIONS_LOG.action%TYPE;BEGIN IF INSERTING THEN log_action := 'Insert'; ELSIF UPDATING THEN log_action := 'Update'; ELSIF DELETING THEN log_action := 'Delete'; ELSE DBMS_OUTPUT.PUT_LINE('This code is not reachable.'); END IF;
INSERT INTO EVALUATIONS_LOG (log_date, action) VALUES (SYSDATE, log_action);END;8、触发器常用操作
8.1、如何在Oracle中查看触发器状态
select table_name, trigger_name,statusFROM dba_triggersWHERE table_name = upper ('&table_name')or trigger_name = upper ('&trigger_name');select table_name, trigger_name,statusFROM user_triggersWHERE table_name = upper ('&table_name');orWHERE trigger_name = upper ('&trigger_name');8.2、如何检查 Oracle 中是否存在触发器
select table_name, trigger_name,status FROM dba_triggers WHERE trigger_name = upper ('&trigger_name');
select table_name, trigger_name,status FROM dba_triggers WHERE owner='PROD';8.3、如何在 Oracle 中检查触发器定义
Set long 20000 pages 1000;SELECT table_name, trigger_name, trigger_bodyFROM dba_triggersWHERE table_name = upper ('&table_name');orWHERE trigger_name = upper ('&trigger_name');
Set long 20000 pages 1000;SELECT table_name, trigger_name, trigger_bodyFROM user_triggersWHERE table_name = upper ('&table_name');orWHERE trigger_name = upper ('&trigger_name');
触发器定义也可以使用dbms_metadata获取，如下所示:set pagesize 0 set long 10000 SELECT DBMS_METADATA.GET_DDL('TRIGGER','<TRIGGER_NAME>','<OWNER') FROM dual;
ALTER TRIGGER "SYSTEM"."LOGON_IP_CONTROL" ENABLE;
8.4、如何在表中找到所有触发器
select table_name, trigger_name,statusFROM dba_triggersWHERE table_name = upper ('&table_name');
--用户级别select table_name, trigger_name,statusFROM user_triggersWHERE table_name = upper ('&table_name');8.5、如何在 Oracle 中禁用触发器
ALTER TRIGGER <trigger name> DISABLE;8.6、如何禁用一个表上的所有触发器
ALTER TABLE <table name> DISABLE ALL TRIGGERS;8.7、如何在 Oracle 中启用触发器
ALTER TRIGGER <trigger name> ENABLE;8.8、如何启用一个表上的所有触发器
ALTER TABLE <table name> enable ALL TRIGGERS;8.9、如何列出所有禁用的触发器
select table_name, trigger_name,statusFROM user_triggersWHERE status='DISABLED';
col TRIGGER_NAME for a30col TABLE_OWNER for a15col TABLE_NAME for a10select OWNER,TRIGGER_NAME,TRIGGER_TYPE,TABLE_OWNER,TABLE_NAME,STATUS from dba_triggers WHERE status='DISABLED';
--查看启用的触发器select OWNER,TRIGGER_NAME,TRIGGER_TYPE,TABLE_OWNER,TABLE_NAME,STATUS from dba_triggers WHERE status='ENABLED';8.10、如何检查触发器是否在 Oracle 中被触发
我们可以在触发器中添加一个 sleep 语句一分钟左右，然后在另一个会话中检查 v$session 中SID 的 “plsql_entry_object_id” 值，看看 dba_objects 中的 object_id 是否与你的触发器名称相对应。
select owner, object_namefrom dba_objectswhere object_id =( select PLSQL_ENTRY_OBJECT_IDfrom v$sessionwhere sid = &1 );8.11、如何在 Oracle 中重新编译触发器
alter trigger <trigger name> compile;如果它报告带有错误(或类似的东西)的Trigger编译，只需输入 SHOW errors 获取更多信息。
也可以使用下面的查询来检查状态 。
select object_name ,status from dba_objects where object_name='<trigger name>';
select object_name ,status from user_objects where object_name='<trigger name>';ALTER TRIGGER <trigger name> RENAME TO <new trigger name>;https://techgoeasy.com/oracle-logon-trigger/
http://www.dba-oracle.com/art_builder_sec_audit.htm
https://techgoeasy.com/how-to-check-trigger-status-in-oracle/
https://docs.oracle.com/en/database/oracle/oracle-database/19/tdddg/using-triggers.html#GUID-3744214A-861D-4C59-AD2D-95840B5B0871
全文完，希望可以帮到正在阅读的你，如果觉得有帮助，可以分享给你身边的朋友，你关心谁就分享给谁，一起学习共同进步~~~
❤️ 欢迎关注我的公众号，来一起玩耍吧！！！
————————————————————————————
公众号：JiekeXu DBA之路
墨天轮：https://www.modb.pro/u/4347
CSDN ：https://blog.csdn.net/JiekeXu
腾讯云：https://cloud.tencent.com/developer/user/5645107
————————————————————————————