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;

/