Subject: The New DBA FAQs, Part 5

From: Kevin Loney <"75663,327"@compuserve.com>
Newsgroups: comp.databases.oracle
Date: 1996/11/19


The New DBA FAQs
(Frequently Asked Questions for/from Database Administrators)
by Kevin Loney

Fifth in a Series.

Part 5. How to Become another user in Oracle

******

This article may not be published.

******

30-NOV-92 Initial posting. Kevin Loney
07-FEB-96 Updated! See the MODIFICATIONS section. Kevin Loney
19-NOV-96 Updated as part of the New DBA FAQs. Kevin Loney

*****

References: ORACLE DBA Handbook, 7.3 Edition (Loney)
See www.osborne.com for scripts and sample chapters.

*****

How to become another user in Oracle.


What you'll need:
A DBA account
A "become" script
A "reset" script

I can't help you get the DBA account, but I can provide the two scripts. Not only that, but I can do it
in such a way that it won't show up in the dba_audit_dba table.

This method makes use of the password assignment techniques used by export/import. I've also used
it to lock people out of accounts while keeping the account open.

First, create a script that will reset the user to his present encrypted password; then revise his password
to a word of your choosing. You'll have to remember to reset his password when you're done.

BECOME.SQL:

rem become.sql
rem K Loney, 9/10/92.
rem resets user's password to 'reset' temporarily
rem to change it back, run sys$login:reset_{username}.sql

set pagesize 0
set feedback off
set verify off
set echo off
set termout off

spool sys$login:reset_&&1..sql
select 'alter user &&1 identified by values '||''''||
password||''''||';'
from dba_users where username = upper('&&1');

select 'host delete sys$login:reset_&&1..sql;*' from dual;

select 'exit' from dual;
spool off

alter user &&1 identified by newpassword;
exit


That's it. The first part builds an alter user string (which won't show up in DBA_AUDIT_DBA), and
stores it in a file (reset_username). Then the account's password is changed - in this case, to
'newpassword'. You can now log in as that user, using 'newpassword' as the account password.
When you're done, run the reset_username script you created. It'll delete itself when it's complete.

MODIFICATIONS TO THE ORIGINAL SCRIPT
======================================
The original script for this utility was developed when I was exclusively a VAX DBA. Under UNIX,
this script can encounter problems. Specifically, if an OPS$ username is used, then Oracle will try to
create a file called something like

reset_ops$kevin.sql

Unfortunately, the $ is a special character in UNIX, and unless it is preceded by a backslash (\), it'll
cause the file to be named

reset_ops

instead. If you create two of these in a row, the second will overwrite the first.

Potential solutions:
1. Don't use OPS$ accounts.
2. Do one account at a time.
3. Remove the username from the output filename.
4. Code the backslash into the output filename.


ANOTHER MODIFICATION
=======================
After this FAQ was first posted, another DBA - probably D. Bath, I don't recall exactly - and by the
way, send his daughter a postcard, he does a lot of work for the 'net - suggested a revised way of
putting the "delete" command into the output file. In the original command file, the

select 'host delete sys$login:reset_&&1..sql;*' from dual;

select 'exit' from dual;

lines generate the "delete" and "exit" lines in the reset file. If you are not putting the username in the
output filename, then you can simplify this and avoid the silly database hit against DUAL:

prompt 'host delete sys$login:reset.sql;*'

prompt 'exit'

*

WHY THIS WORKS
================

Oracle does not store your account's password in the database. Instead, it stores the encrypted version
of your password. When you try to log in, it takes the password you use, encrypts it, and compares it
to the version stored in the database. If it matches, you get in; if it doesn't, you don't.

When you export a user from one database and import him into another database (via a full system
export), this password is the same in both instances because Import sets the user's encrypted password
via the IDENTIFIED BY VALUES clause used in the scripts.

You can also use this method to set impossible passwords for users (such as SYS, since you could
still CONNECT INTERNAL) by setting encrypted passwords that violate the encryption algorithm's
pattern rules (16 chars long, all upper case letters + numbers). See Chapter 9 of the ORACLE DBA
Handbook for a detailed description.

=======
Kevin Loney is the author of the ORACLE DBA Handbook, 7.3 Edition, coauthor of ORACLE: The
Complete Reference, Electronic Edition, and coauthor of Advanced ORACLE Tuning and
Administration, all published under Osborne/McGraw-Hill's Oracle Press imprint. See
www.osborne.com for scripts and sample chapters.


[Top | Back | Home ]