Re: Change User

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 06 May 1998 15:10:49 GMT
Message-ID: <35537c4e.7132986@192.86.155.100>

A copy of this was sent to jkallman_at_us.oracle.com (Joel R. Kallman) (if that email address didn't require changing) On Wed, 06 May 1998 11:35:37 GMT, you wrote:

>On Tue, 5 May 1998 12:00:14 -0400, "Murtaza Doctor"
><mdoctor_at_firstsense.com> wrote:
>
>>
>> Is there a way for a dba to change a login to another user
>> - if he/she does not have the user password?
>> (without logging off ofcourse).
>>

[snip]
>>
>
>Do this using the ALTER USER statement. If you are the DBA, you can
>change anyone's password without knowing what their old password was.
>
>----
>SQL> create user foo identified by foo;
>SQL> grant connect, resource to foo;
>SQL> connect foo/foo;
>SQL> connect system/manager;
>SQL> alter user foo identified by newpassword;
>SQL> connect foo/newpassword;
>SQL>
If you want to 'su' to another user without losing their password (the above would permanently alter user foo's password -- foo wouldn't be able to log in anymore) you can use the following script (I call it su.sql)

• BOF ----------------------------- 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

------------------- EOF -----------------------------

Why do I query dba_users twice? I do it to make sure

1- i have access to dba_users, if not the whenver sqlerror exit will bounce me out of sqlplus harmlessly.

2- to make sure the user exists, if not the select into will fail and the whenever sqlerror exit will again bounce me out of sqlplus harmlessly

then we select the encoded password for the user out of dba_users and stash that in a sqlplus macro variable. We alter that user to a known password, connect as that user, and immediately reset their password back to the original one.

>--------
>Thanks!
>
>Joel
>
>Joel R. Kallman
>Oracle Government, Education, & Health
>Columbus, OH http://govt.us.oracle.com
>jkallman@us.oracle.com http://www.oracle.com
>
>----
>The statements and opinions expressed here are my own
>and do not necessarily represent those of Oracle Corporation.
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed May 06 1998 - 10:10:49 CDT