Try the following for 12c:
select 'ALTER USER '||name||' identified by values
'||CHR(39)||spare4||';'||password||CHR(39)||';'
from user$ where name in ('SCOTT');
On Wed, Jul 6, 2016 at 8:40 PM, Mladen Gogala <gogala.mladen@xxxxxxxxx>
wrote:
I've hit a rather unpleasant snag: I needed to change a password for a
user and make sure that I can return it back to what it was. The usual
method is by extracting the password from DBA_USERS and then use "VALUES"
clause to return it back. However, query to DBA_USERS did not return
password. I checked it later at home and here is what happens:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit
Production
With the Partitioning, OLAP, Advanced Analytics and Real Application
Testing options
Session altered.
Elapsed: 00:00:00.00
SQL> select password from dba_users where username='SCOTT';
PASSWORD
--------------------------------------------------------------------------------
Elapsed: 00:00:00.00
SQL> select password from sys.user$ where name='SCOTT';
PASSWORD
--------------------------------------------------------------------------------
F894844C34402B67
Elapsed: 00:00:00.01
SQL> alter user scott identified by values 'F894844C34402B67';
User altered.
Elapsed: 00:00:00.10
SQL> connect scott/tiger@local
Connected.
Session altered.
Elapsed: 00:00:00.00
SQL>
I was connected as SYSDBA, so there is no doubt about not having enough
privileges. Moreover, querying directly the table USER$ provided the right
password. Is this on purpose or a bug? I have also checked with 11.2.0.4,
the same thing happens. I am not terribly upset because I can always go
directly to the USER$ table, but this is annoying, since prevents me from
doing this when not connected as SYSDBA, On the other hand, changing
password back using the "VALUES" clause is a very usual thing, somebody
must have noticed. Googling around, I found the page from Laurent
Schneider:
http://laurentschneider.com/wordpress/2007/08/the-password-is-not-longer-displayed-in-dba_userspassword.html
which states more or less the same thing. However, the change isn't
documented as Laurent has stated. What is the purpose of the PASSWORD
column in DBA_USERS view, if the password will not be shown?
--
Mladen Gogala
Oracle DBA
Tel: (347) 321-1217
--
//www.freelists.org/webpage/oracle-l
