Hi everyone,
Sometimes we need to connect to the database using an unusual schema that we don’t even know the password, maybe because it was created through a script in a release or the latest DBA never stored it somewhere public or maybe due to any other reason (whatever, right? you just need to connect there and end of the story), but anyhow, you need to login using this schema specifically (to create/delete synonyms, dblinks, jobs etc…). How would you do that without the password?
Well, there are 2 simple and very useful ways to do that:
- Use proxy connection (connect through);
- Save the password hash -> Change -> Perform what you need -> Change back to the original password using the hash.
PS: The second approach might be more risky because the password may be set into some application, datasource, etc… So be aware of the impact before actually changing the password.
Using Proxy connection:
This is very simple, and in order to do that, you have to connect as sysdba:
sqlplus / as sysdba
Then you will say to the database: “Alright mate, now you will connect to the user A, through the user B, even not knowing user’s A password”, with the following command:
alter userA grant connect through userB;
By performing this command, you’ll be able to access the user A, through the user B. But how does the connection works?
When you are connecting to the database, do it like this:
conn userB[userA]/passB@database
See that we have put the schema’s name in [ ]’s. This is how it works. Once you connect to the database and run:
show user
You will see: “userA”
Using Password Hash:
As said before, this one should be faced more carefully, as it might affect something, because we will temporarily change the password of the user.
First of all, connect to the database with an user who have “grant select any dictionary” or at least grant select on dba_users. Then run:
select password from dba_users where username='schema';
You will have a result like this:
PASSWORD ------------------------------ F894844C34402B67
Now that you have the CURRENT password hash saved, change the users password:
alter user schema identified by newpassword;
Doing that, you will be able to connect to the user using the new password. Do what you need, and when you are done, you need to change the password back to the original one like this:
alter user schema identified by values 'F894844C34402B67';
Please notice the command VALUES there, using the saved password hash. This is the command which allow us to set up the user’s password using the hash.
That’s it for today guys, very simple, but useful.
Have a wonderful week.
Rafael.