隨著oracle不在退出10g的patch,越來越多的朋友升級後遇到密碼的問題，這個測試不是最近做的，但還是有朋友不斷的遇到，發出來共享下！

升級前：

14:02:05 sys@myora>create tablespace test datafile '+DATA/oradata/myora/test.dbf' size 100M autoextend on next 50M;

Tablespace created.

Elapsed: 00:00:00.60

14:03:27 sys@myora>create user test default tablespace test identified by TEST;

User created.

Elapsed: 00:00:00.08

14:04:22 sys@myora>set linesize 1000 pagesize 1000

14:04:38 sys@myora>select username,password from dba_users;

USERNAME PASSWORD

------------------------------ ------------------------------

SYSTEM 2D594E86F93B17A1

SYS 8A8F025737A9097A

ORACLE_OCM 6D17CF1EB1611F94

TEST 7A0F2B316C212D67

DBSNMP E066D214D5421CCC

DIP CE4A36B8E06CA59C

OUTLN 4A3BA55E08595C81

TSMSYS 3DF26A8B17D0F29F

8 rows selected.

Elapsed: 00:00:00.02

create user test default tablespace test identified by tesT;

14:05:33 sys@myora>create user test default tablespace test identified by tesT;

User created.

Elapsed: 00:00:00.02

14:05:59 sys@myora>select username,password from dba_users;

USERNAME PASSWORD

------------------------------ ------------------------------

SYSTEM 2D594E86F93B17A1

SYS 8A8F025737A9097A

ORACLE_OCM 6D17CF1EB1611F94

TEST 7A0F2B316C212D67

DBSNMP E066D214D5421CCC

DIP CE4A36B8E06CA59C

OUTLN 4A3BA55E08595C81

TSMSYS 3DF26A8B17D0F29F

8 rows selected.

Elapsed: 00:00:00.00

這裡可以看出，10g設定密碼是，儲存的都是一樣的。

14:06:06 sys@myora>grant connect,resource to test;

Grant succeeded.

Elapsed: 00:00:00.04

升級後

[17:35:29 oracle@david_pri ~]$ sql

SQL*Plus: Release 11.2.0.3.0 Production on Fri Oct 11 17:35:31 2013

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

SQL> conn test/test

Connected.

SQL> connect test/TEST

Connected.

SQL> conn test/tesT

Connected.

SQL>

可以看出升級後，密碼仍是不區分大小寫的。

以下是oracle官方給出的解釋，意思是新建使用者，或者修改了賬戶密碼，就會區分大小寫

In previous releases of Oracle Database, passwords were not case sensitive. If you import user accounts from a previous release, for example, Release 10g, into the current database release, the case-insensitive passwords in these accounts remain case insensitive until the user changes his or her password. If the account was granted SYSDBA or SYSOPER privilege, it is imported to the password file. (See "How Case Sensitivity Affects Password Files" for more information.) When a password from a user account from the previous release is changed, it then becomes case sensitive.

You can find users who have case sensitive or case insensitive passwords by querying the DBA_USERS view. The PASSWORD_VERSIONS column in this view indicates the release in which the password was created. For example:

SELECT USERNAME,PASSWORD_VERSIONS FROM DBA_USERS;

USERNAME PASSWORD_VERSIONS

------------------------------ -----------------

JONES 10G 11G

ADAMS 10G 11G

CLARK 10G 11G

PRESTON 11G

BLAKE 10G

The passwords for accounts jones, adams, and clark were originally created in Release 10g and then reset in Release 11g. Their passwords, assuming case sensitivity has been enabled, are now case sensitive, as is the password for preston. However, the account for blake is still using the Release 10g standard, so it is case insensitive. Ask him to reset his password so that it will be case sensitive, and therefore more secure.

我們可以先看一下升級後密碼相關資訊及修改密碼後相關情況。

SQL> SELECT USERNAME,PASSWORD_VERSIONS FROM DBA_USERS;

USERNAME PASSWORD

------------------------------ --------

OUTLN 10G

TEST 10G

SYS 10G

SYSTEM 10G

APPQOSSYS 10G 11G

DIP 10G

DBSNMP 10G

ORACLE_OCM 10G

8 rows selected.

SQL> alter user test identified by tesT;

User altered.

SQL> SELECT USERNAME,PASSWORD_VERSIONS FROM DBA_USERS;

USERNAME PASSWORD

------------------------------ --------

OUTLN 10G

TEST 10G 11G

SYS 10G

SYSTEM 10G

APPQOSSYS 10G 11G

DIP 10G

DBSNMP 10G

ORACLE_OCM 10G

8 rows selected.

SQL> conn test/test;

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL> conn test/tesT

Connected.

結論是：升級資料庫的時候不需要擔心密碼大小些問題，但在修改密碼或者新建賬戶時，需要關注賬號密碼大小些問題。
當然，可以關掉這個密碼大小寫功能，修改sec_case_sensitive_logon 為false即可。

10g升級到11g密碼問題

相關文章