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;
/