How to Reset Oracle User Password After Expiry?

The admin of the Oracle database can create the user’s accounts, and grant them appropriate privileges. The user can access the database according to their privileges using the account. However, sometimes, the user password expires due to security reasons, and the password need to be reset. The Oracle database provides a feature for the administrator to reset the user’s password after expiry.

This post will provide a procedure to reset the Oracle user password after expiry.

How to Reset Oracle User Password After Expiry?

To reset the Oracle user password after expiry, login to the database as “SYSDBA” by typing the following command:

SQLPLUS SYS/root1234 AS SYSDBA

In the above command, “root1234” is the password of the “SYS” user.

Output

The output shows that the user has been logged in.

Unlock User Account

The “ALTER USER” clause with “ACCOUNT UNLOCK” can be utilized to unlock the user’s account after the login as an “SYSDBA”:

ALTER USER C##DANI ACCOUNT UNLOCK;

In the above statement, “C##DANI” is the username.

Output

The output depicts that the user has been altered.

Reset User Password

The password of the Oracle database user can be changed using the “ALTER” command. The example is given below:

ALTER USER C##DANI IDENTIFIED BY dani321;

In the above example, “dani321” is the new password that is used with “IDENTIFIED BY”.

Output

The output “User altered” showed that the user’s password has been changed.

Enforcing Password Expiration

Use the “PASSWORD EXPIRE” clause with the “ALTER” command to force the user to change their password after the next login. The command to do that is given below:

ALTER USER C##DANI PASSWORD EXPIRE;

Output

The output depicts that the user has been altered.

Let’s confirm if the user’s password has been changed or not by logging into the user’s account. The command to log in to the “C##DANI” user is given below:

SQLPLUS C##DANI/dani321

Output

The output demonstrates that upon logging in, the password set by “SYSDBA” expired and the user was asked to specify a new one.

Alternatively, the following statement can be used to change the user’s password after expiry, unlock the user’s account and force the user to change the password after the next login in a single statement:

ALTER USER C##DANI IDENTIFIED BY dani1234 ACCOUNT UNLOCK PASSWORD EXPIRE;

In the above statement, “dani1234” is the user’s new password.

Output

The output showed that the specified alterations were made successfully.

Conclusion

To reset the Oracle user password after the expiry, login to the database as an “SYSDBA”. Then use the “ALTER” statement with “ACCOUNT UNLOCK” to unlock the user account. The “IDENTIFIED BY” clause can be used to reset the user’s password. Use the “PASSWORD EXPIRE” to force the user to change the password after the next login. This guide has explained how to reset the Oracle user password after expiry.

How to Reset Oracle User Password After Expiry?