Oracle 11g New Feature password security dba_users password column is null and stored in user$

Oracle 11g released with lot of enhanced security features. In prior versions of Oracle the hashed passwords were stored in dba_users table password column. So in those versions (10g and below) users can temporarily reset (in case if you forgot the password or unknown) the user passwords by updating the password column in dba_users table.
Method
  • Get the current value of the password from dba_users table and save it
SQL> select username, passWORD  from dba_users where username='SYSTEM';

USERNAME                       PASSWORD
------------------------------ ------------------------------
SYSTEM                         29E4274345D065E4
  • Reset the password for temporary purpose using “alter user system identified by <temp password>;” and do your activities with the system user with new password
  • Reset back to the old password by using
Alter user system identified by values ‘29E4274345D065E’;
          In Oracle 11g the password column of dba_users table is null. Then, where is the encrypted password? Yes. It is there in password column of sys.user$ table. 11g makes one more level of security for the passwordJ.
SQL> select name, password from sys.user$
  2  where name ='SYSTEM';

NAME                           PASSWORD
------------------------------ ------------------------------
SYSTEM                         29E4274345D065E4

How to perform the temporary password reset in Oracle 11g?
          We can perform the temporary password reset in Oracle 11g also. Please find the method below.

Method
  1. Select the user password from sys.user$ table.
SQL> select name, password from sys.user$
  2  where name ='SYSTEM';

NAME                           PASSWORD
------------------------------ ------------------------------
SYSTEM                         29E4274345D065E4

  1. Reset the user password with the temp password

alter user system identified by <temp password>;

  1. Reset back the user password to the original old value

Alter user system identified by values ‘29E4274345D065E’;

Note: You can get the user creation script including the encrypted password by using DBMS_METADATA.GET_DDL package.
select dbms_metadata.get_ddl('USER','SYSTEM') FROM DUAL;


DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google