Oracle 使用sqlnet.ora/trigger限制/允许某IP或IP段访问指定用户
原创 Oracle 作者：maohaiqing0304 时间：2016-05-03 17:05:46 17297 0
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
#tcp.excluded_nodes=(10.240.1.8,10.240.1.7) #不允许访问的IP
注释：
在9i提供了几个参数：-->9i以前版本更改protocol.ora文件...
TCP.INVITED_NODES ：设置允许访问数据库的IP地址列表，当这个参数和TCP.EXCLUDED_NODES设置的地址相同的时候将覆盖TCP.EXCLUDED_NODES设置。
TCP.VALIDNODE_CHECKING：检测上述参数的设置。
简单演示：
sql*Plus: Release 11.2.0.4.0 Production on Wed Apr 27 18:44:15 2016
Copyright (c) 1982,2013,Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning,OLAP,Data Mining and Real Application Testing options
Copyright (c) 1982,2013,Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning,OLAP,Data Mining and Real Application Testing options
sql>
sql*Plus: Release 11.2.0.4.0 Production on Wed Apr 27 18:44:40 2016
Copyright (c) 1982,Oracle. All rights reserved.
ERROR:
ORA-12547: TNS:lost contact
ORA-01017: invalid username/password; logon denied
SP2-0157: unable to CONNECT to ORACLE after 3 attempts,exiting sql*Plus
[ [email protected] ~]$
Copyright (c) 1982,Oracle. All rights reserved.
ERROR:
ORA-12547: TNS:lost contact
ORA-01017: invalid username/password; logon denied
SP2-0157: unable to CONNECT to ORACLE after 3 attempts,exiting sql*Plus
[ [email protected] ~]$
二、触发器
-->TRIGGER限制10.240.1.%网段访问lottery用户
CREATE OR REPLACE TRIGGER DISABLELOGIN
AFTER logoN ON LOTTERY.SCHEMA -->使用方式为USERNAME.SCHEMA，若直接写database，RAISE_APPLICATION_ERROR部分不起作用..
BEGIN
IF ORA_CLIENT_IP_ADDRESS LIKE (‘10.240.1.%‘) 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‘)
简单演示：
[
[email protected]
10.240.1.7 ~]$ sqlplus lottery/
[email protected]/test
sql*Plus: Release 11.2.0.4.0 Production on Wed Apr 27 16:05:55 2016
Copyright (c) 1982,Oracle. All rights reserved.
ERROR:
ORA-00604: error occurred at recursive sql level 1
ORA-20001: USER LOTTERY IS NOT ALLOWED TO CONNECT FROM 10.240.1.7
ORA-06512: at line 3
[ [email protected] admin]$
sql*Plus: Release 11.2.0.4.0 Production on Wed Apr 27 16:05:55 2016
Copyright (c) 1982,Oracle. All rights reserved.
ERROR:
ORA-00604: error occurred at recursive sql level 1
ORA-20001: USER LOTTERY IS NOT ALLOWED TO CONNECT FROM 10.240.1.7
ORA-06512: at line 3
[ [email protected] admin]$
[
[email protected]
10.240.1.8 ~]$ sqlplus lottery/
[email protected]/test
sql*Plus: Release 11.2.0.4.0 Production on Wed Apr 27 16:05:55 2016
Copyright (c) 1982,Oracle. All rights reserved.
ERROR:
ORA-00604: error occurred at recursive sql level 1
ORA-20001: USER LOTTERY IS NOT ALLOWED TO CONNECT FROM 10.240.1.8
ORA-06512: at line 3
[ [email protected] ~]$
sql*Plus: Release 11.2.0.4.0 Production on Wed Apr 27 16:05:55 2016
Copyright (c) 1982,Oracle. All rights reserved.
ERROR:
ORA-00604: error occurred at recursive sql level 1
ORA-20001: USER LOTTERY IS NOT ALLOWED TO CONNECT FROM 10.240.1.8
ORA-06512: at line 3
[ [email protected] ~]$
sql*Plus: Release 11.2.0.4.0 Production on Wed Apr 27 16:23:33 2016
Copyright (c) 1982,Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning,Data Mining and Real Application Testing options
sql>
Copyright (c) 1982,Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning,Data Mining and Real Application Testing options
sql>
sql> SELECT * FROM DBA_TRIGGERS WHERE trigger_name=‘DISABLELOGIN‘ ;
.png)
@H_301_234@
