Oracle 登入觸發器

CREATE OR REPLACE TRIGGER SYS.CONTROL_LOGON
AFTER LOGON
ON DATABASE
DECLARE
UIP   VARCHAR2(300);
PRGRM  VARCHAR2(60);
CUSER VARCHAR2(60);
MO      VARCHAR2(60);
/******************************************************************************
this trigger is to monitor the control of logon
******************************************************************************/
BEGIN
   SELECT SYS_CONTEXT('USERENV','IP_ADDRESS'),USERNAME,PROGRAM,MODULE INTO UIP,CUSER,PRGRM,MO FROM V$SESSION WHERE AUDSID=USERENV('sessionid') and rownum=1;
   IF CUSER IN ('EXPENSECTL') AND INSTR(UPPER(PRGRM),'TOAD')>0 AND UIP IN ('10.161.32.1','10.130.2.144','10.130.2.121','10.130.2.241','10.244.154.47','10.130.2.79','10.164.68.63','10.164.68.65','10.134.130.131','10.161.32.65','10.134.130.117') THEN
      NULL;
   ELSIF CUSER IN ('EXPENSECTL') AND INSTR(UPPER(PRGRM),'TOAD')>0  THEN
      INSERT INTO sys.C_LOGIN_LOG_T(SELECT USERNAME,COMMAND,SCHEMANAME,PROGRAM,TERMINAL,SYS_CONTEXT('USERENV','IP_ADDRESS') IP,SYSDATE FROM V$SESSION WHERE AUDSID=USERENV('SESSIONID'));
      COMMIT;
      RAISE_APPLICATION_ERROR(-20001,'IP登入管控！');
   END IF;
END ;
/

 

 

select
　　SYS_CONTEXT('USERENV','TERMINAL') terminal,
　　SYS_CONTEXT('USERENV','LANGUAGE') language,
　　SYS_CONTEXT('USERENV','SESSIONID') sessionid,
　　SYS_CONTEXT('USERENV','INSTANCE') instance,
　　SYS_CONTEXT('USERENV','ENTRYID') entryid,
　　SYS_CONTEXT('USERENV','ISDBA') isdba,
　　SYS_CONTEXT('USERENV','NLS_TERRITORY') nls_territory,
　　SYS_CONTEXT('USERENV','NLS_CURRENCY') nls_currency,
　　SYS_CONTEXT('USERENV','NLS_CALENDAR') nls_calendar,
　　SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') nls_date_format,
　　SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') nls_date_language,
　　SYS_CONTEXT('USERENV','NLS_SORT') nls_sort,
　　SYS_CONTEXT('USERENV','CURRENT_USER') current_user,
　　SYS_CONTEXT('USERENV','CURRENT_USERID') current_userid,
　　SYS_CONTEXT('USERENV','SESSION_USER') session_user,
　　SYS_CONTEXT('USERENV','SESSION_USERID') session_userid,
　　SYS_CONTEXT('USERENV','PROXY_USER') proxy_user,
　　SYS_CONTEXT('USERENV','PROXY_USERID') proxy_userid,
　　SYS_CONTEXT('USERENV','DB_DOMAIN') db_domain,
　　SYS_CONTEXT('USERENV','DB_NAME') db_name,
　　SYS_CONTEXT('USERENV','HOST') host,
　　SYS_CONTEXT('USERENV','OS_USER') os_user,
　　SYS_CONTEXT('USERENV','EXTERNAL_NAME') external_name,
　　SYS_CONTEXT('USERENV','IP_ADDRESS') ip_address,
　　SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') network_protocol,　　SYS_CONTEXT('USERENV','BG_JOB_ID') bg_job_id,
　　SYS_CONTEXT('USERENV','FG_JOB_ID') fg_job_id,
　　SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE') authentication_type,
　　SYS_CONTEXT('USERENV','AUTHENTICATION_DATA') authentication_data
　　from dual ;

 

 

 

CREATE OR REPLACE TRIGGER SYS.CONTROL_LOGON
AFTER LOGON  ON   DATABASE
DECLARE
PRGRM    VARCHAR2(60);
MO          VARCHAR2(60);

BEGIN
   SELECT    PROGRAM,   MODULE   INTO   PRGRM,   MO   FROM   V$SESSION   WHERE    AUDSID=USERENV('sessionid')   and  rownum=1; 

   IF  PRGRM  IN ('scanbarcode.exe', 'PALLET.exe')   THEN
           EXECUTE IMMEDIATE 'ALTER SESSION SET CURSOR_SHARING=EXACT'  ; 
   ELSIF 
           NULL ;  
   RAISE_APPLICATION_ERROR(-20001,  '程式登入管控！');
   END  IF;
END ;
/

 

 

CREATE OR REPLACE TRIGGER SYS.CURSOR_SHARING AFTER LOGON ON DATABASE
DECLARE
UIP   VARCHAR2(300);
PRGRM  VARCHAR2(60);
CUSER VARCHAR2(60);
MO      VARCHAR2(60);
BEGIN
    SELECT SYS_CONTEXT('USERENV','IP_ADDRESS'),USERNAME,PROGRAM,MODULE INTO UIP,CUSER,PRGRM,MO FROM V$SESSION WHERE AUDSID=USERENV('sessionid') and rownum=1;
    IF INSTR(UPPER(PRGRM),'TOAD.EXE')>0 THEN
        EXECUTE IMMEDIATE 'ALTER SESSION SET CURSOR_SHARING=FORCE';
    END IF;
END;

 

---------------------------------------------------------------------------------------  

 

不過版本不一樣,   執行結果也有不一樣,  研究中 。 

 

9i 中執行如下SQL
SELECT SYS_CONTEXT('USERENV','IP_ADDRESS'),USERNAME,PROGRAM,MODULE   
FROM V$SESSION  WHERE AUDSID=USERENV('sessionid')   
得到結果把後臺程式及其他同仁登入的程式也抓出來了：   


SYS_CONTEXT('USERENV','IP_ADDRESS'),USERNAME,PROGRAM,MODULE
10.161.32.120,,oracle@imbfatp (PMON),
10.161.32.120,,oracle@imbfatp (DBW0),
10.161.32.120,,oracle@imbfatp (LGWR),
10.161.32.120,,oracle@imbfatp (CKPT),
10.161.32.120,,oracle@imbfatp (SMON),
10.161.32.120,,oracle@imbfatp (RECO),
10.161.32.120,,oracle@imbfatp (CJQ0),
10.161.32.120,,oracle@imbfatp (ARC0),
10.161.32.120,,oracle@imbfatp (ARC1),
10.161.32.120,SYS,toad.exe,TOAD 9.7.2.5
10.161.32.120,SYS,toad.exe,TOAD 9.7.2.5
10.161.32.120,SYS,toad.exe,TOAD 9.7.0.51
10.161.32.120,SYS,Toad.exe,Toad.exe



在10g 中執行同樣的語句， 只有一個結果 。
SYS_CONTEXT('USERENV','IP_ADDRESS'),USERNAME,PROGRAM,MODULE
10.161.32.120,SYS,toad.exe,TOAD 9.7.0.51