外部身份驗證方法(os_authent_prefix和remote_os_authent)

--整理以前的學習筆記

參考資料：
http://biancheng.dnbcw.info/oracle/164652.html
http://hi.baidu.com/loveyurui/blog/item/1aafbba33a207e804710648a.html

在linux上建立賬戶erin
[root@mylinux ~]# useradd erin
[root@mylinux ~]# passwd erin
Changing password for user erin.
New UNIX password:
BAD PASSWORD: it is too short
Retype new UNIX password:
passwd: all authentication tokens updated successfully.

切換到oracle使用者，啟動例項
[root@mylinux ~]# su - oracle
[oracle@mylinux ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Wed May 16 21:14:58 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area  497995776 bytes
Fixed Size                  1337464 bytes
Variable Size             373294984 bytes
Database Buffers          117440512 bytes
Redo Buffers                5922816 bytes
Database mounted.
Database opened.
SQL> show parameter os_authent_prefix

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix                    string      ops$
SQL> create user ops$erin identified externally;

User created.

SQL> grant create session to ops$erin;

Grant succeeded.

切換到erin賬戶
su - erin
export ORACLE_SID=orcl
export ORACLE_HOME=/u01/oracle
export PATH=/u01/oracle/bin:$PATH

[oracle@mylinux bin]$ ll oracle
-rwsr-s--x 1 oracle oinstall 173515991 Mar 13 05:39 oracle

確保/u01/oracle/bin/oracle有suid屬性,這個suid的屬性的意思是其他使用者執行oracle這個檔案的時候，可以暫時地得到

檔案擁有人oracle的許可權（suid詳細資料，請參考鳥哥的linux私房菜）
如果沒有的話，chmod +s oracle，試試看如果oracle沒有s這個屬性(chmod -s oracle)時，erin是否可以正常連線資料庫

[erin@mylinux bin]$ env | grep ORA
ORACLE_SID=orcl
ORACLE_HOME=/u01/oracle
[erin@mylinux ~]$ echo $PATH
/u01/oracle/bin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/erin/bin
[erin@mylinux ~]$ sqlplus /
SQL*Plus: Release 11.2.0.1.0 Production on Thu May 17 19:16:52 2012
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show user
USER is "OPS$ERIN"
SQL>
--erin使用者不需要輸入使用者名稱和密碼就能登入資料庫，這是外部身份驗證的一種(作業系統身份驗證)，只要erin使用者能登

錄作業系統就能登入資料庫，這與as sysdba身份的作業系統身份驗證稍有不同，以as sysdba身份登入資料庫的作業系統

使用者需要加入DBA組

su - oracle切換到oracle使用者，去掉oracle的suid屬性
[oracle@mylinux ~]$ cd /u01/oracle/bin
[oracle@mylinux bin]$ ll oracle
-rwsr-s--x 1 oracle oinstall 173515991 Mar 13 05:39 oracle
[oracle@mylinux bin]$ chmod -s oracle
[oracle@mylinux bin]$ ll oracle
-rwxr-x--x 1 oracle oinstall 173515991 Mar 13 05:39 oracle

su - erin
[erin@mylinux ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Thu May 17 19:23:23 2012
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
SQL> conn /
ERROR:
ORA-12547: TNS:lost contact
發現賬戶erin就不能正常連線資料庫了

下面試驗一下remote_os_authent,在遠端資料庫上以作業系統驗證來登入
我的客戶端是一臺windows機器
電腦名是windows，使用者名稱是administrator
SQL> conn /as sysdba
Connected.
SQL> create user ops$administrator identified externally;
User created.
SQL> grant create session to ops$administrator;
Grant succeeded.
SQL> show parameter remote_os_authent;--remote_os_authent是靜態引數，修改需要重啟例項
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_os_authent                    boolean     FALSE
SQL> alter system set remote_os_authent=true scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance--重啟的時候提示有引數已經廢棄的或不贊成使用的開啟了
ORACLE instance started.
Total System Global Area  497995776 bytes
Fixed Size                  1337464 bytes
Variable Size             373294984 bytes
Database Buffers          117440512 bytes
Redo Buffers                5922816 bytes
Database mounted.
Database opened.
SQL> show parameter remote_os_authent;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_os_authent                    boolean     TRUE

檢視聯機文件reference：
The REMOTE_OS_AUTHENT parameter is deprecated. It is retained for backward compatibility only.
--說明這個引數已經廢棄，只是為了向後相容

在windows客戶端
C:\Documents and Settings\Administrator>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 5月 17 19:37:59 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn /@dedicated
已連線。
SQL> show user
USER 為 "OPS$ADMINISTRATOR"

遠端連線成功

(
ORACLE_SID=orcl--例項名大小寫很有關係的哦，我當時就是寫錯了，把小寫寫成了大寫，一直出以下的錯誤
SQL> conn hr/hr
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory
Process ID: 0
Session ID: 0 Serial number: 0

TOM大師在Expert.Oracle.Database.Architecture這本書中的論述，描述了只有通過ORACLE_SID和ORACLE_HOME的雜湊值才

能找到共享記憶體（SGA），所以如果ORACLE_SID和ORACLE_HOME設定錯了自然會出現ORA-27101: shared memory realm does

not exist這個錯誤嘍
For those who are unfamiliar with the term SID or ORACLE_SID, a full definition is called for. The SID is a
site identifier. It and ORACLE_HOME (where the Oracle software is installed) are hashed together in UNIX to

create a unique key name for creating or attaching a Shared Global Area (SGA) memory region. If your ORACLE_SID or
ORACLE_HOME is not set correctly, you’ll get the ORACLE NOT AVAILABLE error, since you can’t attach to a

shared memory segment that is identified by this unique key. On Windows, shared memory isn’t used in the same

fashion as on UNIX, but the SID is still important. You can have more than one database under the same ORACLE_HOME,

so you need a way to uniquely identify the instance associated with each one, along with their configuration files.
)

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