Oracle 11g / 12c identified by values – set an Oracle password to its current value without knowing the password

This post will be a quick one. I needed to set a Oracle password as it was about to expire but the client did not want to change the password itself.

The simplest way to get the encrypted password was:

select name, password from sys.user$ where name=‘USER’;

But after Oracle 11g/12c seems a better option for security ends to use DBMS_METADATA.get_ddl or select the spare4 column on sys.user$

select name, spare4 from sys.user$ where name=‘USER’;

or

set long 9999999
set longc 9999999
select dbms_metadata.get_ddl('USER','DBSNMP') from dual;

And how to set this password back to the user?

alter user [USER] identified by values '[encrypted password]';

 

Hope it helps!

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.