文章出处:http://blog.sina.com.cn/s/blog_4d19b6210100jyce.html
限制登录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. sys@ORCL> 在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 显然使用触发器会带来更多便利 在system,用户,或SYS用户下更有效! create or replace trigger chk_ip after logon on dzjw.schema declare ipaddr VARCHAR2(30); begin select sys_context('userenv', 'ip_address') into ipaddr from dual; if ipaddr != ('192.168.0.124') then raise_application_error('-20005', 'object statistics are locked stattype all'); end if; end chk_ip;