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;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - 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;

DBMS_METADATA.GET_DDL('USER','COSKAN')
------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------

   CREATE USER "COSKAN" IDENTIFIED BY 
VALUES 'S:1F0648E7E665F0A0EE44B1E9BD4B626A77CA25B376A49177F9E97DF98BFA;26EB15F771A78542'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"


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

On the User session —try connection

SQL> connect coskan/oracle
Connected.
SQL>

DBA Session —change user password

SQL> alter user coskan identified by msssql;

User altered.

User Session —connect with new password

SQL> connect coskan/msssql
Connected.

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
Connected.
SQL>

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 http://www.freelists.org/post/oracle-l/11202-setting-password I think I need to mention that you need to have “SET LONG 999999″ setting for your sqlplus env

About these ads

12 Comments »

  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.

    Thanks,
    Sasi

    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
    Uwe

    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
    greatly.

    Comment by support.gigatms.com.tw — 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:

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

The Silver is the New Black Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 199 other followers

%d bloggers like this: