限制机器登录oralce

CREATE OR REPLACE TRIGGER trg_work_log
AFTER LOGON ON DATABASE
declare
v_program_name varchar2(200);
v_username varchar2(100);
v_ip varchar2(18);
BEGIN
select username,program,SYS_CONTEXT('USERENV','IP_ADDRESS')
into v_username,v_program_name,v_ip
from sys.v_$session where AUDSID = SYS_CONTEXT('USERENV', 'SESSIONID');
if (upper(v_username)='SCOTT') then
  if (UPPER(v_program_name) = 'SQLPLUS.EXE' OR  UPPER(v_program_name) ='PLSQLDEV.EXE') then
     if (v_ip = ('10.76.5.1369')) then
       RAISE_APPLICATION_ERROR(-20001,v_ip||'不能登陸數據庫');

    end if;
  ELSE
    RAISE_APPLICATION_ERROR(-20001,'不能用plsql登陸');  end if;
else
   RAISE_APPLICATION_ERROR(-20001,'不能用plsql登陸');

end if;
END;

 

CREATE OR REPLACE TRIGGER Work_Log_OFF
AFTER LOGON ON DATABASE
declare

v_username varchar2(100);
v_ip varchar2(18);
BEGIN
select username,SYS_CONTEXT('USERENV','IP_ADDRESS')
   into v_username,v_ip
from sys.v_$session where AUDSID = SYS_CONTEXT('USERENV', 'SESSIONID');
     if (v_ip = ('10.76.5.13223')) then
       RAISE_APPLICATION_ERROR(-20001,v_ip||'no found database');
    end if;
END;

CREATE OR REPLACE TRIGGER 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 logon control
******************************************************************************/
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  UIP IN ('10.76.5.60','10.76.5.175','10.76.5.135','10.76.5.239','10.185.128.80','10.76.5.239','10.76.4.138','10.76.6.168','10.185.130.41','10.185.136.80','10.76.188.111','10.76.5.5') THEN
     -- NULL;
/* IF CUSER IN ('EXPENSECTL') AND INSTR(UPPER(PRGRM),'TOAD')>0 AND UIP IN ('10.76.5.60') 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,'祅魁恨北??');
   END IF;
END ;

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

转载于:http://blog.itpub.net/10201716/viewspace-707950/