advertisements
_____________________________________________________________________________________________________________________
password_versions column in user$ table can have 3 types of values.
A value as 10G means the user is created in 10g and migrated to 11g version and password is never changed in 11g version.
A value as 11G represent the user password modified with alter user <username> identified by values ‘spare4’; command.
A value 10G 11G means the user is migrated from 10g and password is modified after migration.
Oracle 11g needs both password and spare4 column(from user$) values to restore the password if the password_version column is 10G 11G. Also, you have to use alter user identified by values ‘spare4;password’; from user$ for resetting the password for temporary purpose.
See some examples below.
SQL> create user test identified by test;
User created.
SQL> SELECT USERNAME,PASSWORD_VERSIONS FROM DBA_USERS where username in ('SYS','SCOTT','TEST');
USERNAME PASSWORD
------------------------------ --------
SYS 10G
TEST 10G 11G
SCOTT 10G 11G
1* select name,password, spare4 from user$ where name in ('SYS','SCOTT','TEST')
SQL> /
NAME PASSWORD SPARE4
---------- ------------------------------ ----------------------------------------------------------------------
SCOTT DB1B37F84BDF15E6 S:5F4B078C2B8AB61008043DAE73D11462E07E4BDF14239B60D55FD1D60F67
SYS C07840DD9FAD7791
TEST 7A0F2B316C212D67 S:79FFC530B4C86EB6796799D752CBF6B99D75190A88A5B11D55AB00A85464
SQL> ALTER USER TEST IDENTIFIED BY VALUES 'S:CE4704C5679090A3B41B74208E72996585656BB133EADA82ED1AC7AE72F1';
User altered.
SQL> SELECT USERNAME,PASSWORD_VERSIONS FROM DBA_USERS where username in ('SYS','SCOTT','TEST');
USERNAME PASSWORD
------------------------------ --------
SYS 10G
TEST 11G
SCOTT 10G 11G
SQL> connect test/test
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
$ sqlplus "/ as sysdba"
SQL*Plus: Release 11.1.0.7.0 - Production on Tue Jul 26 01:20:50 2011
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> ALTER USER TEST IDENTIFIED BY VALUES 'S:CE4704C5679090A3B41B74208E72996585656BB133EADA82ED1AC7AE72F1;7A0F2B316C212D67';
User altered.
SQL> connect test/test
Connected.
SQL> connect / as sysdba
Connected.
SQL> SELECT USERNAME,PASSWORD_VERSIONS FROM DBA_USERS where username in ('SYS','SCOTT','TEST');
USERNAME PASSWORD
------------------------------ --------
SYS 10G
TEST 10G 11G
SCOTT 10G 11G
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment