In today’s data-driven world, successful organizations require the ability to process and manipulate data effectively as part of their business foundation. Structured Query Language (SQL) is a powerful tool that enables users to manipulate relational databases. Within SQL, there is an entity where data can be stored, retrieved, and manipulated.
In this article, we will explore the steps to unlock a locked user account in MySQL. Unlocking a user account in MySQL refers to restoring access to the user who has been temporarily restricted from logging in due to certain security measures or some other reasons. To unlock a user account in MySQL, administrators utilize the ALTER USER statement. which allows to change of user account properties, including unlocking the account.
SQL ALTER USER Command
The SQL ALTER command is primarily used to modify existing database objects such as tables, views, or indexes. However, SQL itself does not have a specific ALTER command for modifying user accounts directly. The ALTER USER statement is often used to modify user account properties, including password changes, permissions, and account status.
ALTER USER ‘user-name’@’host-name’
IDENTIFIED BY ‘new_password’
[ACCOUNT {LOCK | UNLOCK}]
[GRANT {role [, role… ] | ALL PRIVILEGES} | REVOKE {role [, role… ] | ALL PRIVILEGES}];
- IDENTIFIED BY new_password: Changes the password for the user to the specified new password.
- GRANT: Grants specified roles or privileges to the user.
- REVOKE: Revokes specified roles or privileges from the user.
- ACCOUNT: Locks or unlocks the user account.
LOCK User
Identify a user account that needs to be locked. The ALTER USER command in MySQL includes an ACCOUNT option to control user access. It is used to either lock or unlock a user account, effectively enabling or disabling login capabilities.
Syntax:
ALTER USER <username>@<host-name> ACCOUNT LOCK
Example
ALTER USER "gfg"@"localhost" ACCOUNT LOCKThe above command will lock the user named with gfg. Since we locked the user(gfg) we can’t able to login into gfg account.
Steps to UNLOCK User
To manage user account statuses, such as locking or unlocking, you must log a in to MySQL account which has the administrative privileges, Only DB Admin has access to UNLOCK or LOCK the user. For instance, I am login into my root account.
root account login
Identify the user account that needs to be unlock. The mysql.user table has all user-related data in MySQL. To Identify which user accounts require unlocking, a query can be executed to select the usernames and their corresponding account statuses on mysql.user table.
All users
To unlock a user account in MySQL, the ALTER USER statement is executed with the ACCOUNT UNLOCK clause. This action restores the user’s ability to log in to the database. The command requires the username and, if necessary, the hostname.
Syntax:
ALTER USER “username@hostname” ACCOUNT UNLOCK
Account Unlocked
After executing the above command, we attempted to log in to the ‘gfg’ account through CLI and were able to login into gfg account.
Login into unlocked accountc
Conclusion
Unlocking a user account in MySQL through the ALTER USER statement is not only about restoring access but also about maintaining the security and operational integrity of the database. It’s a controlled approach that allows administrators to manage user access in response to various situations, such as potential security breaches or policy violations. By unlocking accounts, administrators ensure that users can continue their work without unnecessary disruption.