Coskan’s Approach to Oracle

March 11, 2009

Alter user identified by values on 11G without using SYS.USER$

Filed under: Security, Tips — coskan @ 11:13 am

You probably know that on Oracle 11G, hash values of passwords, are no longer shown in dba_users table, so to change the password temporarily, it is not possible to use the way I explained here before.

On Oracle 11G, there is one method posted by Laurent Schneider which is, to use SYS.USER$ table to extract hash value of the password.

Another method, I tried and got success, is using DBMS_METADATA.GET_DDL function without going to any other dictionary tables.

DBA Session — check the output of DBMS_METADATA.GET_DDL

SQL> select * from v$version;

Oracle Database 11g Enterprise Edition Release - Production
PL/SQL Release - Production
CORE      Production
TNS for 32-bit Windows: Version - Production
NLSRTL Version - Production

SQL> drop user coskan cascade;

User dropped.

SQL> create user coskan identified by oracle;

User created.

SQL> grant create session to coskan;

Grant succeeded.

SQL>  select dbms_metadata.get_ddl('USER','COSKAN') from dual;


VALUES 'S:1F0648E7E665F0A0EE44B1E9BD4B626A77CA25B376A49177F9E97DF98BFA;26EB15F771A78542'

As you see gathering DDL of the user gives us the hash value

On the User session —try connection

SQL> connect coskan/oracle

DBA Session —change user password

SQL> alter user coskan identified by msssql;

User altered.

User Session —connect with new password

SQL> connect coskan/msssql

DBA Session — Update the user password with the value you gathered by DBMS_METADATA.GET_DDL

SQL> alter user coskan identified by
  2  VALUES 'S:1F0648E7E665F0A0EE44B1E9BD4B626A77CA25B376A49177F9E97DF98BFA;26EB15F771A78542';

User altered.

User Session — Try the old password

SQL> connect coskan/oracle

I think this option is a bit easier than extracting from table approach.

Update-01/12/11 After I read the question on this oracle-l question I think I need to mention that you need to have “SET LONG 999999” setting for your sqlplus env

Create a free website or blog at