Oracle 11g restores history password using ALTER user identified by values password

Prior to 11.1, many people might have known that Oracle database history passwords could be restored through alter user identified by values password, but several problems occurred in 11g:
1. Dba_users no password record (null value), this problem can be directly queried User$.password still have records
Sql> Select password from dba_users where username= ' SYS ';

PASSWORD
------------------------------


Sql> Select password from user$ WHERE name= ' SYS ';

PASSWORD
------------------------------
8a8f025737a9097a
2. In the beginning of the 11.1 user$ in the SPARE4 has a value, which makes the database password case-sensitive, reference blog: About Oracle 11G password case sensitive conjecture (user$. SPARE4)
Sql> Select SPARE4 from user$ WHERE name= ' SYS ';

SPARE4
--------------------------------------------------------------------------------
S:c7c81bbe7760b5bbb3973f0971aa36c737bf6dcc4a34fe925ce70b0739bd
There is now a question as to how to restore the Oracle database user history password in the 11G version. , here the test proves that alter user identified by values can be User$.password or user$. SPARE4, but the two differ in the size of the password, the specific test is as follows:

Create a test user Xifenfei
[Oracle@localhost ~]$ SS

Sql*plus:release 11.2.0.4.0 Production on Fri APR 10 16:00:03 2015

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


Connected to:
Oracle Database 11g Enterprise Edition release 11.2.0.4.0-64bit Production
With the partitioning, OLAP, Data Mining and real application testing options

Sql> select * from V$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition release 11.2.0.4.0-64bit Production
Pl/sql Release 11.2.0.4.0-production
CORE 11.2.0.4.0 Production
TNS for Linux:version 11.2.0.4.0-production
Nlsrtl Version 11.2.0.4.0-production

Sql> Select Sysdate "www.xifenfei.com" from dual;

Www.xifen
---------
10-apr-15

Sql> create user Xifenfei identified by Oracle;

User created.

Sql> Grant create session to Xifenfei;

Grant succeeded.

Sql> Conn Xifenfei/oracle
Connected.
Sql> Conn Xifenfei/oracle
ERROR:
Ora-01017:invalid Username/password; Logon denied


Warning:you are no longer connected to ORACLE.
Sql> Conn/as SYSDBA
Connected.
Sql> Show parameter Sec_case_sensitive_logon;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Sec_case_sensitive_logon Boolean TRUE
Here because the Sec_case_sensitive_logon parameter defaults to True, the password is case-sensitive

Modify Database Password
Sql> Select Spare4,password from user$ where name= ' Xifenfei ';

SPARE4
--------------------------------------------------------------------------------
PASSWORD
------------------------------
s:6e34e993900317bbfd6289e4ae619d634aa6ad804c765a3dee1ccabcc50d
1ba871fa3b1c3f45


sql> alter user Xifenfei identified by Xifenfei;

User altered.

Sql> Select Spare4,password from user$ where name= ' Xifenfei ';

SPARE4
--------------------------------------------------------------------------------
PASSWORD
------------------------------
S:a75a184ea2767488e698c443e97cb2473b46a9c80c2c61833ba867cb8b17
1682caa2339f770f


Sql> Conn Xifenfei/xifenfei
Connected.
Sql> Conn Xifenfei/xifenfei
ERROR:
Ora-01017:invalid Username/password; Logon denied


Warning:you are no longer connected to ORACLE.
Sql> Conn Xifenfei/oracle
ERROR:
Ora-01017:invalid Username/password; Logon denied
This modifies the Xifenfei user's password from Oracle to Xifenfei

Try values User$.password restore previous password
Sql> Conn/as SYSDBA
Connected.
sql> alter user Xifenfei identified by values ' 1ba871fa3b1c3f45 ';

User altered.

Sql> Select Spare4,password from user$ where name= ' Xifenfei ';

SPARE4
--------------------------------------------------------------------------------
PASSWORD
------------------------------

1ba871fa3b1c3f45


Sql> Conn Xifenfei/oracle
Connected.
Sql> Conn Xifenfei/oracle
Connected.
Sql> Conn Xifenfei/xifenfei
ERROR:
Ora-01017:invalid Username/password; Logon denied


Warning:you are no longer connected to ORACLE.
After restoring the last password in this way, user$ is found. SPARE4 is NULL, which makes Oracle no longer sensitive to password capitalization.

Try values user$.spare4 restore previous password
Sql> Conn/as SYSDBA
Connected.
sql> alter user Xifenfei identified by Xifenfei;

User altered.

Sql> Select Spare4,password from user$ where name= ' Xifenfei ';

SPARE4
--------------------------------------------------------------------------------
PASSWORD
------------------------------
S:48a11864ad633e904126c20e8c374a4aa45d87bb005d35ad2b10766e8e11
1682caa2339f770f


Sql> Conn Xifenfei/xifenfei
Connected.
Sql> Conn Xifenfei/oracle
ERROR:
Ora-01017:invalid Username/password; Logon denied


Warning:you are no longer connected to ORACLE.
Sql> Conn Xifenfei/xifenfei
ERROR:
Ora-01017:invalid Username/password; Logon denied


sql> alter user Xifenfei identified by values ' 6e34e993900317bbfd6289e4ae619d634aa6ad804c765a3dee1ccabcc50d ';
Sp2-0640:not connected
Sql> Conn/as SYSDBA
Connected.
sql> alter user Xifenfei identified by values ' 6e34e993900317bbfd6289e4ae619d634aa6ad804c765a3dee1ccabcc50d ';
Alter user Xifenfei identified by values ' 6e34e993900317bbfd6289e4ae619d634aa6ad804c765a3dee1ccabcc50d '
*
ERROR at line 1:
Ora-00600:internal error code, arguments: [Kzsviver:2], [], [], [], [], [],
[], [], [], [], [], []
--Write less S: Direct report ORA-600 error, suspect S: is some kind of identification of spare4 column

Sql>
Sql>
Sql>
sql> alter user Xifenfei identified by values ' s:6e34e993900317bbfd6289e4ae619d634aa6ad804c765a3dee1ccabcc50d ';

User altered.

Sql> Select Spare4,password from user$ where name= ' Xifenfei ';

SPARE4
--------------------------------------------------------------------------------
PASSWORD
------------------------------
s:6e34e993900317bbfd6289e4ae619d634aa6ad804c765a3dee1ccabcc50d

Sql> Conn Xifenfei/oracle
Connected.
Sql> Conn Xifenfei/oracle
ERROR:
Ora-01017:invalid Username/password; Logon denied


Warning:you are no longer connected to ORACLE.
Sql> Conn Xifenfei/xifenfei
ERROR:
Ora-01017:invalid Username/password; Logon denied
It is found that after restoring the previous password through values User$.spare4, the User$.password column is empty, but the password is still case-sensitive. As can be seen here, the User$.password item may be canceled later, for compatibility, so Oracle remains in subsequent versions.

About restoring the previous password operation summary in Oracle 11G
1. After restoring the previous password through values User$.password, case-insensitive
2. After restoring the previous password through values User$.spare4, case sensitive
3. There are two ways to achieve 11g restore the previous password, but the recommended use of User$.spare4 value modification