PLSQL_Oracle Logon Trigger的建立

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

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