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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/35489/viewspace-711607/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/35489/viewspace-711607/