Change User Password
Periodically, you may like to change your own password for more protection. In fact, there're many ways that can change user's password in oracle.
In this post, we'll introduce changing password in tools like SQL*Plus, SQL developer, Toad for Oracle and PL/SQL developer.
- ALTER USER Statement
- SQLPlus Change Password
- SQL Developer Change Password
- Toad for Oracle Change Password
- PL/SQL Developer Change Password
On the other side, if you're facing some security issues, you do have ways to expire user passwords manually to make them change it.
ALTER USER Statement
Users can always use ALTER USER IDENTIFIED BY statement to change their own password in any tool containing SQL editor.
Let's see how we change the password for the current session.
SQL> show user
USER is "HR"
SQL> alter user hr identified by Welcome1;
User altered.
One command can overwrite current password with the new one.
Two topics about user's password are worth mentioning, the first one is a case-sensitive problem, the other is a connection problem.
Password is Case-Sensitive
Although we didn't add double quotations to the password string, it's case-sensitive by default from 11g. For example, we intentionally use it in lowercase.
SQL> conn hr/Welcome1@orclpdb
ERROR:
ORA-01017: invalid username/password; logon denied
We saw ORA-01017: invalid username/password; logon denied. The error is pretty common for users.
To correctly login, we should use exactly what we entered at changing the password.
SQL> conn hr/Welcome1@orclpdb
Connected.
Password Containing Special Characters
For passwords which contain special characters should be handled carefully. That is, you should add double quotations to make statements work properly.
Changing Password
Quotation marks are required to enclose the string containing special characters.
SQL> alter user hr identified by "Welcome1@Boston";
User altered.
Connecting Database
Again, quotation marks are required.
SQL> conn hr/"Welcome1@Boston"@orclpdb
Connected.
Work with SQL*Plus Problem
You can hardly use such special password to directly login a database by sqlplus.
C:\Users\ed>sqlplus hr/"Welcome1@Boston"@orclpdb
...
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified
Such command that tries to connect to a database along with sqlplus always returns error, most likely ORA-12154.
To workaround it, we can make a batch file for execution.
[oracle@test ~]$ vi execute.sh
sqlplus -s /nolog << EOF
conn hr/"Welcome1@Boston"@orclpdb
show user;
select count(*) cnt from employees;
exit;
EOF
Let's see the result of execution.
[oracle@test ~]$ chmod u+x execute.sh
[oracle@test ~]$ ./execute.sh
USER is "HR"
CNT
----------
107
SQLPlus Change Password
SQLPlus (i.e. SQL*Plus) is a very basic client tool provided by Oracle, which supports most platform you have ever used.
Beside ALTER USER IDENTIFIED BY statement we have talked about in the above, we can use PASSWORD command in sqlplus to change password.
Change Your Own Password
Without specifying any username, we intend to change password for current user (session).
SQL> show user
USER is "HR"
SQL> password
Changing password for HR
Old password:
New password:
Retype new password:
Password changed
In fact, PASSWORD command is essentially implemented by ALTER USER IDENTIFIED BY statement.
Change Other User's Password
You can change other user's password as long as you have ALTER USER system privilege.
SQL> password oe
Changing password for oe
New password:
Retype new password:
Password changed
Lack of ALTER USER system privilege, you got ORA-01031: Insufficient Privileges.
SQL Developer Change Password
You may take the following 4 steps to change your own password in SQL developer.
Right Click on Connection
You can change the password when the connection is connected or disconnected, but I recommend that you should change the password on an active connection.
Click on "Reset Password" in Menu
Yes, that's correct. In SQL developer, changing password is called "Reset Password", although it confuses some developers.
Enter Old and New Password (2 times) in Dialog
The current used and new password are required in the pop-up dialog, they should be typed carefully. Don't forget to enter the new password 2 times to confirm it.
4. Click OK to Confirm the Change
If there's no problem, click "OK" to confirm. If there's any hesitation, then "Cancel" the dialog.
Please note that, your current connection won't be affected by the new password, next connection will.
Toad for Oracle Change Password
You may take the following 4 steps to change your own password in Toad for Oracle
Click on Session in Top Function Menu
Password can only be change in current active session, you can't do it in a disconnected session.
Click on "Change Password" in Menu
Before you change the password, you'd better make sure that current session is the right user to be changed its password.
Enter Old and New Password (2 times) in Dialog
The current used and new password are required in the pop-up dialog, they should be typed carefully. Don't forget to enter the new password 2 times to confirm it.
Click OK to Confirm the Change
If there's no problem, click "OK" to confirm. If there's any hesitation, then "Cancel" the dialog.
A successful message of changing password pops up to your screen, just close it.
Please note that, your current connection won't be affected by the new password until the next time.
PL/SQL Developer Change Password
You may take the following 4 steps to change your own password in PL/SQL Developer.
Click on Session in Top Function Menu
Password can only be change in current active session, you can't do it in a disconnected session.
Click on "Change Password" in Menu
Before you change the password, you'd better make sure that current session is the right user to be changed its password.
Enter Old and New Password (2 times) in Dialog
The current used and new password are required in the pop-up dialog, they should be typed carefully. Don't forget to enter the new password 2 times to confirm it.
Click OK to Confirm the Change
If there's no problem, click "OK" to confirm. If there's any hesitation, then "Cancel" the dialog.
Please note that, your current connection won't be affected by the new password until the next time.