How to change user's password in oracle 11g

Summary :-
As a DBA you may need to log in as another user, for example to test an application after
doing some workarounds to solve a problem. But you don't know the password and you don't want
to wait. You can change the password, as dba, but how to change it back to previous?

The right way to proceed is:
    * Note down the current (old) password, as found in data dictionary.
    * modify the password with the command ALTER USER IDENTIFIED BY
    * connect using the new password
    * do what you wanted to do
    * reset the password with the clause IDENTIFIED BY VALUES

To get the current password :-
SQL> SET LONG 100000
SQL> SELECT dbms_metadata.get_ddl('USER','SOUMYA') FROM dual;

   CREATE USER "SOUMYA" IDENTIFIED BY VALUES 'S:25204F11525CA54FAC51C7A06E8BF5D2
47BAB12D3C68B3FEAAA688FFF92F;6673D7515E467AA4'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"

Yes we can only get the password as encrypted format.

Now change the password temporarity:-
SQL > alter user soumya identified by das;

Once done, Login to the user a/c:-
SQL> conn soumya/das
Connected.

Now once the job is done we can again change back the password into previous one:-
SQL> alter user soumya identified by values 'S:25204F11525CA54FAC51C7A06E8BF5D2
47BAB12D3C68B3FEAAA688FFF92F;6673D7515E467AA4' ;


Another method:-

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

SPARE4
--------------------------------------------------------------------------------
S:C3C54AC4597D90A7CE643D6874F8348167D20E624FC49D981A2DE2BB116F

SQL>  alter user "SOUMYA" identified by values 'S:25204F11525CA54FAC51C7A06E8BF5D2
47BAB12D3C68B3FEAAA688FFF92F';

User altered.



Thats it.

No comments:

Post a Comment