Message-Id: <10753.127675@fatcity.com> From: "Kevin Fries" Date: Fri, 26 Jan 2001 17:17:11 -0800 Subject: RE: Altering passwords 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@fatcity.com [mailto:root@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 Altering passwords
 
-----Original Message-----
From: root@fatcity.com = [mailto:root@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

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  

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.

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

dba_users where username =3D 'SCOTT' ;  

(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

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 =3D upper('&1');

end;

/

select password

from sys.dba_users

where username =3D 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=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.

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