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



  1. s/e/c/ in my last name 😉

    Comment by laurentschneider — March 13, 2009 @ 9:11 am

  2. Ooops sorry for that. Checked 5 times before I wrote, and still wrong 🙂

    Comment by coskan — March 13, 2009 @ 10:38 am

  3. Tks for the tip this procedure is really a save life

    Comment by Nomura — January 6, 2010 @ 11:35 pm

  4. Hi Coskan, its interesting and is the easiest option.


    Comment by Sasidhar B — August 10, 2011 @ 6:47 am

  5. Hi,
    The table sys.user$ contains the hashed password as well.

    Comment by Mette Evert — May 16, 2012 @ 9:12 am

    • problem is you will be needing sysdba where not everybody has got access

      Comment by coskan — May 16, 2012 @ 7:02 pm

  6. […] ~]$ sqlplus /nolog @ > connect prueba/prueba Conectado. Fuente Be Sociable, Share! Tweet […]

    Pingback by Tips DBA, Linux and Others Things » Cambio temporal del password a un USER — September 8, 2012 @ 1:45 pm

  7. […] ~]$ sqlplus /nolog @ > connect prueba/prueba Conectado. Fuente Oracle […]

    Pingback by TIPS DBA, LINUX AND OTHERS THINGS » Cambio temporal del password a un USER — September 26, 2012 @ 7:00 pm

  8. Cool! I wouldn’t have thought about that approach, but it is indeed much easier than the old way with USER$ Thank you for sharing it, Coskan!
    Kind regards

    Comment by Uwe Hesse — February 21, 2013 @ 10:25 am

  9. Allah razı olsun Coşki! 😉

    Comment by Mehmet Bekir Birden — July 17, 2013 @ 8:17 am

  10. Yalnız bir de script’e şunu eklemek şukela olur; set long 90000

    Comment by Mehmet Bekir Birden — July 17, 2013 @ 8:21 am

  11. whoah this blog is excellent i love reading your articles.
    Keep up the great work! You understand, a lot of individuals
    are searching round for this information, you can aid them

    Comment by — May 15, 2014 @ 1:27 am

RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

Create a free website or blog at

%d bloggers like this: