用戶登錄oracle管控TRIGGER-优快云博客

CREATE OR REPLACE TRIGGER TR_LOGON
AFTER LOGON ON DATABASE
DECLARE
vip      VARCHAR2(30);
vuser    varchar2(30);
vhost    varchar2(50);
vosuser varchar2(50);
vprogram varchar2(50);
BEGIN
   select sys_context('userenv','session_user') into vuser from dual;
   SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') INTO vip FROM DUAL;
   SELECT SYS_CONTEXT('USERENV','HOST') INTO vhost FROM DUAL;
   SELECT SYS_CONTEXT('USERENV','OS_USER') INTO vosuser FROM DUAL;
   SELECT SYS_CONTEXT('USERENV','MODULE') INTO vprogram FROM DUAL;

if vuser not in ('system') then
sys.dbms_system.ksdwrt(2,to_char(sysdate,'yyyymmdd hh24:mi:ss')||'ip:'||vip||'want logon'||vuser); //非system用戶登錄的信息寫入alert_orcl.log

RAISE_APPLICATION_ERROR (num =>-20001,msg => '只有SYSTEM用戶才可以登錄'); //提示錯誤信息給客戶端用戶。
end if;
END;

以上內容請在sys用戶下執行，可以添加判斷達到對登錄用戶的IP、用戶、主機名進行判斷限定作業。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16381228/viewspace-710425/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16381228/viewspace-710425/