Tuesday, January 18, 2011

Where is the password column from DBA_USERS in 11g?

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 IDENTIFID BY VALUES '14C785FC66029BF9';

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='SYSTEM';

USERNAME PASSWORD
--------------- ------------------------------
SYSTEM
<>


By taking a look at the underlying data dictionary table where the DBA_USERS view is built on, we can easily find the data dictionary table is SYS.USER$, and it has a column named ... guess ... PASSWORD.

Oracle 11g only makes it a little bit more difficult to get the hashed password, but if you have enough privileges you can still apply the traditional procedure to temporarily reset the password, and still have access to the hashed password.

SQL> SELECT NAME, PASSWORD
FROM SYS.USER$
WHERE NAME = 'SYSTEM' ;

NAME PASSWORD
------------------------------ ------------------------------
SYSTEM 2D594E86F93B17A1


SQL> ALTER USER SYSTEM IDENTIFIED BY tempPasswd;
User altered.

SQL> connect system/tempPasswd@orcl;
Connected.

SQL> ALTER USER SYSTEM IDENTIFIED BY VALUES '2D594E86F93B17A1'
User altered.

SQL> connect system/manager@orcl
Connected.