Resetting Your Oracle User Password with SQL Developer
- by thatjeffsmith
There’s nothing more annoying than having to email, call, or log a support ticket to have one of your accounts reset. This is no less annoying in the Oracle database. Those pesky security folks have determined that your password should only be valid for X days, and your time is up. Time to reset the password!
Except…you can’t log into the database to reset your password.
What now?
Wait a second, look at this nifty thing I see in SQL Developer:
Right click on my connection, reset password not available! Why not?
The JDBC Driver Doesn’t Support This Operation
We can’t make this call over the Oracle JDBC layer, because it hasn’t been implemented. However our primary interface, OCI, does indeed support this. In order to use the Oracle Call Interface (OCI), you need to have an Oracle Client on your machine.
The good news is that this is fairly easy to get going.
The Instant Client will do.
You have two options, the full or ‘Lite’ Instant Clients. If you want SQL*Plus and the other client tools, go for the full. If you just want the basic drivers, go for the Lite.
Either of these is fine, but mind the bit level and version of Oracle!
Make sure you get a 32 bit Instant Client if you run 32 bit SQL Developer or 64 bit if you run 64
Here’s the download link
What, you didn’t believe me?
Mind the version of Oracle too!
You want to be at the same level or higher of the database you’re working with.
You can use a 11.2.0.3 client with 11.2.0.1 database but not a 10gR2 client with 11gR2 database.
Clear as mud?
Download and Extract
Put it where you want – Program Files is as good as place as any if you have the rights. When you’re done, copy that directory path you extracted the archive to, because we’re going to add it to your Windows PATH environment variable.
The easiest way to find this in Windows 7 is to open the Start dialog and type ‘path’. In Windows 8 you’ll cast your spell and wave at your screen until something happens.
I recommend you put it up front so we find our DLLs first.
Now with that set, let’s start up SQL Developer.
Check the Connection Context menu again
Bingo!
What happened there?
SQL Developer looks to see if it can find the OCI resources. Guess where it looks? That’s right, the PATH. If it finds what it’s looking for, and confirms the bit level is right, it will activate the Reset Password option.
We have a Preference to ‘force’ an OCI/THICK connection that gives you a few other edge case features, but you do not need to enable this to activate the Reset Password.
Not necessary, but won’t hurt anything either.
There are a few actual benefits to using OCI powered connections, but that’s beyond the scope of today’s blog post…to be continued.
Ok, so we’re ready to go.
Now, where was I again?
Oh yeah, my password has expired…
Right click on your connection and now choose ‘Reset Password’
You’ll need to know your existing password and select a new one that meets your databases’s security standards.
I Need Another Option, This Ain’t Working!
If you have another account in the database, you can use the DBA Panel to reset a user’s password, or of course you can spark up a SQL*Plus session and issue the ALTER USER JEFF IDENTIFIED BY _________; command – but you knew this already, yes?
I need more help ‘installing’ the Instant Client, help!
There are lots and lots of resources out there on this subject. But I also know from personal experience that many of you have problems getting this to ‘work.’ The key things to remember is to download the right bit level AND make sure the client install directory is in your path. I know many folks that will just ‘install’ the Instant Client directly to one of their ‘bin’ type directories. You can do that if you want, but I prefer the cleaner method. Of course if you lack admin privs to change the PATH variable, that might be your only option.
Or you could do what the original ORA- message indicated and ‘contact your DBA.’
