Oracle 登录触发器

来源：这里教程网时间：2026-03-01 18:10:29 作者：

作者 | 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 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%';

[object Object]

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 immediatestartup

6、使用触发器记录 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')));

[object Object]

使用其他用户测试，然后修改系统时间为 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                 6

[object Object]

6.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,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';

[object Object]

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;

[object Object]

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_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>';

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 ————————————————————————————