Re: Passwords in DBA_USERS (Oracle 12c)

My fault - I was thinking the "...grant connect through system;" was a specific grant to SYSTEM for some reason. Good catch.

Chris

On Thu, Jul 7, 2016 at 10:14 AM, Mladen Gogala <gogala.mladen_at_gmail.com> wrote:

> Chris, Scott is right, proxy log-in is a better alternative. No 3rd
> account is needed, please see below:
>
> mgogala_at_umajor:~$ sqlplus system_at_local
>
> SQL*Plus: Release 12.1.0.2.0 Production on Thu Jul 7 11:11:01 2016
>
> Copyright (c) 1982, 2014, Oracle. All rights reserved.
>
> Enter password:
> Last Successful login time: Thu Jul 07 2016 11:10:05 -04:00
>
> 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> alter user scott grant connect through system;
>
> User altered.
>
> Elapsed: 00:00:00.00
> SQL> connect system[scott]_at_local
> Enter password:
> Connected.
>
> Session altered.
>
> Elapsed: 00:00:00.00
> SQL> show user
> USER is "SCOTT"
> SQL>
>
>
>
> On 07/07/2016 10:57 AM, Chris Taylor wrote:
>
> It appears the only problem with this approach is the DBA doesn't include
> this ability automatically. You still have to do the grant to the DBA to
> allow the connect through - which requires a 3rd account to do the grant as
> you can't grant privs to yourself. It would be great if this was included
> in the DBA role functionality to connect through any user.
>
> After setting up the necessary grant, it is definitely easier to do it
> this way but in the middle of an application deployment, this method is
> cumbersome if the setup grants haven't been completed.
>
> Thanks,
> Chris
>
>
> On Thu, Jul 7, 2016 at 9:44 AM, Deas, Scott <Scott.Deas_at_lfg.com> wrote:
>
>> But you don’t need to know the user’s password or change it, you just
>> proxy into the account. We’ve been using it successfully here for years.
>>
>>
>>
>>
>> <http://www.oracle.com/technetwork/issue-archive/2013/13-mar/o23asktom-1906478.html>
>> http://www.oracle.com/technetwork/issue-archive/2013/13-mar/o23asktom-1906478.html
>>
>>
>>
>> Thanks,
>> Scott
>>
>>
>>
>> *From:* <oracle-l-bounce_at_freelists.org>oracle-l-bounce_at_freelists.org
>> [mailto:oracle-l-bounce_at_freelists.org] *On Behalf Of *Andrew Kerber
>> *Sent:* Thursday, July 07, 2016 10:27 AM
>> *To:* mark.powell2_at_hpe.com
>> *Cc:* Chris Taylor < <christopherdtaylor1994_at_gmail.com>
>> christopherdtaylor1994_at_gmail.com>; andy_at_oracledepot.com;
>> dimensional.dba_at_comcast.net; Mladen Gogala <gogala.mladen_at_gmail.com>;
>> oracle-l <oracle-l_at_freelists.org>
>> *Subject:* Re: Passwords in DBA_USERS (Oracle 12c)
>>
>>
>>
>> Yes. Until programmers learn to include functionality that allows
>> passwords to be changed easily on the mid tier, the DBA or designated
>> security personnel must be able to change a password and take it back to
>> what it was.
>>
>>
>>
>> On Thu, Jul 7, 2016 at 9:20 AM, Powell, Mark <mark.powell2_at_hpe.com>
>> wrote:
>>
>> Andy, I will disagree that it is absurd for Oracle to allow a means for a
>> 'privileged' user to be able to change another's users password hash
>> because without such a method how would an existing user with their
>> existing password be migrated to another database?
>>
>> ________________________________
>> From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on
>> behalf of Andy Klock < <andy_at_oracledepot.com>andy_at_oracledepot.com>
>> Sent: Thursday, July 7, 2016 9:32:56 AM
>> To: Chris Taylor
>> Cc: dimensional.dba_at_comcast.net; Mladen Gogala; oracle-l
>> Subject: Re: Passwords in DBA_USERS (Oracle 12c)
>>
>> All your points are valid Chris. My absurdity comment is about the
>> Oracle software allowing someone to log into someone else's account and
>> then reset the password back to its previous state. This is a gaping
>> security hole that should be filled. Removing PASSWORD from DICTIONARY
>> access was a step in the right direction. Those hashes shouldn't be
>> considered unbreakable.
>>
>> Didn't meant to imply that the Mladen was doing anything wrong.
>>
>> On Thu, Jul 7, 2016 at 9:16 AM, Chris Taylor <
>> christopherdtaylor1994_at_gmail.com<mailto:
>> <christopherdtaylor1994_at_gmail.com>christopherdtaylor1994_at_gmail.com>>
>> wrote:
>> 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
>>
>>
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>>
>>
>> --
>>
>> Andrew W. Kerber
>>
>> 'If at first you dont succeed, dont take up skydiving.'
>>
>> Notice of Confidentiality: **This E-mail and any of its attachments may
>> contain
>> Lincoln National Corporation proprietary information, which is
>> privileged, confidential,
>> or subject to copyright belonging to the Lincoln National Corporation
>> family of
>> companies. This E-mail is intended solely for the use of the individual
>> or entity to
>> which it is addressed. If you are not the intended recipient of this
>> E-mail, you are
>> hereby notified that any dissemination, distribution, copying, or action
>> taken in
>> relation to the contents of and attachments to this E-mail is strictly
>> prohibited
>> and may be unlawful. If you have received this E-mail in error, please
>> notify the
>> sender immediately and permanently delete the original and any copy of
>> this E-mail
>> and any printout. Thank You.**
>>
>
>
>
> --
> Mladen Gogala
> Oracle DBA
> Tel: (347) 321-1217
>
>

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