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

---

• become_user.sql

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

---

• tmpfile.sql
• create a temporary file name
• it will be of the form /tmp/<INSTANCE>.<USER>.<HSECS>
• the temp filename will be in the variable _tmp_file_name_
• you must be able to select from v$timer for this to work
• use rmfile to remove tmp files

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