Return-Path: X-Original-To: oracle-l@orafaq.com Delivered-To: oracle-l@orafaq.com Received: from puck1183.startdedicated.com (localhost [127.0.0.1]) by puck1183.startdedicated.com (Postfix) with ESMTP id 5B0C01960186 for ; Thu, 7 Jul 2016 17:16:26 +0200 (CEST) Received: from turing.freelists.org (turing.freelists.org [206.53.239.180]) by puck1183.startdedicated.com (Postfix) with ESMTPS for ; Thu, 7 Jul 2016 17:16:26 +0200 (CEST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 26F3D25203; Thu, 7 Jul 2016 11:16:25 -0400 (EDT) X-Virus-Scanned: Debian amavisd-new at turing.freelists.org Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id rTpfnALi4cCu; Thu, 7 Jul 2016 11:16:25 -0400 (EDT) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 408902529A; Thu, 7 Jul 2016 11:16:12 -0400 (EDT) Received: with ECARTIS (v1.0.0; list oracle-l); Thu, 07 Jul 2016 11:14:50 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 3CC3C251B7 for ; Thu, 7 Jul 2016 11:14:50 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id MjO0XmqAaN21 for ; Thu, 7 Jul 2016 11:14:50 -0400 (EDT) Received: from mail-qk0-f174.google.com (mail-qk0-f174.google.com [209.85.220.174]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (No client certificate requested) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id 0A267251D0 for ; Thu, 7 Jul 2016 11:14:49 -0400 (EDT) Received: by mail-qk0-f174.google.com with SMTP id t127so16898985qkf.1 for ; Thu, 07 Jul 2016 08:14:49 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:subject:to:references:cc:from:message-id:date :user-agent:mime-version:in-reply-to; bh=f/44ogEIeyP/Q/7GR9v4FBze2Ay+EbfTHTPWyIAic6w=; b=Y41VvwWyzvEl4q66F5nkkU5jESm4zHgaE58ovAOTyiEdVA33mVtHlfrq0QNNLbIxjd ep1m/YU3V0OjTtwu88D/drJEUplzUUO5BbUpg7HTgY8dYXOcHb+Wo8L/IF+Jl7WIp/jl t6E5VOUxZrH/StIBOuxNmdoiqVNlVT/LBmLWW3s1ral929gmSKChGP2uUXLZ1Qrz8xxE zEeeIKMCUGpzr3pXJ70fFMWX2mWsEQetBHdhjT9LJ3yLJEpBJwL+nfb0LhG+eC58X+Qx D2WZqyOCFP1RPUQN7kMOZ7hBG5Af5I4aEG9o0g3aPCzhLGr+aAdPr3LlX3Pu/KiR6Ilb 6mSA== X-Gm-Message-State: ALyK8tKKWTW2efeNZbTRg11WJNu2VatU6ocIhYX9ULdaQx8Xy/tN3AVS3jnr4esXnOT6OA== X-Received: by 10.55.144.69 with SMTP id s66mr903953qkd.6.1467904489482; Thu, 07 Jul 2016 08:14:49 -0700 (PDT) Received: from [192.168.1.100] (cpe-72-226-85-94.nyc.res.rr.com. [72.226.85.94]) by smtp.gmail.com with ESMTPSA id g15sm1122100qtc.17.2016.07.07.08.14.48 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Thu, 07 Jul 2016 08:14:48 -0700 (PDT) Subject: Re: Passwords in DBA_USERS (Oracle 12c) To: Chris Taylor , "Deas, Scott" References: <577D96E8.60502@gmail.com> <577DA534.2090905@gmail.com> <016201d1d808$763be5f0$62b3b1d0$@comcast.net> Cc: "andrew.kerber@gmail.com" , "mark.powell2@hpe.com" , "andy@oracledepot.com" , "dimensional.dba@comcast.net" , oracle-l From: Mladen Gogala Message-ID: <577E71E8.8060700@gmail.com> Date: Thu, 7 Jul 2016 11:14:48 -0400 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:38.0) Gecko/20100101 Thunderbird/38.8.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------000609070809030303020506" X-archive-position: 65477 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: gogala.mladen@gmail.com Precedence: normal Reply-To: gogala.mladen@gmail.com List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l --------------000609070809030303020506 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit Chris, Scott is right, proxy log-in is a better alternative. No 3rd account is needed, please see below: mgogala@umajor:~$ sqlplus system@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]@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 > 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 > > Thanks, > Scott > > *From:*oracle-l-bounce@freelists.org > > [mailto:oracle-l-bounce@freelists.org > ] *On Behalf Of *Andrew Kerber > *Sent:* Thursday, July 07, 2016 10:27 AM > *To:* mark.powell2@hpe.com > *Cc:* Chris Taylor >; andy@oracledepot.com > ; dimensional.dba@comcast.net > ; Mladen Gogala > >; > oracle-l > > *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 > 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@freelists.org > > > on behalf of Andy > Klock > > Sent: Thursday, July 7, 2016 9:32:56 AM > To: Chris Taylor > Cc: dimensional.dba@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 > >> 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 --------------000609070809030303020506 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit
Chris, Scott is right, proxy log-in is a better alternative. No 3rd account is needed, please see below:

mgogala@umajor:~$ sqlplus system@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]@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@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

 

Thanks,
Scott

 

From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Andrew Kerber
Sent: Thursday, July 07, 2016 10:27 AM
To: mark.powell2@hpe.com
Cc: Chris Taylor <christopherdtaylor1994@gmail.com>; andy@oracledepot.com; dimensional.dba@comcast.net; Mladen Gogala <gogala.mladen@gmail.com>; oracle-l <oracle-l@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@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@freelists.org <oracle-l-bounce@freelists.org> on behalf of Andy Klock <andy@oracledepot.com>
Sent: Thursday, July 7, 2016 9:32:56 AM
To: Chris Taylor
Cc: dimensional.dba@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@gmail.com<mailto:christopherdtaylor1994@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
--------------000609070809030303020506-- -- http://www.freelists.org/webpage/oracle-l