POUG 2-day event

Just a quick post to let you know I will be speaking, with a lot of other Oracle ACE and other excellent speakers at the Pint with/Polish Oracle User Group (Chapter IV), taking place on 7th/8th October in Warsaw. Click on Buy Ticket link to see some of the other speakers, or look at the images below.

Hope to see you there! Should be fabulous.

 

poug_speakers_1poug_speakers_2

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  /

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!

%d bloggers like this: