Re: Passwords in DBA_USERS (Oracle 12c)

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_at_gmail.com> 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_at_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
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
--
Thanks,
* Ricardo Arnoud*

*Bacharel em Ciência da Computação* - 2013/1





(51) 9511-4658 VIVO / (51) 9259-2333 CLARO / Porto Alegre - RS
http://www.ricardoarnoud.com
http://www.alterdatabaseopen.com
FB http://www.facebook.com/ricardo.arnoud
Linkedin http://br.linkedin.com/pub/ricardo-arnoud/15/127/3b1

--
http://www.freelists.org/webpage/oracle-l