Re: Switch-user equivalent

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 21 Jul 1998 15:57:17 GMT
Message-ID: <35b8b942.11294871@192.86.155.100>

A copy of this was sent to Badri_at_cris.com (BADRI) (if that email address didn't require changing) On 21 Jul 1998 11:08:23 EDT, you wrote:

>Hi:
>
>I need a switch-user equivalent in Oracle. I dont want to know
>the password for someone, but want to log in as that person. I can
>settle for a workaround if available, but if I change the password
>of that user, I want to put it back to what it was before I changed
>it, without knowing what it was.
>
>Suppose there us a user names jack with a password Jill. I, the
>DBA does not know the password Jill. I want to change the password
>temporarily to another value, use the id and then set-it back to
>the old value - like the 'su' equivalent in UNIX.
>
>Is there a way I can alter the user Jack's password to test and then
>put it back to Jill using the encrypted value of the password?
>
>I read somewhere that there is a syntax like alter user <username>
>identified as <hexvalue> ; will change the password to the text
>equivalent of the hexvalue (instead of the hexvalue used as a text string).
>
>Any help will be greatly appreciated.
>
>thanks,
>
>-Badri

This is an 'su.sql' script I use:

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

it starts by testing your access to the sys.dba_users table -- if that fails -- it exits. If zero rows returned -- it exits.

It then selects the 'password' from the dba_users table and stuffs it into a macro variable "&pw"

We alter the user you want to become to have a known password (if that fails, we exit).

We 'fix' their password back after loggin in as them....

Note, you need to have access to dba_users and the alter user privelege.  

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 Tue Jul 21 1998 - 10:57:17 CDT