生产库中常用的就2种一个是sqlnet.ora,一个是用trigger，由于最近用到过特此记录(实际使用中常用就是server端sqlnet.ora直接限制).

#10g client connet 11g database
[oracle@ora10g admin]$ sqlplus system/a123@test

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Apr 23 21:10:22 2010

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

1.sqlnet.ora方式
[oracle@vm11g admin]$ vi sqlnet.ora

tcp.validnode_checking=yes
tcp.invited_nodes =(192.168.1.99)
tcp.excluded_nodes=(192.168.1.66)

~
~

TCP.EXCLUDED_NODES
设置禁止访问数据库的IP地址列表。
TCP.INVITED_NODES
设置允许访问数据库的IP地址列表，当这个参数和TCP.EXCLUDED_NODES设置的地址相同的时候将覆盖TCP.EXCLUDED_NODES设置。
TCP.VALIDNODE_CHECKING
检测上述参数的设置。

~[oracle@vm11g admin]$ lsnrctl start 重起lsnr

LSNRCTL for Linux: Version 11.1.0.6.0 - Production on 12-APR-2010 09:19:45

Starting /home/oracle/product/11g/database/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.1.0.6.0 - Production
System parameter file is /home/oracle/product/11g/database/network/admin/listener.ora
Log messages written to /home/oracle/diag/tnslsnr/vm11g/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vm11g)(PORT=1521)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.1.0.6.0 - Production
Start Date                12-APR-2010 09:19:45
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /home/oracle/product/11g/database/network/admin/listener.ora
Listener Log File         /home/oracle/diag/tnslsnr/vm11g/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vm11g)(PORT=1521)))
The listener supports no services
The command completed successfully

SQL> alter system register;

System altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@vm11g admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.1.0.6.0 - Production on 12-APR-2010 09:35:53

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.1.0.6.0 - Production
Start Date                12-APR-2010 09:35:44
Uptime                    0 days 0 hr. 0 min. 15 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /home/oracle/product/11g/database/network/admin/listener.ora
Listener Log File         /home/oracle/diag/tnslsnr/vm11g/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vm11g)(PORT=1521)))
Services Summary...
Service "xh" has 1 instance(s).
Instance "xh", status READY, has 1 handler(s) for this service...
Service "xhXDB" has 1 instance(s).
Instance "xh", status READY, has 1 handler(s) for this service...
Service "xh_XPT" has 1 instance(s).
Instance "xh", status READY, has 1 handler(s) for this service...
The command completed successfully

#已经连接不上了
[oracle@ora10g admin]$ sqlplus system/a123@test

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Apr 23 21:23:14 2010

ERROR:
ORA-12537: TNS:connection closed

[oracle@vm11g admin]$ rm -rf sqlnet.ora

2.trigger方式思路很简单就是登陆时候 sys_context获取下用户信息中的ip_address(还可以获取很多比如sessionid之类),然后抛出一个异常
create or replace trigger test_ip
after logon on xh.schema
declare
ip VARCHAR2(50);
begin
select sys_context('userenv', 'ip_address') into ip from dual;
if ip='192.168.1.66' then
raise_application_error('-20002', 'this is test you can not loggon');
end if;
end test_ip;

SQL> set linesize 1000
SQL> select owner,status,trigger_type,triggering_event from dba_triggers where trigger_name='TEST_IP';

OWNER STATUS TRIGGER_TYPE TRIGGERING_EVENT
------------------------------ -------- ---------------- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SYS ENABLED AFTER EVENT LOGON

SQL>

[oracle@ora10g admin]$
[oracle@ora10g admin]$ sqlplus xh/a123@test

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Apr 23 21:44:04 2010

ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20002: this is test you can not loggon
ORA-06512: at line 6

Enter user-name:

SYS_CONTEXT('USERENV','TERMINAL') terminal,

SYS_CONTEXT('USERENV','LANGUAGE') language,

SYS_CONTEXT('USERENV','SESSIONID') sessionid,

SYS_CONTEXT('USERENV','INSTANCE') instance,

SYS_CONTEXT('USERENV','ENTRYID') entryid,

SYS_CONTEXT('USERENV','ISDBA') isdba,

SYS_CONTEXT('USERENV','NLS_TERRITORY') nls_territory,

SYS_CONTEXT('USERENV','NLS_CURRENCY') nls_currency,

SYS_CONTEXT('USERENV','NLS_CALENDAR') nls_calendar,

SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') nls_date_format,

SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') nls_date_language,

SYS_CONTEXT('USERENV','NLS_SORT') nls_sort,

SYS_CONTEXT('USERENV','CURRENT_USER') current_user,

SYS_CONTEXT('USERENV','CURRENT_USERID') current_userid,

SYS_CONTEXT('USERENV','SESSION_USER') session_user,

SYS_CONTEXT('USERENV','SESSION_USERID') session_userid,

SYS_CONTEXT('USERENV','PROXY_USER') proxy_user,

SYS_CONTEXT('USERENV','PROXY_USERID') proxy_userid,

SYS_CONTEXT('USERENV','DB_DOMAIN') db_domain,

SYS_CONTEXT('USERENV','DB_NAME') db_name,

SYS_CONTEXT('USERENV','HOST') host,

SYS_CONTEXT('USERENV','OS_USER') os_user,

SYS_CONTEXT('USERENV','EXTERNAL_NAME') external_name,

SYS_CONTEXT('USERENV','IP_ADDRESS') ip_address,

SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') network_protocol,

SYS_CONTEXT('USERENV','BG_JOB_ID') bg_job_id,

SYS_CONTEXT('USERENV','FG_JOB_ID') fg_job_id,

SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE') authentication_type,

SYS_CONTEXT('USERENV','AUTHENTICATION_DATA') authentication_data

ITPub博客

oracle 登陆限制ip

最新文章