Oracle 设置访问限制，IP层，用户层

Oracle 设置访问限制，IP层，用户层（特定用户禁止访问）

1. 通过SQLNET.ORA文件配置IP地址白名单，黑名单

sqlnet.ora


tcp.validnode_checking = yes                       #需要设置成yes，方可激活生效                
tcp.invited_nodes=(10.240.1.8,10.240.1.7)      #允许访问的IP
#tcp.excluded_nodes=(10.240.1.8,10.240.1.7) #不允许访问的IP 

2. 通过触发器TRIGGER设置，IP地址和用户禁止登陆
TRIGGER
-->TRIGGER限制用户BADUSER；
CREATE OR REPLACE TRIGGER DISABLELOGIN  
    AFTER LOGON ON BADUSER.SCHEMA  
BEGIN                                                                      
   IF ORA_CLIENT_IP_ADDRESS LIKE ('168.60.40.105') THEN    
   RAISE_APPLICATION_ERROR(-20001,'USER '||ORA_LOGIN_USER||' IS NOT ALLOWED TO CONNECT FROM '||ORA_CLIENT_IP_ADDRESS);
   END IF;
END;
/
--不能指定sys.schema，会报《ORA-30510: 系统触发器不能在 SYS 用户方案中定义》
--限制某IP  ORA_CLIENT_IP_ADDRESS IN ('10.240.1.7','10.240.1.8')




create or replace trigger DISABLELOGIN2  
after logon on database 
declare 
  ip STRING(30);  
  user STRING(30);  
begin 
SELECT SYS_CONTEXT('USERENV','SESSION_USER') into user from dual;  
SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') into ip from dual;  
if user='BADUSER123'  
  THEN 
      IF ip in ('168.60.40.105')     
      THEN 
      raise_application_error(-20001,'User '||user||' is not allowed to connect from '||ip);
      END IF;  
END IF;  
end;  
/






DROP TRIGGER SYS.TRI_NODROPTAB;


CREATE OR REPLACE TRIGGER SYS.tri_nodroptab
before drop on database
WHEN (
USER not in ('SYS','SYSTEM')
      )
DECLARE
user_program v$session.PROGRAM%TYPE;
usr_command v$session.COMMAND%TYPE;
CURSOR ses_program IS
SELECT upper(program),command FROM SYS.v$session WHERE audsid = sys_context('USERENV','SESSIONID') and sid=sys_context('USERENV','SID');
begin
   OPEN ses_program;
   FETCH ses_program INTO user_program,usr_command;
   CLOSE ses_program;
   --in 10gr2, v$session.command=12 means drop table
   IF ((user_program like 'PLSQLDEV%' or user_program like 'TOAD%') and usr_command=12)  THEN
      RAISE_APPLICATION_ERROR ( -20001,'ARE YOU REALLY SURE TO DROP THIS TABLE??? Pls use sqlplus to do it if you are determined.');
   END IF;
end;
/