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

[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 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')));

[object Object]

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

[object Object]

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;

[object Object]

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

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 用户

ITPub博客

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.11、如何在 Oracle 中重新编译触发器

Oracle 表碎片检查及整理方案

2021 年公众号历史文章合集整理

2020 年公众号历史文章合集整理

我的 2021 年终总结和 2022 展望

Oracle 查询表空间使用率超慢问题一则

国产数据库|TiDB 5.4 单机快速安装初体验

Oracle ADG 备库停启维护流程及增量恢复

Oracle 19c 使用数据泵如何导入导出 PDB 用户

最新文章

ITPub博客

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.11、如何在 Oracle 中重新编译触发器

Oracle 表碎片检查及整理方案

2021 年公众号历史文章合集整理

2020 年公众号历史文章合集整理

我的 2021 年终总结和 2022 展望

Oracle 查询表空间使用率超慢问题一则

国产数据库|TiDB 5.4 单机快速安装初体验

Oracle ADG 备库停启维护流程及增量恢复

Oracle 19c 使用数据泵如何导入导出 PDB 用户

最新文章

6.2、分区表实现