Accessing a user when you don’t know the password

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  /

---------- ------------------------------------------------------------------------------------------------------------------------
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;

04:38:35 HACKME @ ORCL01 > show user

04:38:35 HACKME @ ORCL01 > connect sys/oracle as sysdba

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


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;


CREATE USER "HACKME" IDENTIFIED BY VALUES 'S:F299C40420DD341AF9AC4AC89C59A2BB1DFCEF01DB5E3C2B5AD837100117;H:2D3693D1357CF012D9A11EFE3D792C0C;T:101F2A697CA5F77B089C4ECA8EE2DDB82E340D46FE60712445699C5715C3C71BA06532F52CFA987076B51254E5E5A565C44E9F7479018F924707F30874A0BF958D1B8935B7434CF993D3346FF53F28B4;4345E749C3EBB34A'

Please read the COMMENTS to learn about Proxy Accounts – an (admin) alternative from 10G onwards!


SYSMAN password change

Some days you just forget the dot all of the i’s.

I had just installed a new RAC cluster, got it all up and running and was using DBCONSOLE to check the system out – no access to the Production Grid Control for this cluster yet. I then made a few more configuration changes and restarted one of the nodes. I was rather surprised that the console could no longer access the system. It was claiming the instance was down, and asking for server logins to allow restart. I was quite sure the instance was available, mainly because I was connected using SQL Developer and executing queries.

So, what went wrong? What config had changed before I restarted the nodes? I checked my notes and… I was hardening passwords. One of the passwords I changed was the SYSMAN password. However, I had completely neglected to inform the EM agent for the console that I had changed the password! Idiot.

cd $ORACLE_HOME/<node_database>/sysman/config
 - oracle.sysman.eml.mntr.emdRepPwd=<clear-text-password>
 - oracle.sysman.eml.mntr.emdRepPwdEncrypted=FLASE

emctl stop dbconsole
emctl start dbconsole
…and all is well again

This blog entry was brought to you by Pierrot.

Complex Passwords

Increasing numbers of Yahoo mail passwords appear to have been compromised; I don’t use Yahoo [although in a historically stupid move, I have multiple email addresses from multiple providers including hotmail, gmail, my ISP and my own domain ]. Anyway, I have been getting an increasing number of spam emails from friends and acquaintances with Yahoo accounts. Not from any other source. I have been multiply spammed from multiple yahoo accounts this year, but from no other provider. The conclusion I draw from this is that either Yahoo has had its password file compromised and the spammers are slowly working their way through it, or it has a significant hole in its security, or there is a focussed piece of malware out there harvesting Yahoo passwords.

Either way, I would strongly recommend that anybody who uses a Yahoo email go and change their password, make it computer-complex (i.e. long), write it on a Post-it and stick it next to your desk (at home – not in the office where everyone can read it)

WHAT! I hear you cry. Why do THAT! You’re mad! Well, no. Brute force attacks are rare, and they will generally use standard dictionary words. I hate to tell you, but hackers know you replace E with 3, A with 4 and L with 1. So your password of AFR1C4 it as much a dictionary word as AFRICA to a computer. [ If you want a really hard-to-crack, easy-to-remember password, I suggest you refer to this XKCD cartoon ]

The likelihood is that your password will be compromised by malware and not brute force attacks, in which case it doesn’t matter how complex it is. The chance it will be compromised by a burglar looking in your desk drawer is very low indeed (although people with teenage children need to be a bit more cautious.)

And change your passwords occasionally – at least once a year. How many of you out there have 2 or 3 different passwords that they use everywhere? A (seemingly) complex one for your bank account and “password” for your forum accounts? And you have NEVER changed them as it would mean changing 200 accounts and it’s too much like hard work? Thought so. One day you will be pwned by the hackers.