Alter User Statement in Oracle is used to change the Authentication of a particular user. For example, you want to change the password of the user because you found any malicious login through those credentials. Here, ALTER STATEMENT can be used to change the password of the particular account.
Also, if you found that the user's account has been locked for some reason or you need to Lock the same account. ALTER STATEMENT helps to perform the same task as well.
1. Oracle Alter User Identified By
Without REPLACE
ALTER User User_name IDENTIFIED BY Password
With REPLACE
In case you remember the old password for the user, you can use the code to replace the old password with the new one:
ALTER User User_name IDENTIFIED BY
password REPLACE old_password
Example
We have a user named NEW_USER and we want to change the password of NEW_USER from manager to manager2. To perform the task we can do it either by using Replace keyword or without using Replace keyword. Find the code to do it in both the ways below:
ALTER User NEW_USER IDENTIFIED BY manager2
OR
ALTER User NEW_USER IDENTIFIED BY manager2 REPLACE manager
Output
2. Oracle Alter User Unlock
Syntax
ALTER USER User_name
ACCOUNT LOCK|UNLOCK;
Example
We have a user NEW_USER and let's suppose a user tried to login into that and it displayed that the account is locked.
In that case, he will ask DBA to unlock that account. Follow the code for the same as below:
Alter user NEW_USER account unlock;
Output