oracle 登陸限制ip

生產庫中常用的就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

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

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

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

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

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

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

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

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

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

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部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-660840/，如需轉載，請註明出處，否則將追究法律責任。