oracle passwordfile作用 &11g改進

檔案位置在
主要用作sysdba遠端登陸
[oracle@vm11g ~]$ sqlplus / as sysdba 這個是作業系統驗證 不需要password file

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Apr 14 06:58:57 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

cd $ORACLE_HOME/dbs

[oracle@vm11g dbs]$ ls orapw*
orapwxh

[oracle@vm11g dbs]$ more orapwxh

ORACLE Remote Password file

檔案中存放
sysdba/sysoper 使用者的使用者名稱和密碼

與之有關係的資料庫引數
remote_login_passwordfile   
說明: 指定作業系統或一個檔案是否檢查具有許可權的使用者的口令。如果設定為 NONE, Oracle 將忽略口令檔案。如果設定為EXCLUSIVE, 將使用資料庫的口令檔案對每個具有許可權的使用者進行驗證。如果設定為 SHARED, 多個資料庫將共享
SYS 和 INTERNAL 口令檔案使用者。
簡單理解就是空駛sysdba/sysoper遠端登陸的（他們有down,startup  database的最高權利）
NODE就是不允許遠端sysdba/sysoper 登陸

 

遠端sysdba 登陸
SQL> show parameter remote_login_pass

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE
SQL>

SQL> conn / as sysdba
Connected.
SQL> grant sysdba to xh;

Grant succeeded.

 

sqlplus xh/a123@test as sysdba（遠端登陸 就需要password file了）
SQL*Plus: Release 10.2.0.4.0 - Production on Sun Apr 25 22:00:06 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> show user
USER is "SYS"

 

SQL> alter system set remote_login_passwordfile=NONE scope=spfile;

System altered.

SQL> startup force

SQL> show parameter remote_log

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      NONE

 

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 (with complications)
[oracle@ora10g ~]$ sqlplus xh/a123@test as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Sun Apr 25 21:56:48 2010

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

ERROR:
ORA-01017: invalid username/password; logon denied

none就不允許遠端sysdba登陸了

 

SQL> alter system set remote_login_passwordfile=exclusive scope=spfile;

System altered.

SQL> startup force

[oracle@vm11g dbs]$ rm -rf orapwxh
[oracle@vm11g dbs]$

SQL> select * from v$pwfile_users;（11g server）

no rows selected

[oracle@ora10g ~]$ sqlplus xh/a123@test as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Sun Apr 25 22:02:43 2010

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

ERROR:
ORA-01031: insufficient privileges

Enter user-name:

重新建立passwordfile
[oracle@vm11g dbs]$ orapwd
Usage: orapwd file= password= entries= force= ignorecase= nosysdba=

  where
    file - name of password file (required),
    password - password for SYS (optional),~~user sys password
    entries - maximum number of distinct DBA (required),
    force - whether to overwrite existing file (optional),~~~~~~~~~覆蓋原有的passwd file
    ignorecase - passwords are case-insensitive (optional),~~~是否區分密碼中大小寫(11g新增,預設區分大小寫，=y後不區分了)
    nosysdba - whether to shut out the SYSDBA logon (optional Database Vault only). 與DATABASE VAULT 有關係若為true 就等於VAULT中禁止了SYSDBA許可權通過密碼檔案驗證方式登陸資料庫
   There must be no spaces around the equal-to (=) character.
[oracle@vm11g dbs]$

  entries： passwordfile中可以存放的最大使用者數，對應於允許以SYSDBA/SYSOPER許可權登入資料庫的最大使用者數，如果使用者數超過這個值只能重建口令檔案，增大entries

[oracle@vm11g dbs]$ orapwd file=orapwxh password=a123456 ignorecase=y

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE

 

[oracle@ora10g ~]$ sqlplus xh/a123@test as sysdba（client）

SQL*Plus: Release 10.2.0.4.0 - Production on Sun Apr 25 22:23:25 2010

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

ERROR:
ORA-01031: insufficient privileges

 

SQL> grant sysdba to xh;（server 由於新建立的裡面只有使用者sys,其他的還得手工grant sysdba新增進去）

Grant succeeded.

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE
XH                             TRUE  FALSE FALSE

[oracle@ora10g ~]$ sqlplus xh/A123@test as sysdba    (不區分大小寫了)

SQL*Plus: Release 10.2.0.4.0 - Production on Sun Apr 25 22:24:36 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> show user
USER is "SYS"

 

關於remote_login_passwordfile = shared 意思就是同一個機器上的db可以共享同一個 password file,不允許grant sysdba to xx了）另外剛才的orapwxh已經無法共享受因為格式是orapwd
所以要建立的名字為orapw就可以共享了（另外orapw也可以被其他exclusive的db共享，但密碼檔案裡除去sys使用者不能有其他user）

SQL> alter system set remote_login_passwordfile=shared scope=spfile;

System altered.

SQL> startup force

SQL> grant sysdba to zz;
grant sysdba to zz
*
ERROR at line 1:
ORA-01999: password file cannot be updated in SHARED mode

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE
XH                             TRUE  FALSE FALSE

 

 

#另外補充在rac中修改擁有sys使用者密碼，都會修改orapw 但這個是在本地disk上，所以不會修改其它node，解決就是每個node都修改,或者password放在共享儲存上,ln從本地到共享儲存

 

 

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