Search This Blog

Tuesday, May 12, 2015

How to change/restore user password in 11G database

The change to DBA_USERS is the result of a security enhancement, it was no longer appropriate to show the password hashes in the DBA_USERS view as it may cause undesired exposure when access to this view is needed by 'unprivileged' users. This feature coincides with the introduction of the new hash algorithm, which is stored differently as compared to the visible hash in earlier releases anyway

In 11g if you query password field, it will return NULL. 

SQL> select USERNAME,PASSWORD,PASSWORD_VERSIONS from dba_users where USERNAME in ('SYSTEM');

USERNAME                       PASSWORD                       PASSWORD
------------------------------ ------------------------------ --------
SYSTEM                                                        10G 11G


Here are the steps to change/rollback user passwords in 11G

SQL>  select spare4 from user$ where name='SYSTEM';

SPARE4
--------------------------------------------------------------------------------
S:FEB2B0DFDF35B910BBEB312E86A2460C23FF77317DB57D16BB564A67D717

SQL> alter user SYSTEM identified by manager;

User altered.

SQL> conn SYSTEM/manager;
Connected.
SQL>
SQL> alter user SYSTEM identified by values 'S:FEB2B0DFDF35B910BBEB312E86A2460C23FF77317DB57D16BB564A67D717';

User altered.

SQL> conn SYSTEM/manager;
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> conn SYSTEM/Syssoa;
Connected.


Reference : User Passwords Are No Longer Visible In DBA_USERS As Of 11g (Doc ID 735651.1)

No comments:

Post a Comment

Transportable tablespace refresh

  1.check tablespace for the user which need to refresh -------------------------------------------------------------------  SQL> select ...