My Journey to the cloud…

I got a request from support team to reset the DBA user password as they were getting some error due to password expiration.
However support person was not aware of the password.

SQL> select USERNAME,ACCOUNT_STATUS,EXPIRY_DATE
  2  from dba_users
  3  where USERNAME = 'PEOPLE';

USERNAME
--------------------------------------------------------------------------------
ACCOUNT_STATUS
--------------------------------------------------------------------------------
EXPIRY_DATE
---------------
PEOPLE
EXPIRED
03-NOV-14

Oracle 11g brought several security enhancements, as it is well known by the 11g users. On previous Oracle versions it was possible to query the DBA_USERS PASSWORD column to get the hashed password string. It was useful when someone tried to temporarily reset the user’s password and restore it to its original value without actually knowing it.
The command:

ALTER USER IDENTIFIED BY VALUES ‘F28740221A2D9A70’;

it could take the hashed value from the DBA_USERS data dictionary view. However starting with Oracle 11g this column is null … so where are we supposed to take this hashed value from?.

SQL> SELECT USERNAME, PASSWORD
2 FROM DBA_USERS
3 WHERE USERNAME='PEOPLE';

USERNAME PASSWORD
--------------- ------------------------------
PEOPLE 

Starting with Oracle 11g, there is a view called SYS.USER$ which stores the PASSWORD, the way it was stored prior to 11g.

Oracle 11g only makes it a little bit more difficult to get the hashed password, but if you login with “SYS” account, you can still apply the conventional (prior to 11g method) to temporarily reset the password, and still have access to the hashed password.

SQL> SELECT NAME, PASSWORD
FROM SYS.USER$
WHERE NAME = 'PEOPLE'   2    3  ;

NAME
--------------------------------------------------------------------------------
PASSWORD
--------------------------------------------------------------------------------
PEOPLE
F28740221A2D9A70

And the command used to reset the password

SQL> alter user PEOPLE identified by values 'F28740221A2D9A70';

User altered.

SQL> select USERNAME,ACCOUNT_STATUS,EXPIRY_DATE
  2  from dba_users
  3  where USERNAME = 'PEOPLE';

USERNAME
--------------------------------------------------------------------------------
ACCOUNT_STATUS
--------------------------------------------------------------------------------
EXPIRY_DATE
---------------
PEOPLE
OPEN
14-JUN-15