PLSQL_Oracle Logon Trigger的建立

20150609 Created By BaoXinjian

一、摘要

---

oracle logon trigger一般用来审计用户登录信息或者限制用户登录，虽说不常用，但仍不失为一种好办法。

 

1. 不能审计dba用户登录

2. 什么时候适合使用

It is advised you use this trigger only when

(1) not using archive logging on the database or

非归档模式

(2) there are few logons to the database.

登录次数少

 

二、锁定统计信息

---

Step1. 创建审计表

CREATE TABLE gavin.bxj_logonlog(
  os_user varchar2(30),
  user_name varchar2(30),
  logon_time date,
  session_user varchar2(30),
  ip_address varchar2(15),
  program varchar2(30)
);

Step2.  创建Logon Trigger

CREATE OR REPLACE TRIGGER gavin.bxj_on_logon
  AFTER logon ON DATABASE
DECLARE
  user_name varchar2(30);
  os_user      varchar2(30);
  v_sid        number;
  v_su         varchar2(15);
  v_program    varchar2(30);
  v_ip         varchar2(15);
BEGIN
  EXECUTE IMMEDIATE 'select distinct sid from sys.v_$mystat'
    INTO v_sid;

  EXECUTE IMMEDIATE 'select osuser, username, program from sys.v_$session where sid = :b1'
    INTO os_user, user_name, v_program
    USING v_sid;
    
  SELECT sys_context('userenv', 'SESSION_USER') INTO v_su from dual;
  SELECT sys_context('userenv', 'IP_ADDRESS') INTO v_ip from dual;
  
  INSERT INTO gavin.bxj_logonlog
  VALUES
    (os_user, user_name, sysdate, v_su, v_ip, v_program);
    
  --禁止WWW账号登陆
  IF (user_name = 'WWW') THEN
    DBMS_SESSION.SET_IDENTIFIER('about to raise app_error..');
    RAISE_APPLICATION_ERROR(-20003, 'You are not allowed to connect to the database');
  END IF;
  
  --启用GAVINTEST账号的所有操作的trace记录
  IF user = 'GAVINTEST' THEN
    EXECUTE IMMEDIATE 'alter session set statistics_level=ALL';
    EXECUTE IMMEDIATE 'alter session set max_dump_file_size=UNLIMITED';
    EXECUTE IMMEDIATE 'alter session set tracefile_identifier='''||user||'_10046''';
    EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12''';
  END IF;   
END;
/

Step3. 使用普通账号登陆，用以测试查看BXJ_LOGONLOG审计表记录

 

Step4. 使用WWW账号登陆，用以测试是否允许登录

 

Step5. 使用GAVINTEST账号登陆，用以而是是否产生trace file

 

Thanks and Regards

参考：Matelink - How To Create A Trigger To Capture User Information On Logon [ID 454088.1]