How to login as user without changing the password in Oracle database

Last updated on July 29th, 2018 at 09:28 am

In Many cases ,DBA need to login with another user whose credentials/oracle password are not known, also exporting the schema and importing the schema and preserving the same password.Lets check out how it works out

Oracle stores the hash of the username and password is stored in the PASSWORD column of SYS.USER$ and is exposed through the DBA_USERS view. It is independent of platform,database name. Oracle password uses Hash over encryption as hash is not reversible while encryption is not.  So if we know the password column of SYS.USER$, we can easily login as another user using below steps

1) First, we extract the encrypted password using select
‘alter user “‘||username||'” identified by values ”’||extract(xmltype(dbms_metadata.get_xml(‘USER’,username)),’//USER_T/PASSWORD/text()’).getStringVal()||”’;’  old_password
from
dba_users
where
username = ‘TEST_TECH’;

OLD_PASSWORD
——————————————————————–
alter user “TEST_TECH” identified by values ’15EC3EC6EAF863Clkhfdadk’;

or

select username,password from dba_users where username=’TEST_TECH’;

TEST_TECH           15EC3EC6EAF863Clkhfdadk

2) You can now change TEST_TECH password  and Do your Stuff

alter user TEST_TECH identified by sunshine;
connect TEST_TECH/sunshine;
< do your stuff>

3)  When you have completed your testing you can set-back the original encrypted password using the output from the query in step 1:

alter user “TEST_TECH” identified by values ’15EC3EC6EAF863Clkhfdadk’;

Changes in 11g Database

The above works good till 10G. Oracle has introduced some changes in 11g and above .Lets see those changes

  1. 11g introduces new security and the ability to support case sensitive passwords.It introduces SHA-1 hash algorithm
  2. DBA_USERS no longer exposes the PASSWORD value, instead it will be NULL and you will have to go to SYS.USER$ to find the hash value.
  3. Oracle 11g contains a new column PASSWORD_VERSIONS in dba_users. This column is derived from below expression

decode(length(u.password),16,’10G ‘,NULL)||NVL2(u.spare4, ’11G ‘ ,NULL)

  1. The hash password is stored inside the SYS.USER$ table in the column “password” and “spare4”

5.   if the PASSWORD_VERSIONS column of DBA_USERS contains 10G then all of the above still applies.  If it does NOT contain 10g else you have to use the spare4 to rollback to same password

Case 1 When 10g is present in Password Version

1) First, we extract the encrypted password using select

select username,password_versions   from dba_users where username=’TEST_TECH’;

TEST_TECH           10g

Extract the password

select name,password from sys.user$ where username=’TEST_TECH’;

TEST_TECH           15EC3EC6EAF863Clkhfdadk

2) You can now change TEST_TECH password  and Do your Stuff

alter user TEST_TECH identified by sunshine;
connect TEST_TECH/sunshine;
< do your stuff>

3)  When you have completed your testing you can set-back the original encrypted password using the output from the query in step 1:

alter user “TEST_TECH” identified by values ’15EC3EC6EAF863Clkhfdadk’;

Case 2 When 10g is not present in Password Version and 11g only is present

1) First, we extract the encrypted password using select

select username,password_versions   from dba_users where username=’TEST_TECH’;

TEST_TECH           11g

Now ,Extract the password

select name,spare4  from sys.user$ where username=’TEST_TECH’;

TEST_TECH          S:17F9149EFD074674747470D5928640F7727B29F261D887gtguww9A

2) You can now change TEST_TECH password  and Do your Stuff

alter user TEST_TECH identified by sunshine;
connect TEST_TECH/sunshine;
< do your stuff>

3)  When you have completed your testing you can set-back the original encrypted password using the output from the query in step 1:

alter user “TEST_TECH” identified by values ‘S:17F9149EFD074674747470D5928640F7727B29F261D887gtguww9A’;

  1. If Before resetting , the password versions is set to 10g 11g, then we should  pass the identified by values  as “SPARE4;PASSWORD” from sys.user$  to restore the original password

Case 3 When 10g and 11g  is present in Password Version

1) First, we extract the encrypted password using select

select username,password_versions   from dba_users where username=’TEST_TECH’;

