Re: Passwords in DBA_USERS (Oracle 12c)

Having the password "somewhere" is important so I'm not sure if Andy is suggesting it's absurd to have it anywhere in the database or not. But for at least one case it's terribly important and that is supporting legacy applications.

Sometimes you need to be able to login as an application schema to create an object such as a materialized view or database link that is either exceptionally difficult or impossible to do UNLESS you are logged in as the schema owner.
The DBA may not have access to the schema password but can preserve the password by looking at sys.user$ for the encrypted password, temporarily change it, create the object (db link or MV), then change the password back without ever affecting the application (or briefly affecting the application at least).

Thanks,
Chris

On Wed, Jul 6, 2016 at 11:31 PM, Dimensional DBA < dimensional.dba_at_comcast.net> wrote:

> Seems to be a lot of churn about nothing. The column is still documented
> in various versions of the documentation and it is documented that it is
> deprecated or has a different meaning.
>
>
>
> 11.1
>
> https://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_5073.htm
>
> 11.2
>
>
> https://docs.oracle.com/cd/E11882_01/server.112/e40402/statviews_5081.htm#REFRN23302
>
> 12.1
>
>
> https://docs.oracle.com/database/121/REFRN/GUID-309FCCB2-2E8D-4371-9FC5-7F3B10E2A8C0.htm#REFRN23302
>
>
>
> If you speak of spare4 in user$ that is not a part of the formal published
> data dictionary so changes are not documented to the public by Oracle. If
> you look at the access rights of “SELECT ANY DICTIONARY” you will find none
> of the base $ tables are viewable, meaning Oracle does not consider them to
> be a part of the data dictionary, but a part of the RDBMS engine itself.
>
>
>
> Reasons for leaving the password column in dba_users could be as simple as
> backwards compatibility for a variety of applications that queried for
> those columns in the dba_users view. I know of many BPM applications that
> consume that information even though it is no longer populated. Easier to
> leave a NULL column in then remove it and have 1000’s of clients not want
> to upgrade to your latest version of the database because you will break
> their other vendor tools.
>
>
>
>
>
>
>
>
>
> *Matthew Parker*
>
> *Chief Technologist*
>
> *Dimensional DBA*
>
> *425-891-7934 <425-891-7934> (cell)*
>
> *D&B *047931344
>
> *CAGE *7J5S7
>
> *Dimensional.dba_at_comcast.net <Dimensional.dba_at_comcast.net>*
>
> *View Matthew Parker's profile on LinkedIn*
> <http://www.linkedin.com/pub/matthew-parker/6/51b/944/>
>
> www.dimensionaldba.com
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Andy Klock
> *Sent:* Wednesday, July 6, 2016 8:39 PM
> *To:* Mladen Gogala
> *Cc:* oracle-l
> *Subject:* Re: Passwords in DBA_USERS (Oracle 12c)
>
>
>
> Important how? I find it absurd that this capability is still even
> possible. As others have noted this was changed in 11g.
>
>
>
> User Passwords Are No Longer Visible In DBA_USERS As Of 11g (Doc ID
> 735651.1)
>
>
>
> The column is deprecated in the manual:
>
>
>
>
> https://docs.oracle.com/cd/E11882_01/server.112/e40402/statviews_5081.htm#REFRN23302
>
>
>
>
>
> Thanks,
>
>
>
> Andy K
>
>
>
>
>
> On Wed, Jul 6, 2016 at 8:41 PM, Mladen Gogala <gogala.mladen_at_gmail.com>
> wrote
>
>
> Hi Ricardo,
> I know that SPARE4 is SHA version of the password hash, but my point was
> more to clarify why was it done, and even more importantly, why wasn't such
> an important change documented? It is also possible to extract the hash
> using DBMS_METADATA.GET_DDL. I have solved my original problem, that's not
> the point.
>
> Regards
>
>
>
>
> --
> Mladen Gogala
> Oracle DBA
> Tel: (347) 321-1217
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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