Re: Passwords in DBA_USERS (Oracle 12c)
- From: "Powell, Mark" <mark.powell2@xxxxxxx>
- To: "gogala.mladen@xxxxxxxxx" <gogala.mladen@xxxxxxxxx>, "cherif.benhenda@xxxxxxxxx" <cherif.benhenda@xxxxxxxxx>
- Date: Fri, 8 Jul 2016 13:40:38 +0000
What is the purpose of the PASSWORD column in DBA_USERS view, if the
password will not be shown? <<
Backward compatibility with existing code that selects the column. I see this
as being just like VPD where you do not let some users see the value in
specific columns. In this case Oracle does not want use to see the value.
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Cherif Ben Henda <cherif.benhenda@xxxxxxxxx>
Sent: Friday, July 8, 2016 6:32:47 AM
To: gogala.mladen@xxxxxxxxx
Cc: oracle-l
Subject: Re: Passwords in DBA_USERS (Oracle 12c)
Hi ,
Password column on dba_users is not the same as sys.user$
DECODE(sys.user$."PASSWORD",'GLOBAL',
sys.user$."PASSWORD",'EXTERNAL',sys.user$."PASSWORD",NULL) "PASSWORD"
This may be help you.
Thanks,
2016-07-07 0:40 GMT+01:00 Mladen Gogala
<gogala.mladen@xxxxxxxxx<
mailto:gogala.mladen@xxxxxxxxx>>:
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
--
Cordialement,
Cherif Ben Henda
--
//www.freelists.org/webpage/oracle-l
Other related posts: