因安全需要,对于特定的 IP 才能够访问业务用户。那么就需要限制 IP 访问数据库,但是防火墙和 Oracle sqlnet.ora 文件均是对 IP 的限制,这样就会拒绝一部分 IP 无法访问数据库,更不用说访问特定用户了。所以,就需要触发器来实现了。
如下,列表中的 IP 做限制只能访问 prod_owner 中的四个用户,也就是说这四个用户只能通过下面列表中的 IP 才能访问,其他 IP 则是无法访问的,而其他用户也不受限制,可对数据库进行读写或者创建只读账号进行查询操作。
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%';
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
/
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;
/
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;
/
当_SYSTEM_TRIG_ENABLED设置为TRUE(默认值)时,系统触发器被启用。 因此,如果将该参数设置为false并重启数据库,则登录触发器或系统触发器将不会触发。
alter system set "_SYSTEM_TRIG_ENABLED" = false scope=spfile;
shutdown immediate
startup
--创建表用于存储登陆或登出的统计信息
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;
/
评论有人说建分区表比较好,对于用户量大、登录频繁的系统会变慢有瓶颈,这里测试环境选择建立以年为单位的自动分区表进行测试。
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 6
--查看用户的登入登出信息
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
.............
如下展示了如何使用 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;
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');
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';
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','
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');
ALTER TRIGGER
我们可以在触发器中添加一个sleep语句一分钟左右,然后在另一个会话中检查v$session中SID的“plsql_entry_object_id”值,看看dba_objects中的object_id是否与你的触发器名称相对应。 如果它报告带有错误(或类似的东西)的Trigger编译,只需输入 SHOW errors 获取更多信息。 也可以使用下面的查询来检查状态 。 https://techgoeasy.com/oracle-logon-trigger/ ————————————————————————————ALTER TABLE
DISABLE ALL TRIGGERS;
8.7、如何在Oracle中启用触发器
ALTER TRIGGER
8.8、如何启用一个表上的所有触发器
ALTER TABLE
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 中被触发
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
select object_name ,status from dba_objects where object_name='
select object_name ,status from user_objects where object_name='8.12、如何在 Oracle 中重命名触发器
ALTER TRIGGER
9、参考链接:
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
————————————————————————————
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有