限制登录oracle的IP地址有两种方法
一、编辑$ORACLE_HOME/network/admin/sqlnet.ora,增加
tcp.validnode_checking = yes
#tcp.invited_nodes=() #允许访问的IP列表,各IP之间用逗号分隔
tcp.excluded_nodes=() #限制访问的IP列表,个IP之间用逗号分隔
注意:
- 不能同时使用tcp.invited_nodes和tcp.excluded_nodes
- 只能指定具体的IP地址,不能指定IP段
- 只能限制TCP协议
- 需要重启监听生效
1、需求:限制IP 192.168.137.1登录
2、实现方式:
oracle@oracle[/oracle/app/10g]> cd $ORACLE_HOME/network/admin
oracle@oracle[/oracle/app/10g/network/admin]> ls -l
total 16
drwxr-x--- 2 oracle oinstall 4096 Dec 30 11:37 samples
-rw-r----- 1 oracle oinstall 172 Dec 26 2003 shrept.lst
-rw-r--r-- 1 oracle oinstall 236 Dec 30 13:10 sqlnet.ora
-rw-r--r-- 1 oracle oinstall 347 Mar 25 21:22 tnsnames.ora
oracle@oracle[/oracle/app/10g/network/admin]> vi sqlnet.ora
# sqlnet.ora Network Configuration File: /oracle/app/10g/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
SQLNET.RECV_TIMEOUT = 0
SQLNET.EXPIRE_TIME = 0
SQLNET.SEND_TIMEOUT = 0
SQLNET.INBOUND_CONNECT_TIMEOUT = 0
tcp.validnode_checking = yes
#tcp.invited_nodes=()
tcp.excluded_nodes=(192.168.137.1)
oracle@oracle[/oracle/app/10g/network/admin]> lsnrctl stop
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 15-JUN-2009 12:10:05
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
The command completed successfully
oracle@oracle[/oracle/app/10g/network/admin]> lsnrctl start
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 15-JUN-2009 12:10:17
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Starting /oracle/app/10g/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Log messages written to /oracle/app/10g/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle)(PORT=1521)))
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 15-JUN-2009 12:10:17
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File /oracle/app/10g/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle)(PORT=1521)))
The listener supports no services
The command completed successfully
在192.168.137.1上登录,显示
ORA-12547:TSN:丢失连接
二、使用触发器
由于sqlnet.ora方式不支持IP段,对于某个IP段的限制,可以使用触发器实现
1、需求:限制IP段 192.168.137登录
2、实现方式:
oracle@oracle[/oracle/app/10g/network/admin]> sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jun 15 12:25:32 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
sys@ORCL>
sys@ORCL> create or replace trigger chk_ip
2 after logon on scott.schema
3 declare
4 ipaddr VARCHAR2(30);
5 begin
6 select sys_context('userenv', 'ip_address') into ipaddr from dual;
7 if ipaddr like ('192.168.137.%') then
8 raise_application_error('-20001', 'you can not logon by scott');
9 end if;
10 end chk_ip;
11 /
Trigger created.
在192.168.137.1上登录,显示:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: you can not logon by scott
ORA-06512: at line 6
[@more@]来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22049049/viewspace-1023932/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22049049/viewspace-1023932/