Working as a database administrator sooner or later we will get confronted with the customer or developer request about executing a SQL Statement for them within the production database.

Sure, as the DBA we are and with all the rights we have, it isn’t really a big deal for us to manipulate objects within the context of another schema owner. We all know that it just needs fully qualified the object like ‘schema_name.object_name’ and the database will know which database object we reference to.

Just how often we will get such full specified scripts to execute from our customers? Guess never! So it needs us to overwork all the scripts and at the end even to overtaking responsibility for the scripts we changed.

How much more convenient would it be for us to directly work within the user context in question? Impossible?! We need the user password first! The password the user has forgotten to share with us first!

And unfortunately we are also not able to change it, because it is hard coded within the application. And changing it would leave the application inoperable later on?!

Now working with Oracle this situation can get solved for sure! You wonder how?

Within Oracle versions older than 11g, we have a DES hash password which can queried from database with the following SQL Command:

• DBA_USERS : SELECT username, password FROM DBA_USERS;

• SYS.USER$ : SELECT name,password FROM SYS.USER$ WHERE password is not null;

Starting with Oracle 11g we can query the new SHA-1 hash by using this SQL Command:

• SYS.USER$ : SELECT name,spare4 FROM SYS.USER$ WHERE password is not null;

   
Now it is possible to change a password temporarily. And using this little trick here, we will be able to act as a different user.

SQL> select username,password from dba_users where username=’SCOTT’;

USERNAME PASSWORD
——– —————-
SCOTT F894844C34402B67

SQL> alter user scott identified by mypassword;

Now we can login with the following credentials: scott/mypassword

And after our work is done we can change the password back by using an undocumented feature called “by values”

SQL> alter user scott identified by values ‘F894844C34402B67’;

And now we just revert our password manipulation by using exactly the password hash we have queried earlier from the database and all will be fine again.