作者 | 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_control
AFTER logon on database
declare
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 10查看触发器set lines 200
column owner format a10
column TRIGGER_NAME format a18
column TRIGGERING_EVENT format a15
column TRIGGER_TYPE format a15
column STATUS format a15
select 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_restrict
AFTER LOGON ON DATABASE
BEGIN
FOR x IN (SELECT username, program
FROM SYS.v_$session
WHERE audsid = USERENV (‘sessionid’))
LOOP
IF LTRIM (RTRIM (x.username)) = ‘TEST’
AND LTRIM (RTRIM (x.program)) IN (‘sqlplusw.exe’,‘TOAD.exe’)
THEN
raise_application_error
(-20999,‘Not authorized to use in the Production
environment!’);
END IF;
END LOOP;
END program_restrict
/3、可以用来停止特定主机名和操作系统用户的登录 Oracle 登录触发器
CREATE OR REPLACE TRIGGER SYSTEM.LOGON_DENY
AFTER LOGON
ON DATABASE
declare
OSUSER varchar2 (200);
HOSTNAME varchar2 (200);
begin
select 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')
then
raise_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_trace
AFTER LOGON ON DATABASE
WHEN (USER like '&USE')
DECLARE
lcommand varchar(200);
BEGIN
EXECUTE 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 immediate
startup6、使用触发器记录 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 DATABASE
BEGIN
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 DATABASE
BEGIN
-- ***************************************************
-- 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 a30
select 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 a30
20:13:54 SYS@testogg> select partition_name from DBA_TAB_PARTITIONS where table_owner='SYS' and TABLE_NAME='STATS$USER_LOG';
PARTITION_NAME
------------------------------
P_YEAR2022
SYS_P2140
Elapsed: 00:00:00.01
20:14:00 SYS@testogg> select user_id, host, last_program, logon_day, logon_time, elapsed_minutes
20: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 1
PROD_C O19cOGG sqlplus@O19cOGG (TNS V1-V3) 2022-04-08 20:06:15 20:06:15 0
Elapsed: 00:00:00.01
20:14:19 SYS@testogg> select user_id, host, last_program, logon_day, logon_time, elapsed_minutes
20: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 240
GX_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 960
SYS 24-OCT-21
GX_ADMIN 25-OCT-21 2891
GX_WEBUSER 25-OCT-21
SYS 25-OCT-21
GX_WEBUSER 26-OCT-21
GX_ADMIN 26-OCT-21 2880
SYS 26-OCT-21
GX_WEBUSER 27-OCT-21
GX_ADMIN 27-OCT-21 2640
GX_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 2
24-OCT-21 16 3
24-OCT-21 20 2
24-OCT-21 22 2
24-OCT-21 23 1
25-OCT-21 00 2
25-OCT-21 03 104
25-OCT-21 04 2
25-OCT-21 06 2
25-OCT-21 10 2
25-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 EVALUATIONS
DECLARE
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,status
FROM dba_triggers
WHERE table_name = upper ('&table_name')
or trigger_name = upper ('&trigger_name');
select table_name, trigger_name,status
FROM user_triggers
WHERE table_name = upper ('&table_name');
or
WHERE 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_body
FROM dba_triggers
WHERE table_name = upper ('&table_name');
or
WHERE trigger_name = upper ('&trigger_name');
Set long 20000 pages 1000;
SELECT table_name, trigger_name, trigger_body
FROM user_triggers
WHERE table_name = upper ('&table_name');
or
WHERE 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,status
FROM dba_triggers
WHERE table_name = upper ('&table_name');
--用户级别
select table_name, trigger_name,status
FROM user_triggers
WHERE 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,status
FROM user_triggers
WHERE status='DISABLED';
col TRIGGER_NAME for a30
col TABLE_OWNER for a15
col TABLE_NAME for a10
select 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_name
from dba_objects
where object_id =
( select PLSQL_ENTRY_OBJECT_ID
from v$session
where 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>';8.12、如何在 Oracle 中重命名触发器ALTER TRIGGER <trigger name> RENAME TO <new trigger name>;9、参考链接：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
————————————————————————————
Oracle 表碎片检查及整理方案
2021 年公众号历史文章合集整理
2020 年公众号历史文章合集整理
我的 2021 年终总结和 2022 展望
Oracle 查询表空间使用率超慢问题一则
国产数据库|TiDB 5.4 单机快速安装初体验
Oracle ADG 备库停启维护流程及增量恢复
Oracle 19c 使用数据泵如何导入导出 PDB 用户
608
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
