[20170111]設定無需口令登入資料庫2.txt

[20170111]設定無需口令登入資料庫2.txt

--//上午測試無需口令登入資料庫，這樣連線資料庫使用sqlplus /@book ,這樣實際上透過網路連線資料庫，哪怕是在本機也是這樣。
--//思考一下是否繞過。
--//前面測試連結：http://blog.itpub.net/267265/viewspace-2132220/

1.環境：
SCOTT@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.分析2種連線方式：
$ rlsql scott/book
...
SCOTT@book> @ &r/spid
       SID    SERIAL# SPID       PID  P_SERIAL# C50
---------- ---------- ------ ------- ---------- --------------------------------------------------
        12       2075 49033       25        213 alter system kill session '12,2075' immediate;

$ ps -ef | grep 4903[3]
oracle   49033 49032  0 14:50 ?        00:00:00 oraclebook (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
                                                                                             ~~~~~~~~~~~~~~
--//可以發現本地使用PROTOCOL=beq。(LOCAL=YES)

--//透過網路連線資料庫。
$ rlsql /@book as sysdba
...
SYS@book> @ &r/spid

       SID    SERIAL# SPID       PID  P_SERIAL# C50
---------- ---------- ------ ------- ---------- --------------------------------------------------
        24       1631 49055       26        214 alter system kill session '24,1631' immediate;

$ ps -ef | grep 4905[5]
oracle   49055     1  0 14:52 ?        00:00:00 oraclebook (LOCAL=NO)
--//透過網路連線資料庫,(LOCAL=NO)

3.突然想起我配置IPC協議模式,順便補習一些概念：

IPC (Inter Process Communications) is a SQL*Net protocol similar to the BEQ protocol in that it is only used for local
connections (when client and server programs reside on the same system). IPC can be used to establish Dedicated Server
and Shared Server connections. A listener is required to make IPC connections.
--//從介紹看依舊需要網路，只不過僅僅支援本機連線。

--//檢查監聽配置：
$ 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 = 127.0.0.1)(PORT = 1521)(RATE_LIMIT=YES))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.78)(PORT = 1521)(RATE_LIMIT=YES))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle
#DIAG_ADR_ENABLED_LISTENER=OFF
USE_NS_PROBES_FOR_DCD=true
#DYNAMIC_REGISTRATION_LISTENER = off
INBOUND_CONNECT_TIMEOUT_LISTENER=100
CONNECTION_RATE_LISTENER=5

--//在tnsnames.ora中配置加入：
78IPC =
(DESCRIPTION =
        (ADDRESS = (PROTOCOL = IPC)(KEY=EXTPROC1521))
        (CONNECT_DATA =
                (SERVER = DEDICATED)
#                (SID = book)
                (SERVICE_NAME = book)
        )
)

--//執行如下測試：
sqlplus scott/book@78ipc
sqlplus scott/book@(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))(CONNECT_DATA=(SERVER=DEDICATED)(SID=book)(SERVICE_NAME=book)))
sqlplus scott/book@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.78)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=book)(UR=A)(INSTANCE_NAME=book)(SERVER=DEDICATED)))

--//以上測試ok。如何使用beq連線資料庫呢，指配置連線串。
--//google,百度N久，做了許多嘗試，最終配置如下透過，在tnsnames.ora中加入:
--//參考連結:

78BEQ =
  (DESCRIPTION =
    (ADDRESS=(PROTOCOL=BEQ)(PROGRAM =oracle)(ARGV0=oraclebook)
    (ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=BEQ)))')
    (ENVS ='ORACLE_SID=book,ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1'))
  )

sqlplus scott/book@(DESCRIPTION=(ADDRESS=(PROTOCOL=BEQ)(PROGRAM=oracle)(ARGV0=oraclebook)(ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=BEQ)))')(ENVS='ORACLE_SID=book,ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1)))

--//測試透過:
$ rlsql scott/book@78beq
SCOTT@78beq> @ &r/spid

       SID    SERIAL# SPID       PID  P_SERIAL# C50
---------- ---------- ------ ------- ---------- --------------------------------------------------
        12       2091 53815       25        221 alter system kill session '12,2091' immediate;

$ ps -ef | grep 5381[5]
oracle   53815     1  0 09:08 ?        00:00:00 oraclebook (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=BEQ)))

--//很明顯這樣配置是使用PROTOCOL=BEQ.繼續測試:

$ mkstore -wrl /u01/app/oracle/admin/wallets -createCredential 78beq scott book
Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.
Enter wallet password:
Create credential oracle.security.client.connect_string2

$ rlsql /@78beq
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 12 09:10:48 2017
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SCOTT@78beq> show user
USER is "SCOTT"

SCOTT@78beq> @ &r/spid

       SID    SERIAL# SPID       PID  P_SERIAL# C50
---------- ---------- ------ ------- ---------- --------------------------------------------------
        24       2281 53872       26        192 alter system kill session '24,2281' immediate;

$ ps -fp 53872
UID        PID  PPID  C STIME TTY          TIME CMD
oracle   53872     1  0 09:10 ?        00:00:00 oraclebook (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=BEQ)))

--//探究純屬無聊...^_^.