RE: Altering passwords

From: Kevin Fries <kfries_at_lart.com>
Date: Fri, 26 Jan 2001 17:17:11 -0800
Message-Id: <10753.127675@fatcity.com>

This is a multi-part message in MIME format.

------=_NextPart_000_002C_01C087BB.D245D580 Content-Type: text/plain;

charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Hagedorn, Linda

Sent: Friday, January 26, 2001 2:06 PM To: Multiple recipients of list ORACLE-L Subject: Altering passwords

Hello,

I need to create a set of passwords on a new machine with the same passwords as on the old machine.

I'm looking for the passwords, but I remember someone here had a very clever way to alter user xx identified by 'xxxx' which was the encrypted string from dba_userids.

If anyone remembers or has that information, I'd appreciate it if you would send it to me or post it.

Thanks very much. Linda

YOu would probably be better off using import/export, but here's an old thing from Tom Kyte and an alternative method that may point you in the right direction.

select 'alter user scott identified by values "'||password||'" ;' from

dba_users where username = 'SCOTT' ;

(You may want to modify the above to spool the user names dynamically too. Since you didn't specify how many users, it might be simpler to just run each script for each user.)

Spool the dynamically generated SQL to a script, change the double quotes to single quotes and keep this script in a safe place ;

execute your script and presto, the users' password is restored and you never had a need to know their original password, and the user is none the wiser.

------other methods

Here's a script from Thomas Kite:

whenever sqlerror exit

column password new_value pw

declare

l_passwd varchar2(45);

begin

select password into l_passwd

from sys.dba_users

where username = upper('&1');

end;

select password

from sys.dba_users

where username = upper( '&1' )

alter user &1 identified by Hello;

connect &1/hello

alter user &1 identified by values '&pw';

show user

whenever sqlerror continue

Thomas uses the first select to determine if the user exists and if he has access to sys.dba_users. if not, he exits sqlplus.

Oracle uses the "by values" clause in a full database import, to create the user accounts with their passwords.

And if you are using password history in Oracle8, you can't do this -- it won't let you change it back until the password has cycled however many times you've set history to.

////////////

Good luck.

------=_NextPart_000_002C_01C087BB.D245D580 Content-Type: text/html;

charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD W3 HTML//EN">
<HTML>
<HEAD>

<META content=3D"text/html; charset=3Diso-8859-1" = http-equiv=3DContent-Type><TITLE>Altering passwords</TITLE>

<META content=3D'"MSHTML 4.72.3110.7"' name=3DGENERATOR>
</HEAD>
<BODY>
<DIV>&nbsp;</DIV>
<BLOCKQUOTE=20

style=3D"BORDER-LEFT: #0000ff solid 2px; MARGIN-LEFT: 5px; PADDING-LEFT: = 5px">

<DIV class=3DOutlookMessageHeader>-----Original Message----- From: root_at_fatcity.com =

[mailto:root_at_fatcity.com]On Behalf Of Hagedorn, = Linda Sent:=20

Friday, January 26, 2001 2:06 PM To: Multiple recipients = of list=20

ORACLE-L Subject: Altering passwords </DIV> Hello, I need to create a set of passwords = on a new=20

machine with the same passwords as on the old machine.  =

I'm looking for the passwords, but I = remember=20

someone here had a very clever way to alter user xx identified by = 'xxxx'=20

which was the encrypted string from dba_userids.  If anyone remembers or has that = information, I'd=20

appreciate it if you would send it to me or post it.  =

Thanks very much.  = Linda <SPAN=20

class=3D420400601-27012001>  <FONT=20

color=3D#0000ff face=3DArial size=3D2>YOu would probably be better = off using=20

import/export, but here's an old thing from Tom Kyte and an = alternative=20

method  that may point you in the right=20 direction.<SPAN =

class=3D420400601-27012001><FONT=20

face=3D"Courier New" size=3D2>
select 'alter user scott identified by values = "'||password||'"=20

;' from
dba_users where username =3D 'SCOTT' ;<FONT=20

color=3D#0000ff face=3DArial size=3D2>   (You=20

may want to modify the above to spool the user names dynamically = too. Since=20

you didn't specify how many users, it might be simpler to just run = each=20

script for each user.) 
Spool the dynamically generated SQL to a script, change the = double quotes=20

to single quotes and keep this script in a safe place ; execute your script and presto, the users' password is restored = and you=20

never had a need to know their original password, and the user is = none the=20

wiser.

------other methods

    <P>Here's a script from Thomas Kite:</P>
    <P>whenever sqlerror exit</P>
    <P>column password new_value pw</P>
    <P>declare</P>
    <P>l_passwd varchar2(45);</P>
    <P>begin</P>
    <P>select password into l_passwd</P>
    <P>from sys.dba_users</P>
    <P>where username =3D upper('&amp;1');</P>
    <P>end;</P>
    <P>/</P>
    <P>select password</P>
    <P>from sys.dba_users</P>
    <P>where username =3D upper( '&amp;1' )</P>
    <P>/</P>
    <P>alter user &amp;1 identified by Hello;</P>
    <P>connect &amp;1/hello</P>
    <P>alter user &amp;1 identified by values '&amp;pw';</P>
    <P>show user</P>
    <P>whenever sqlerror continue</P>
    <P>Thomas uses the first select to determine if the user exists and =

if he=20

has access to sys.dba_users. if not, he exits sqlplus. ******
Oracle uses the "by values" clause in a full database = import,=20

to create the user accounts with their passwords. And if you are using password history in Oracle8, you can't do = this -- it=20

won't let you change it back until the password has cycled however = many=20

times you've set history to.  <FONT color=3D#0000ff = face=3DArial=20

size=3D2>//////////// Received on Fri Jan 26 2001 - 19:17:11 CST