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

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

Part 5. How to Become another user in Oracle

What you'll need:

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.

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

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

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

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:

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), his password is the same in both instances because Import sets the user's encrypted password via the iIDENTIFIED 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.