TEST_TECH           10g 11g

Extract the password

select name,password,spare4  from sys.user$ where username=’TEST_TECH’;

TEST_TECH           15EC3EC6EAF863Clkhfdadk  S:17F9149EFD074674747470D5928640F7727B29F261D887gtguww9A

2) You can now change TEST_TECH password  and Do your Stuff

alter user TEST_TECH identified by sunshine;
connect TEST_TECH/sunshine;
< do your stuff>

3)  When you have completed your testing you can set-back the original encrypted password using the output from the query in step 1:

alter user “TEST_TECH” identified by values ‘S:17F9149EFD074674747470D5928640F7727B29F261D887gtguww9A;15EC3EC6EAF863Clkhfdadk’;

 

Changes in 12c Database

Oracle has introduced more changes in 12c  .Lets see those changes

  1. 12c  introduces new security and the ability to support case sensitive passwords.It introduces SHA-2 hash algorithm
  2.  Password Version column now also has 12c as value
  3. Same things applies as mentioned above for 11g for login to user without resetting the password

Case 1 When 10g is present in Password Version

1) First, we extract the encrypted password using select

select username,password_versions   from dba_users where username=’TEST_TECH’;

TEST_TECH           10g

Extract the password

select name,password from sys.user$ where username=’TEST_TECH’;

TEST_TECH           15EC3EC6EAF863Clkhfdadk

2) You can now change TEST_TECH password  and Do your Stuff

alter user TEST_TECH identified by sunshine;
connect TEST_TECH/sunshine;
< do your stuff>

3)  When you have completed your testing you can set-back the original encrypted password using the output from the query in step 1:

alter user “TEST_TECH” identified by values ’15EC3EC6EAF863Clkhfdadk’;

Case 2 When 10g is not present in Password Version and 11g only is present

1) First, we extract the encrypted password using select

select username,password_versions   from dba_users where username=’TEST_TECH’;

TEST_TECH           11g

Extract the password

select name,spare4  from sys.user$ where username=’TEST_TECH’;

TEST_TECH          S:17F9149EFD074674747470D5928640F7727B29F261D887gtguww9A

2) You can now change TEST_TECH password  and Do your Stuff

alter user TEST_TECH identified by sunshine;
connect TEST_TECH/sunshine;
< do your stuff>

3)  When you have completed your testing you can set-back the original encrypted password using the output from the query in step 1:

alter user “TEST_TECH” identified by values ‘S:17F9149EFD074674747470D5928640F7727B29F261D887gtguww9A’;

Case 3 When 10g and 11g  is not present in Password Version and 12c only is present

1) First, we extract the encrypted password using select

select username,password_versions   from dba_users where username=’TEST_TECH’;

TEST_TECH           12c

Extract the password

select spare4  from sys.user$ where username=’TEST_TECH’;

S:44F34BA1369DFHJFHF4432522D390A98C29A3B6A34;H:FD30F9DA6ECB907FHFHFHFHFHGHFHJFF
6C10C04D20AFF9492;T:450F7F2A4BB8104E33E7C09FF1698AEA2DE3EBD60BFA681942057D83EE2
DD773BB4F7B1046355D1CB63EBF256BC7B466BB1B3185A0988D1CBAE3276D1B181756DB27BB40505
8C44152DB2DD41074396

2) You can now change TEST_TECH password  and Do your Stuff

alter user TEST_TECH identified by sunshine;
connect TEST_TECH/sunshine;
< do your stuff>

3)  When you have completed your testing you can set-back the original encrypted password using the output from the query in step 1:

alter user “TEST_TECH” identified by values ‘S:44F34BA1369DFHJFHF4432522D390A98C29A3B6A34;H:FD30F9DA6ECB907FHFHFHFHFHGHFHJFF
6C10C04D20AFF9492;T:450F7F2A4BB8104E33E7C09FF1698AEA2DE3EBD60BFA681942057D83EE2
DD773BB4F7B1046355D1CB63EBF256BC7B466BB1B3185A0988D1CBAE3276D1B181756DB27BB40505
8C44152DB2DD41074396 ‘;

I hope you like this article on password management in Oracle and how to login as user without changing the password

Leave a Reply