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 A9EDC196126A for ; Thu, 7 Jul 2016 01:52:05 +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 01:52:05 +0200 (CEST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 36F5324BA2; Wed, 6 Jul 2016 19:52:00 -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 UoThCd20yVdW; Wed, 6 Jul 2016 19:52:00 -0400 (EDT) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 5C01224C09; Wed, 6 Jul 2016 19:51:47 -0400 (EDT) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 06 Jul 2016 19:50:25 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 6834E24A90 for ; Wed, 6 Jul 2016 19:50:25 -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 iewVS26OKBqr for ; Wed, 6 Jul 2016 19:50:25 -0400 (EDT) Received: from mail-qt0-f174.google.com (mail-qt0-f174.google.com [209.85.216.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 3125723D60 for ; Wed, 6 Jul 2016 19:50:25 -0400 (EDT) Received: by mail-qt0-f174.google.com with SMTP id w59so966515qtd.3 for ; Wed, 06 Jul 2016 16:50:25 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc; bh=SpjnAJ5Ad20OvOgt8BjMLhasNsgWPx2PD8ClC0GGoe0=; b=IlTZjJZPRugCPZQ5TM9qLseekgbKORAwwLuj0N/5LLSNp9xKOVnJLftMEan7yp4iC8 crM7VYPYESeSjmU2GeCm24ni/COIoiT4k+hKs3kaZLaN6BSbLIJxhjRnaqulFRACsNct oKhR4stC494XqUwCQV2P8hYR8O/hhyBfSRhKip+yCQsJk855iYYF5qIfCHLt1CfsEPtE hf80AeqWTixgOZawDOWc7U/3wBdrctNw18j8TQaEk85IAo/xTNhPjp5pAxq+ocdVBs3Y gHlrWZWEGrTa4Vko1BZ2EixR79ZeWX25MmE9u+tqI+drhqKOozTsSuTktMTIDHQDOy9R ZsGA== X-Gm-Message-State: ALyK8tIY/Uy+d07VdNA6pGJh4iKBOujCI6ZpEkGC8Z7R3vYidYVgzTk+U8MD+s8xg0qU5z1ORaZxnBIM+fWRIA== X-Received: by 10.200.37.150 with SMTP id e22mr39830898qte.37.1467849024655; Wed, 06 Jul 2016 16:50:24 -0700 (PDT) MIME-Version: 1.0 Received: by 10.237.46.135 with HTTP; Wed, 6 Jul 2016 16:50:24 -0700 (PDT) In-Reply-To: <577D96E8.60502@gmail.com> References: <577D96E8.60502@gmail.com> From: Ricardo Arnoud Date: Wed, 6 Jul 2016 20:50:24 -0300 Message-ID: Subject: Re: Passwords in DBA_USERS (Oracle 12c) To: gogala.mladen@gmail.com Cc: oracle-l Content-Type: multipart/alternative; boundary=001a11c04ca8b2d6770537003b0f X-archive-position: 65459 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: ricardoskn@gmail.com Precedence: normal Reply-To: ricardoskn@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 --001a11c04ca8b2d6770537003b0f Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable 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 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=3D'SCOTT'; > > PASSWORD > > -------------------------------------------------------------------------= ------- > > > Elapsed: 00:00:00.00 > SQL> select password from sys.user$ where name=3D'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 righ= t > 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 > > > --=20 -- Thanks, * Ricardo Arnoud* *Bacharel em Ci=C3=AAncia da Computa=C3=A7=C3=A3o* - 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 --001a11c04ca8b2d6770537003b0f Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Try the following for 12c:

select 'ALTER USER &= #39;||name||' identified by values '||CHR(39)||spare4||';'|= |password||CHR(39)||';'
=C2=A0from user$ where name in ('SCO= TT');

On Wed, Jul 6, 2016 at 8:40 PM, Mladen Gogala <gogala.mladen@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 extra= cting the password from DBA_USERS and then use "VALUES" clause to= return it back. However, query to DBA_USERS did not return password. I che= cked it later at home and here is what happens:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Productio= n
With the Partitioning, OLAP, Advanced Analytics and Real Application Testin= g options


Session altered.

Elapsed: 00:00:00.00
SQL> select password from dba_users where username=3D'SCOTT';
PASSWORD
---------------------------------------------------------------------------= -----


Elapsed: 00:00:00.00
SQL> select password from sys.user$ where name=3D'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 pri= vileges. Moreover, querying directly the table USER$ provided the right pas= sword.=C2=A0 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 d= irectly to the USER$ table, but this is annoying, since prevents me from do= ing this when not connected as=C2=A0 SYSDBA, On the other hand, changing pa= ssword back using the "VALUES" clause is a very usual thing, some= body must have noticed.=C2=A0 Googling around, I found the page from Lauren= t Schneider:

http://laurentschneider.com/wordpress/2007/08/the-password-is-not-l= onger-displayed-in-dba_userspassword.html

which states more or less the same thing. However, the change isn't doc= umented as Laurent has stated. What is the purpose of the PASSWORD column i= n 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





--
<= div>
--
Thanks,
Ricardo Arnoud

Bacharel em Ci=C3=AAnc= ia da Computa=C3=A7=C3=A3o - 2013/1


=

=C2=A0

(51) 9511-4658 VIVO / (51) 9259-2333 CLARO / Porto Alegre - R= S
--001a11c04ca8b2d6770537003b0f-- -- http://www.freelists.org/webpage/oracle-l