[20160513]Restrict Session與靜態監聽.txt

lfree發表於2016-05-17

[20160513]Restrict Session與靜態監聽.txt

--給同事講解動靜態監聽時,一次測試,當執行後alter system enable restricted session;發現遠端使用者依舊可以登入,檢查發現是靜態
--監聽註冊搞的鬼,做一個記錄.並且把各種情況做一個總結:

1.環境:
SYS@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.檢查監聽狀態:
--沒有靜態監聽註冊.
$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

#SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.78)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle
DIAG_ADR_ENABLED_LISTENER=OFF

3.首先分析啟動資料庫的各個階段:
--//關閉資料庫.
SYS@book> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.

$ lsnrctl status
...
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/11.2.0.4/dbhome_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.78)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
--可以發現沒有服務.

--啟動到nomount階段:
SYS@book> startup nomount
ORACLE instance started.

Total System Global Area  634732544 bytes
Fixed Size                  2255792 bytes
Variable Size             197133392 bytes
Database Buffers          427819008 bytes
Redo Buffers                7524352 bytes

$ lsnrctl status
...
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/11.2.0.4/dbhome_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.78)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "book" has 1 instance(s).
  Instance "book", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully

--注意這個時候監聽狀態BLOCKED.
--這個時候遠端client端是無法連線資料庫,要想透過連線要加入UR=A的內容如下,並且只能以sys使用者登入.

78 =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.78)(PORT = 1521))
    )
    (SDU = 32768)
    (CONNECT_DATA =
        (UR = A)
        ~~~~~~~
      (SERVICE_NAME = book)
  )
)

--//啟動到mount階段:
SYS@book> alter database mount ;
Database altered.

$ lsnrctl status
...
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/11.2.0.4/dbhome_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.78)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "book" has 1 instance(s).
  Instance "book", status READY, has 1 handler(s) for this service...
The command completed successfully

--如果不出現狀態READY,可能要等1小會,或者執行alter system register;,這個時候可以實現遠端登入使用sys使用者.
--另外僅僅存在一個服務.所以僅僅看到一項.
SYS@book> show parameter service
NAME          TYPE    VALUE
------------- ------- ----------
service_names string  book

--//啟動到open階段:
SYS@book> alter database open ;
Database altered.

$ lsnrctl status
...
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/11.2.0.4/dbhome_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.78)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=gxqyydg4)(PORT=8888))(Presentation=HTTP)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=gxqyydg4)(PORT=7777))(Presentation=FTP)(Session=RAW))
Services Summary...
Service "book" has 1 instance(s).
  Instance "book", status READY, has 2 handler(s) for this service...
Service "bookXDB" has 1 instance(s).
  Instance "book", status READY, has 1 handler(s) for this service...
The command completed successfully

--到open階段,可以發現還啟動了bookXDB服務,以及開啟了HTTP以及ftp埠.

4.執行alter system enable restricted session;後.
--//執行alter system enable restricted session後,可以禁止遠端使用者登入,但不影響本地使用者登入.但是實際情況呢?

SYS@book> alter system enable restricted session;
System altered.

$ lsnrctl status
...
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/11.2.0.4/dbhome_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.78)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=gxqyydg4)(PORT=8888))(Presentation=HTTP)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=gxqyydg4)(PORT=7777))(Presentation=FTP)(Session=RAW))
Services Summary...
Service "book" has 1 instance(s).
  Instance "book", status RESTRICTED, has 2 handler(s) for this service...
Service "bookXDB" has 1 instance(s).
  Instance "book", status RESTRICTED, has 1 handler(s) for this service...
The command completed successfully

--//可以發現狀態變成了RESTRICTED.遠端連線出現如下錯誤不管sys使用者還是scott使用者.

ORA-12526: TNS:listener: all appropriate instances are in restricted mode

--//但是如果tnsnames.ora配置加入UR=A,完全不受任何影響,照樣可以登入.感覺UR=A就像開啟了一個後門.
--//取消restricted.

SYS@book> alter system disable restricted session;
System altered.

5.加入靜態監聽註冊呢?
$ lsnrctl stop
..

$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

#SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF

SID_LIST_LISTENER =
   (SID_LIST =
    (SID_DESC =
      (SDU=32767)
      (GLOBAL_DBNAME = book)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/dbhome_1)
      (SID_NAME = book)
      )
    )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.78)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle
DIAG_ADR_ENABLED_LISTENER=OFF

$ lsnrctl start
..

SYS@book> alter system register;
System altered.

$ lsnrctl status
...

Listener Parameter File   /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/11.2.0.4/dbhome_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.78)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=gxqyydg4)(PORT=8888))(Presentation=HTTP)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=gxqyydg4)(PORT=7777))(Presentation=FTP)(Session=RAW))
Services Summary...
Service "book" has 2 instance(s).
  Instance "book", status UNKNOWN, has 1 handler(s) for this service...
  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  Instance "book", status READY, has 2 handler(s) for this service...
Service "bookXDB" has 1 instance(s).
  Instance "book", status READY, has 1 handler(s) for this service...
The command completed successfully

--注意看~,啟動狀態是UNKNOWN,這個就是靜態監聽註冊,因為這個即使資料庫沒有啟動也存在,因為這個例項是否存在未知,所以oracle標
--識狀態為UNKNOWN.

SYS@book> alter system enable restricted session;
System altered.

$ lsnrctl status
....
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/11.2.0.4/dbhome_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.78)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=gxqyydg4)(PORT=8888))(Presentation=HTTP)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=gxqyydg4)(PORT=7777))(Presentation=FTP)(Session=RAW))
Services Summary...
Service "book" has 2 instance(s).
  Instance "book", status UNKNOWN, has 1 handler(s) for this service...
  Instance "book", status RESTRICTED, has 2 handler(s) for this service...
Service "bookXDB" has 1 instance(s).
  Instance "book", status RESTRICTED, has 1 handler(s) for this service...
The command completed successfully

--可以發現動態監聽狀態RESTRICTED.但是由於靜態監聽存在,遠端使用者依舊可以登入,無需加入UR=A.
--遠端client使用sqlplus連上後觀察:

$ lsnrctl service
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 17-MAY-2016 08:23:31
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.78)(PORT=1521)))
Services Summary...
Service "book" has 2 instance(s).
  Instance "book", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:1 refused:0
     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
         LOCAL SERVER
  Instance "book", status RESTRICTED, has 2 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: gxqyydg4, pid: 53314>
         (ADDRESS=(PROTOCOL=tcp)(HOST=gxqyydg4)(PORT=21060))
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "bookXDB" has 1 instance(s).
  Instance "book", status RESTRICTED, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: gxqyydg4, pid: 53314>
         (ADDRESS=(PROTOCOL=tcp)(HOST=gxqyydg4)(PORT=21060))
The command completed successfully

--注意看~,可以發現是透過靜態監聽連上資料庫的.

SYS@book> alter system disable restricted session;
System altered.

6.做一個總結:
-- 如果tnsnames.ora,加入(UR=A),就好像開啟了一個後門,
-- 配置靜態監聽註冊, alter system enable restricted session;對於遠端使用者無效.
-- 最後在補充一點,不啟動監聽實際上也可以遠端連線資料庫,參考連結:http://blog.itpub.net/267265/viewspace-1816211/

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2101141/,如需轉載,請註明出處,否則將追究法律責任。

相關文章