Home > Oracle Database > PASSWORD column NULL in DBA_USERS from 11g onwards

PASSWORD column NULL in DBA_USERS from 11g onwards

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
Advertisements
Categories: Oracle Database Tags: ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: