VMCD.ORG » sqlnet.ora 控制访问IP

sqlnet.ora中进行下列参数的设置可以限制或允许用户从特定的客户机连接到数据库中

[root@Hong ~]# ifconfig -a
eth0 Link encap:Ethernet HWaddr 00:0C:29:24:93:F5
inet addr:192.168.1.99 Bcast:192.168.1.255 Mask:255.255.255.0
inet6 addr: fe80::20c:29ff:fe24:93f5/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:10471 errors:0 dropped:0 overruns:0 frame:0
TX packets:10026 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:925836 (904.1 KiB) TX bytes:1022351 (998.3 KiB)
Interrupt:10 Base address:0x2024

[oracle@Hong ~]$ cd /u01/app/oracle/product/10.2.0.1/db_1/network/admin/
[oracle@Hong admin]$ cat tnsnames.ora
css =
(description =
(address =(protocol =tcp)(host=192.168.1.223)(port=1521))
(connect_data=
(server=dedicated)
(service_name=css)
)
)

我们切换到另一台主机查看sqlnet.ora

[root@yang ~]# ifconfig -a
eth0 Link encap:Ethernet HWaddr 00:0C:29:40:56:87
inet addr:192.168.1.223 Bcast:192.168.1.255 Mask:255.255.255.0
inet6 addr: fe80::20c:29ff:fe40:5687/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:65490 errors:0 dropped:0 overruns:0 frame:0
TX packets:73327 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:10821555 (10.3 MiB) TX bytes:21653642 (20.6 MiB)
Interrupt:185 Base address:0x2024

[root@yang ~]# ps -ef |grep css
oracle 3313 1 0 Oct18 ? 00:00:00 ora_pmon_css
oracle 3315 1 0 Oct18 ? 00:00:00 ora_psp0_css
oracle 3318 1 0 Oct18 ? 00:00:00 ora_mman_css
oracle 3320 1 0 Oct18 ? 00:00:00 ora_dbw0_css
oracle 3322 1 0 Oct18 ? 00:00:00 ora_lgwr_css
oracle 3324 1 0 Oct18 ? 00:00:00 ora_ckpt_css
oracle 3326 1 0 Oct18 ? 00:00:02 ora_smon_css
oracle 3328 1 0 Oct18 ? 00:00:00 ora_reco_css
oracle 3330 1 0 Oct18 ? 00:00:01 ora_mmon_css
oracle 3332 1 0 Oct18 ? 00:00:00 ora_mmnl_css
oracle 3334 1 0 Oct18 ? 00:00:00 ora_d000_css
oracle 3336 1 0 Oct18 ? 00:00:00 ora_s000_css
oracle 3340 3291 0 Oct18 ? 00:00:03 oraclecss (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 3374 1 0 Oct18 ? 00:00:00 ora_cjq0_css
root 4176 3719 0 00:09 pts/3 00:00:00 grep css

[oracle@yang admin]$ cat sqlnet.ora
tcp.validnode_checking = yes
#tcp.excluded_nodes = (192.168.1.99)
NAMES.DIRECTORY_PATH= (TNSNAMES)
[oracle@yang admin]$

[oracle@Hong admin]$ tnsping css

TNS Ping Utility for Linux: Version 10.2.0.1.0 – Production on 19-OCT-2011 00:10:31

Used parameter files:
/u01/app/oracle/product/10.2.0.1/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (description = (address =(protocol =tcp)(host=192.168.1.223)(port=1521)) (connect_data= (server=dedicated) (service_name=css)))
OK (290 msec)
[oracle@Hong admin]$ sqlplus ‘sys/oracle@css as sysdba’

SQL*Plus: Release 10.2.0.1.0 – Production on Wed Oct 19 00:10:39 2011

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

此时可以正常访问源端服务器,下面我们来设置excluded参数

[oracle@yang admin]$ cat sqlnet.ora
tcp.validnode_checking = yes
tcp.excluded_nodes = (192.168.1.99)
NAMES.DIRECTORY_PATH= (TNSNAMES)
[oracle@yang admin]$ lsnrctl reload

LSNRCTL for Linux: Version 10.2.0.4.0 – Production on 19-OCT-2011 00:11:58

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.223)(PORT=1521)))
The command completed successfully
[oracle@yang admin]$

[oracle@Hong ~]$ tnsping css

TNS Ping Utility for Linux: Version 10.2.0.1.0 – Production on 19-OCT-2011 00:14:10

Used parameter files:
/u01/app/oracle/product/10.2.0.1/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (description = (address =(protocol =tcp)(host=192.168.1.223)(port=1521)) (connect_data= (server=dedicated) (service_name=css)))
TNS-12537: TNS:connection closed
[oracle@Hong ~]$ sqlplus ‘sys/oracle@css as sysdba’

SQL*Plus: Release 10.2.0.1.0 – Production on Wed Oct 19 00:14:48 2011

ERROR:
ORA-12537: TNS:connection closed

Enter user-name:

来自192.168.1.99的访问被close

另外提供一种trigger禁止特性ip访问：

–>限制单用户从单IP登录，下面限制scott用户从客户端的登录

SQL> CREATE OR REPLACE TRIGGER disablelogin
2 AFTER logon ON liu.schema –>注意使用方式为username.schema
DECLARE
ipaddr VARCHAR2(30);
BEGIN
SELECT sys_context(‘userenv’, ‘ip_address’)
INTO ipaddr
FROM dual;
IF ipaddr = ‘192.168.1.103’ THEN
raise_application_error(‘-20001’,
‘You can not login,Please contact administrator’);
END IF;
END disablelogin;
/
Trigger created.

–>限制IP段登录
CREATE OR REPLACE TRIGGER refuse_range_ip
AFTER logon ON liu.schema
DECLARE
ipaddr VARCHAR2(30);
BEGIN
SELECT sys_context(‘userenv’, ‘ip_address’)
INTO ipaddr
FROM dual;
IF ipaddr LIKE (‘192.168.1.%’) THEN
raise_application_error(‘-20001’,
‘You can not login,Please contact administrator’);
END IF;
END refuse_range_ip;
/

Classic Gucci Kids Shoes for less 912 13 years ago

I have just discovered your internet site and appreciate every article. I admire your talent.

gucci high top sneakers men 13 years ago

I have to admit your own weblog is exceptional! I’ll unquestionably appear back again again!

mens fashion dress shoes 13 years ago

Among the finest to inform a person that I am just all new to running a blog and certainly loved youre website. Most likely I will bookmark your website . You certainly possess exceptional posts. Kudos for discussing with us your blog.