Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Become User privilege

Re: Become User privilege

From: Jared Still <jkstill_at_bcbso.com>
Date: Tue, 29 Aug 2000 07:47:36 -0700 (PDT)
Message-Id: <10603.115736@fatcity.com>


On Tue, 29 Aug 2000, Boivin, Patrice J wrote:

> Is there a way a DBA can log on as other users (without saving the encrypted
> password, changing the password of the account, logging in using the new
> password, logging out, and restoring the original password)?
>

These scripts will do the trick.

These will not work with some newer features such as single sign on.

Jared


rem avoid messing up an account if something does not work! whenever sqlerror exit

set pages 0 feed on verify off echo off term on

col bu_user new_value user_to_become noprint col global_name new_value gname noprint

prompt bu.sql will save a users old password prompt and assign a new one so that you prompt ( the DBA ) can log in. Run the tmp prompt script to restore the old password. prompt
prompt

prompt User to become:
set term off feed off
select '&&1' bu_user from dual;
select global_name from global_name;

@@tmpfile
set term off feed off

define reset_script=&&_tmp_file_name_..reset.sql

spool &&reset_script
select 'alter user &&user_to_become identified by values ' || '''' ||

        password || '''' || ';'
from dba_users
where username = upper('&&user_to_become') /

prompt set feed on echo off pause off term on select 'prompt Please remove &&reset_script after you are done' from dual; prompt prompt
spool off

!chmod 640 &&reset_script

alter user &&user_to_become identified by dbatest /

whenever sqlerror continue
set term on feed on

connect &&user_to_become/dbatest@&&gname

prompt
prompt The password for &&user_to_become@&&gname will be changed to 'dbatest' prompt The script to restore the password is &&reset_script prompt
prompt I am resetting the users password to its original setting now. prompt

@&&reset_script

undef gname
undef 1
undef user_to_become


set verify off feed off
set echo off pause off feed off term off

var tmpstamp_ varchar2(30);
var dbname_ varchar2(8);

declare

	tmpsecs  number;
	secs varchar2(9);
	sec_len integer := 0;
begin
	select hsecs into tmpsecs from v$timer;

	--dbms_output.enable(1000000);
	--dbms_output.put_line('secs: ' || secs);

	select to_char(tmpsecs)
	into :tmpstamp_ 
	from dual;

	select lower(substr(global_name,1,instr(global_name,'.')-1))
	into :dbname_
	from global_name;

end;
/

col tmpstamp_ noprint new_value timestamp col filetmp_ noprint new_value _tmp_file_name_ col dbname_ noprint new_value instance

select :tmpstamp_ tmpstamp_, :dbname_ dbname_ from dual;

select Received on Tue Aug 29 2000 - 09:47:36 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US