ITPub博客

首页 > 数据库 > Oracle > 10g升级到11g密码问题

10g升级到11g密码问题

原创 Oracle 作者:yangzhangyue 时间:2013-12-20 11:45:11 0 删除 编辑
随着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

 

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

 

 

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即可。


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29033984/viewspace-1063863/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-07-09

  • 博文量
    36
  • 访问量
    219937