Intermediate SQL Color Coded SQL, UNIX and Database Essays

29Jan/115

How to become regular ORACLE user when you do NOT know the password

This is a fairly common situation for a DBA: you can always connect as SYSDBA, but sometimes you really need to connect as a regular database user (i.e. to have proper environment for your queries).

If you are root in UNIX, this would be extremely easy to do:

su - <regular user>

Unfortunately, ORACLE does not have an equivalent su command and, even if you are SYS, to connect to any regular database user you need to know its password (which could be a problem).

Still, there is a simple workaround that can be applied here. Essentially, you can save user’s old password, change it to some dummy value and quickly change it back after your connection is established.

While user password hashes are no longer exposed in DBA_USERS view (starting with 11g, I believe), they are still available in SYS.USER$.

Below is the script that does exactly that (full credit goes to Matt Parker who showed me this trick).

define BECOME_USER=&1

COLUMN user_pwd new_value SZ_PWD

SET termout off
SELECT password AS user_pwd FROM USER$ WHERE UPPER(name)='&BECOME_USER';

spool '/tmp/.&BECOME_USER..CHANGE'
prompt SET termout off
prompt CONNECT / AS sysdba
prompt ALTER USER &BECOME_USER IDENTIFIED BY VALUES '&SZ_PWD';;
prompt exit
spool off

ALTER USER &BECOME_USER IDENTIFIED BY TEMPORARY;
CONNECT &BECOME_USER/TEMPORARY
host sqlplus -S /nolog @/tmp/.&BECOME_USER..CHANGE
host rm /tmp/.&BECOME_USER..CHANGE

SET termout ON

SHOW USER

undef BECOME_USER
Comments (5) Trackbacks (0)
  1. Hehe, Well known trick 🙂

  2. This runs the risk of a “ORA-28007: the password cannot be reused” if you are using a policy the prohibits password reuse.
    An alternative approach using proxy authentication is outlined in my blog
    http://blog.sydoracle.com/2010/10/temporary-access-to-database-accounts.html

  3. Thanks, Gary. Yes, proxy connection seems to be a better way and closer to the spirit of UNIX su command … Why have I not thought of this earlier ? 🙂
    The slight issue might be however when a password for a DBA user is unknown (but you can still connect as /)

  4. This helped me on our 11.2 upgrade of good old TMS db – fixing the
    “ORA-28002: the password will expire within 7 days”
    messages on some logins (even after the profile PASSWORD_LIFE_TIME was set to unlimited). Luckily we have no restriction on using the same password. Thanks Maxym! =)

  5. Daniel, glad it was useful 🙂


Leave a comment

No trackbacks yet.