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

The Silver is the New Black Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 203 other followers