Menu Close

Oracle 登录触发器

lcc0556

以下内容均需要在sys用户下执行。

1. 限制用户登录

1.1. 需求与场景

因为安全因素，现需要限定 prod_owner 中的四个用户只能通过下面列表中的 IP 才能访问，其他 IP 则是无法访问的，而其他用户也不受限制。

1.2. 触发器语句

create or replace trigger tr_logon_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;

2. 记录用户登陆与登出的信息

2.1. 需求与场景

当用户登录或登出时需要记录其登录或登出的时间，当用户登录时还要记录其IP地址。

2.2. 触发器语句

-- 创建表用于存储登陆或登出的统计信息
CREATE TABLE stats$user_log
(
   user_id           VARCHAR2 (30),
   session_id        NUMBER (8),
   HOST              VARCHAR2 (30),
   IPAddress         VARCHAR2 (20),
   last_program      VARCHAR2 (48),
   last_action       VARCHAR2 (32),
   last_module       VARCHAR2 (32),
   logon_time        DATE,
   logoff_time       DATE,
   elapsed_minutes   NUMBER (8),
   update_time       DATE
);

-- 创建登陆之后的触发器
CREATE OR REPLACE TRIGGER tr_logon_audit
   AFTER LOGON
   ON DATABASE
BEGIN
   INSERT INTO stats$user_log
        VALUES (USER,
                SYS_CONTEXT ('USERENV', 'SESSIONID'),
                SYS_CONTEXT ('USERENV', 'HOST'),
                SYS_CONTEXT ('USERENV', 'IP_ADDRESS'),
                NULL,
                NULL,
                NULL,
                SYSDATE,
                NULL,
                NULL,
                SYSDATE);
END;
/

--创建登出之后的触发器
CREATE OR REPLACE TRIGGER tr_logoff_audit
   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_time = SYSDATE,
          update_time = SYSDATE
    WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = session_id;

   -- ***************************************************
   -- Update the logoff time
   -- ***************************************************
   UPDATE stats$user_log
      SET logoff_time = SYSDATE
    WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = session_id;

   -- ***************************************************
   -- Compute the elapsed minutes
   -- ***************************************************
   UPDATE stats$user_log
      SET elapsed_minutes = ROUND ( (logoff_time - logon_time) * 24 * 60)
    WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = session_id;
END;
/

用户登录与登出的信息示例：

file

3. 跟踪特定用户(会话)的活动

3.1. 需求与场景

新创建的会话(可限定某个用户)需要跟踪其执行的SQL等日志。

3.2. 触发器语句

CREATE OR REPLACE TRIGGER tr_logon_trace
AFTER logon on DATABASE
BEGIN
  IF(SYS_CONTEXT('USERENV','SESSION_USER') = 'EASYTONG') THEN
    dbms_application_info.set_client_info(sys_context('userenv','ip_address'));
    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 4''';
  END IF;

END;
/

4. 登录失败的时候记录日志信息

4.1. 需求与场景

通过查看alert日志来获取登录失败的用户信息。

4.2. 触发器语句

CREATE OR REPLACE TRIGGER logon_denied_alert
  AFTER servererror ON DATABASE
DECLARE
  message   VARCHAR2(168);
  ip        VARCHAR2(15);
  v_os_user VARCHAR2(80);
  v_module  VARCHAR2(50);
  v_action  VARCHAR2(50);
  v_pid     VARCHAR2(10);
  v_sid     NUMBER;
  v_program VARCHAR2(48);
BEGIN
  IF (ora_is_servererror(1017)) THEN

    -- get ip FOR remote connections :
    IF upper(sys_context('userenv', 'network_protocol')) = 'TCP' THEN
      ip := sys_context('userenv', 'ip_address');
    END IF;

    SELECT sid INTO v_sid FROM sys.v_$mystat WHERE rownum < 2;
    SELECT p.spid, v.program
      INTO v_pid, v_program
      FROM v$process p, v$session v
     WHERE p.addr = v.paddr
       AND v.sid = v_sid;

    v_os_user := sys_context('userenv', 'os_user');
    dbms_application_info.read_module(v_module, v_action);

    message := to_char(SYSDATE, 'YYYYMMDD HH24MISS') ||
               ' logon denied from ' || nvl(ip, 'localhost') || ' ' ||
               v_pid || ' ' || v_os_user || ' with ' || v_program || ' – ' ||
               v_module || ' ' || v_action;

    sys.dbms_system.ksdwrt(2, message);

  END IF;
END;
/

用户登录失败后，在trace文件中提示如下：

file

附录

附录A. 相关联的文章

附录C. 其他知识点

  • 查看数据库中某个触发器的内容
SET LINES 300 PAGES 999
column owner format a10
column TRIGGER_NAME format a30
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 '%TR_LOG%';

SELECT *
  FROM dba_triggers
 WHERE triggering_event like '%TR_LOG%';
  • 查看用户的登入登出信息
SET LINES 300 PAGES 999
COL user_id FORMAT A12
COL last_program FORMAT A30
COL last_action FORMAT A30
COL last_module FORMAT A20
ALTER SESSION SET NLS_DATE_FORMAT = 'yyyy-MM-dd hh24:mi:ss'

SELECT a.user_id,
       a.session_id,
       a.host,
       a.ipaddress,
       a.last_program,
       a.last_action,
       a.last_module,
       to_char(a.logon_time,'yyyy-MM-dd hh24:mi:ss') AS logon_time,
       to_char(a.logoff_time,'yyyy-MM-dd hh24:mi:ss') AS logoff_time,
       a.elapsed_minutes,
       to_char(a.update_time,'yyyy-MM-dd hh24:mi:ss') AS update_time
  FROM sys.stats$user_log a
 WHERE TRUNC(a.update_time) = TRUNC(SYSDATE)
 ORDER BY a.update_time;
Post Views: 286
Tagged ,