Skip to content

Oracle Change User Password

  • by

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.

  1. ALTER USER Statement
  2. SQLPlus Change Password
  3. SQL Developer Change Password
  4. Toad for Oracle Change Password
  5. 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.

SQL Developer - Right Menu - "Reset Password"

SQL Developer - Right Menu - "Reset 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.

SQL Developer - Enter Current and New Password

SQL Developer - Enter Current and New Password

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.

Toad for Oracle - Top Menu - Session - "Change Password"

Toad for Oracle - Top Menu - Session - "Change 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.

Toad for Oracle - Password Dialog - Enter Current and New Password

Toad for Oracle - Password Dialog - Enter Current and New Password

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.

Toad for Oracle - Password Changed Successfully

Toad for Oracle - Password Changed Successfully

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.

PL/SQL Developer - Top Menu - Session - "Change Password"

PL/SQL Developer - Top Menu - Session - "Change 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.

PL/SQL Developer - Password Dialog - Enter Current and New Password

PL/SQL Developer - Password Dialog - Enter Current and New Password

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.

Leave a Reply

Your email address will not be published. Required fields are marked *