Accessing a user when you don’t know the password
25/03/2016 4 Comments
There are times that you may need to logon to a database user, probably a schema owner to do a release, but you don’t know the password. You may not be able to (easily) change the password as it could be embedded in application connect strings or worse.
If may not be possible simply to change your session using alter session set current_schema=<schema-to-be-changed>; to auto-prefix all of your selects with the schema, especiually if the release references “USER_” views, which is unaffected by the session setting.
You need to become the account.
So, what you need to do is record the current password encryption, change the password, logon and do your maintenance, logoff and change the password back!
And this is how you do it:
Create an account:
04:38:35 SYS @ ORCL01 > create user hackme identified by password1;
User created.
04:38:35 SYS @ ORCL01 > grant connect,resource to hackme;
Grant succeeded.
Grab the encryption.This is stored in SYS.USER$.SPARE4 plus SYS.USER$.PASSWORD:
04:38:35 SYS @ ORCL01 > select name,'alter user '||name||' identified by values '''||spare4||';'||password||''';' command from sys.user$ where name = 'HACKME' 04:38:35 2 / NAME COMMAND ---------- ------------------------------------------------------------------------------------------------------------------------ HACKME alter user HACKME identified by values 'S:59F38E64D3914BB9396C5D4B968380676333EA7CB34F2471A85C4770A7BA;H:2D3693D1357CF012D9A11EFE3D792C0C;T:B2261F70475F3BD6173867C68427E346C53216E3EC305121DDAF4E13E72E6889DF1E314934F3C5F46E5F12B82D8AC144955C937413FD192904A2762D66B31A872429AB78E72AFC2BC4101E68DB5903A6;4345E749C3EBB34A';
Now we can change the password, logon with the new password, logoff back to a DBA and change it back using the previously captured command
04:38:35 SYS @ ORCL01 > alter user hackme identified by hacker; User altered. 04:38:35 SYS @ ORCL01 > connect hackme/hacker; Connected. 04:38:35 HACKME @ ORCL01 > show user USER is "HACKME" 04:38:35 HACKME @ ORCL01 > connect sys/oracle as sysdba Connected. 04:38:35 SYS @ ORCL01 > alter user HACKME identified by values 'S:59F38E64D3914BB9396C5D4B968380676333EA7CB34F2471A85C4770A7BA;H:2D3693D1357CF012D9A11EFE3D792C0C;T:B2261F70475F3BD6173867C68427E346C53216E3EC305121DDAF4E13E72E6889DF1E314934F3C5F46E5F12B82D8AC144955C937413FD192904A2762D66B31A872429AB78E72AFC2BC4101E68DB5903A6;4345E749C3EBB34A'; User altered. 04:38:57 SYS @ ORCL01 > conn hackme/password1 Connected.
Magic!
You can also use DBMS_METADATA to get the encryption;
04:39:08 SYS @ ORCL01 > set long 10000
04:39:08 SYS @ ORCL01 > select dbms_metadata.get_ddl('USER','HACKME') command from dual;
COMMAND
--------------------------------------------------------------------------------
CREATE USER "HACKME" IDENTIFIED BY VALUES 'S:F299C40420DD341AF9AC4AC89C59A2BB1DFCEF01DB5E3C2B5AD837100117;H:2D3693D1357CF012D9A11EFE3D792C0C;T:101F2A697CA5F77B089C4ECA8EE2DDB82E340D46FE60712445699C5715C3C71BA06532F52CFA987076B51254E5E5A565C44E9F7479018F924707F30874A0BF958D1B8935B7434CF993D3346FF53F28B4;4345E749C3EBB34A'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP"
Please read the COMMENTS to learn about Proxy Accounts – an (admin) alternative from 10G onwards!
Neil Chandler's DB Blog 
Hi.
You can also do a proxy user.
ALTER USER scott GRANT CONNECT THROUGH test_user;
CONN test_user[scott]/test_user
The advantage of this is you are not changing the password, so you are not screwing up any applications/people that are still trying to access the account.
Cheers
Tim…
LikeLike
Tim,
You’ve only been able to do proxy users since Oracle 8i (for OCI connections. Java in 9i but for admins, realistically at 10G when this feature became available to SQL*Plus)
That functionality completely passed me by. How awesome! I love all the stuff I simply don’t know about Oracle.
Here’s you explaining it (with the older password-values thing):
https://oracle-base.com/articles/misc/proxy-users-and-connect-through
And here’s Jonathan doing the same, 10 years ago: https://jonathanlewis.wordpress.com/2006/12/18/proxy-users/
Neil.
btw – I wrote this blog post because I found myself explaining how to do the above to an entire team of DBA’s in the fairly recent past who has no idea about hacking your way into an account – something I’ve been doing for well over 20 years. Think I’m about to change that method to proxy-ing.
LikeLike
Don’t even get me started on the stuff that has passed me by over the years. Typically, I start to read stuff in a new version and find myself saying, “Why have they not listed that as a new feature?”, only to find it was introduced in 9iR1 or something like that. 🙂
Happens all the time! 🙂
Cheers
Tim…
LikeLike
Short answer, proxy user 🙂 .
GG
LikeLike