密碼區分大小寫

Ora-01017 When Connecting As Sysoper/Sysdba To A 11g Database (Doc ID 1101584.1)
也是從11.2開始：
在10G 中是沒有這種情況的
例10G:     -- 不區分大小寫
SQL> create user tt identified by TT
  2  ;


User created.


SQL> grant create session to tt;


Grant succeeded.


SQL> conn tt/tt
Connected.
SQL> conn tt/TT
Connected.
SQL> select * from v$version;


BANNER
--------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production




11G:    ---預設區分大小寫
SQL> create user tt identified by TT
  2  ;


使用者已建立。


SQL> grant create session to tt;


授權成功。


SQL> conn tt/tt
ERROR:
ORA-01017: invalid username/password; logon denied




警告: 您不再連線到 ORACLE。
SQL> conn tt/TT
已連線。
SQL> select * from v$version; 


BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production


從文件 How To Check The Value For the Parameter IGNORECARE In The Password File (Doc ID 1379947.1) 中也可以看出來 。
此文件中也介紹了在12C時 檢視是密碼檔案是不是區分大小寫的方法：
[oracle@localhost dbs]$ cd $ORACLE_HOME/dbs
[oracle@localhost dbs]$ orapwd describe file=orapw$ORACLE_SID
Password file Description : format=12 ignorecase=N




APPLIES TO:


Oracle Database - Enterprise Edition - Version 11.2.0.2 to 12.1.0.1 [Release 11.2 to 12.1]
Information in this document applies to any platform.
GOAL


How to check if the password file was created using


orapwd file=orapwTEST01 password=Welc0me1 ignorecase=n


or


orapwd file=orapwTEST01 password=Welc0me1 ignorecase=y






SOLUTION


Up to version 11gR2:


Before 12c there is no any easy way to check if ignorecase=n or ignorecase=y were used to create the password file, since the password file is encrypted and the information is not available in the database.


The only way to check it,  is to test it using different passwords:


sqlplus sys/manager@PROD as sysdba
Connected.




sqlplus sys/MANAGER@PROD as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied






Since it is obvious that the case of the password matters it is quite clear that the password file was created using the following command:


 


orapwd file=orapwPROD password=manager ignorecase=n


If the password file would have been created with the following command then the case of the letters in the password would not have been important:


orapwd file=orapwPROD password=manager ignorecase=y


 Both sqlplus connections work fine:




sqlplus sys/manager@PROD as sysdba -> connected
sqlplus sys/MANAGER@PROD as sysdba -> connected






Versions as of 12c


Since version 12.1.0.1.0 the followng can be used to check the passwordfile using orapwd describe :


[oracle@localhost dbs]$ cd $ORACLE_HOME/dbs
[oracle@localhost dbs]$ orapwd describe file=orapw$ORACLE_SID
Password file Description : format=12 ignorecase=N
















Ora-01017 When Connecting As Sysoper/Sysdba To A 11g Database (Doc ID 1101584.1)




In this Document
  Symptoms
  Cause
  Solution
  References


APPLIES TO:


Oracle Server - Enterprise Edition - Version: 11.1.0.7 and later   [Release: 11.1 and later ]
Information in this document applies to any platform.
SYMPTOMS


When attempting to connect as sysoper/sysdba with a new user the following error occurs.
試圖用新sysoper/sysdba 使用者登陸時 出現 1017




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




The issue can be reproduced with the following steps:




SQL> create user TEST identified by MANAGER;
SQL> grant connect, sysoper, dba to TEST;
SQL> connect TEST/manager@V11G as sysoper


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




CAUSE


The Password file is case sensitive. It was created by default using ignorecase=n.
密碼檔案是大小寫敏感的的，建立時預設是 ignorecase=n 不忽略大小寫
SOLUTION
解決方法 ：
1) Change user's password and make sure that the letters are all lowercase
改使用者密碼，都是小寫的
or


2) Re-create the password file using
重新建 密碼檔案 加上 ignorecase=y 
orapwd file=orapw password= entries= ignorecase=y 

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