In many cases, DBA needs to log in with another user whose credentials/oracle password are not known, also exporting schema and importing the schema, and preserve the same password. Let’s check out how it works out with alter user identified by values
Oracle Database 10G
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 the platform, and database name. Oracle password uses Hash over encryption as the 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 well till 10G. Oracle has introduced some changes in 11g and above. Let’s see those changes
(1) 11g introduces new security and the ability to support case-sensitive passwords. It introduces the 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 the below expression
decode(length(u.password),16,'10G ',NULL)||NVL2(u.spare4, '11G ' ,NULL)
(4) 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 the 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';
(6) 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 and above
Oracle has introduced more changes in 12c .Lets see those changes
- 12c introduces new security and the ability to support case sensitive passwords.It introduces SHA-2 hash algorithm
- Password Version column now also has 12c as value
- 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 ';
Here I am assuming we are not changing the sqlnet.ora files and its parameters. If we are changing that, then it will be different depending on the change
I hope you like this article on password management in Oracle and how to login as user without changing the password using alter user identified by values
Related Articles
ORA-01017: invalid username/password; logon denied : Learn the possible resolution of ORA-01017 invalid username/password; logon denied. How to resolve it quickly without much efforts
Oracle Database Interactive Quick Reference Guide | 11g & 12c : Useful Reference guide for 11g/12c for DBA,developers and administrator
Oracle Database Security Best Practices : This page contains awesome Oracle Database Security Tips to help DBA,developers and administrator
oracle dba interview questions and answers : You should not miss these 60 awesome oracle apps dba interview questions.Must read to succeed in interviews and jobs.Download also available
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_8003.htm