分类: Oracle

2017-11-07 17:11:26



标题:Oracle 使用sqlnet.ora/trigger限制/允许某IP或IP段访问指定用户 

作者:lōττéry©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]



注释:
 接触MySQL的朋友想必都知道mysql可针对指定IP/IP段来限制用户的访问,在Oracle数据库中默认有账号密码访问主机的权限的IP都可登陆该DB用户;
  那么,Oracle 如何实现针对DB、单个用户来限制/允许IP访问呢?
  1、整个DB层:可设置$ORACLE_HOME/network/admin/sqlnet.ora文件,限制/允许IP访问;  -->不可针对IP段..
  2、单个用户:可通过trigger触发器限制/允许某IP或IP段访问;                 -->实验不可对整个DB层 (AFTER LOGON ON database)登陆提示告警..


一、sqlnet.ora  

[oracle@10.240.1.7 admin]$ cat 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 
注释:
在9i提供了几个参数:-->9i以前版本更改protocol.ora文件...
TCP.EXCLUDED_NODES    :设置禁止访问数据库的IP地址列表。
TCP.INVITED_NODES     :设置允许访问数据库的IP地址列表,当这个参数和TCP.EXCLUDED_NODES设置的地址相同的时候将覆盖TCP.EXCLUDED_NODES设置。
TCP.VALIDNODE_CHECKING:检测上述参数的设置。 

简单演示:
[oracle@10.240.1.8 ~]$ sqlplus lottery/lottery@10.240.1.7/test 
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
SQL> 
[oracle@10.240.1.9 ~]$  sqlplus lottery/lottery@10.240.1.7/test
SQL*Plus: Release 11.2.0.4.0 Production on Wed Apr 27 18:44:40 2016
Copyright (c) 1982, 2013, 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
[oracle@10.240.1.9 ~]$ 

二、触发器

-->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')

简单演示:
[oracle@10.240.1.7 ~]$ sqlplus lottery/lottery@10.240.1.7/test
SQL*Plus: Release 11.2.0.4.0 Production on Wed Apr 27 16:05:55 2016
Copyright (c) 1982, 2013, 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
[oracle@10.240.1.7 admin]$

[oracle@10.240.1.8 ~]$ sqlplus lottery/lottery@10.240.1.7/test
SQL*Plus: Release 11.2.0.4.0 Production on Wed Apr 27 16:05:55 2016
Copyright (c) 1982, 2013, 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
[oracle@10.240.1.8 ~]$

[oracle@10.240.2.8 ~]$  sqlplus lottery/lottery@10.240.1.7/test 
SQL*Plus: Release 11.2.0.4.0 Production on Wed Apr 27 16:23:33 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
SQL> 
SQL> SELECT * FROM DBA_TRIGGERS WHERE trigger_name='DISABLELOGIN' ;



【源于本人笔记】 若有书写错误,表达错误,请指正...


此条目发表在 Oracle分类目录。将固定连接加入收藏夹。



阅读(421) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册