Oracle 11g Password : Relation between user$, DBA_USERS, PASSWORD_VERSIONS, spare4, IDENTIFIED BY VALUES

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
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google