In this tutorial we’ll learn how to lock and unlock a user’s account in MySQL, we’ll also learn how to create a user and how to check if the user is locked or not.
How to create users?
Before learning how to lock or unlock a user in MySQL, we must know if that user actually exists in our system or not and how to create one if needed. Let’s see the SQL query for listing all the users present in MySQL on our systems.
SELECT user FROM mysql.user;Code language: SQL (Structured Query Language) (sql)If you want to see the detailed description of users in MySQL then you can use the below query.
DESC user; Code language: SQL (Structured Query Language) (sql)To view the current user, use the below command.
SELECT user();Code language: SQL (Structured Query Language) (sql)For viewing the details of users with their hostname and database access, use the below command.
SELECT user, host, db, command FROM information_schema.processlist; Code language: Scala (scala)To create a new user, use the below query and do the required changes such as the name of the user. ‘sam’ is the name of the user which we are creating, ‘localhost’ is the hostname, and ‘root’ is the password for authentication of the user.
CREATE USER 'sam'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';Code language: SQL (Structured Query Language) (sql)Now that we have learned how to create a user, let’s see how to assign them privileges. For the above user, we will be assigning all the privileges for all the databases present in our MySQL.
GRANT ALL PRIVILEGES ON *.* TO 'sam'@'localhost';Code language: SQL (Structured Query Language) (sql)Also read: MySQL User Creation, Display, Delete – Complete Guide
How to check the account status of the user?
Now let’s see how we can find if a user’s account is locked or unlocked. We’ll be querying for ‘sam’ (we created this user in the last section) and checking whether his account is active or not. We’ll be taking three parameters namely, user, host, and account_locked. The user will revert the name of the user, the host will revert the hostname on which the user exists, and account_locked which will revert ‘N’ if the account is not locked and ‘Y’ if the account is locked. To do so we will be using the below query.
SELECT user, host, account_locked FROM mysql.user WHERE user = 'sam';Code language: SQL (Structured Query Language) (sql)
How to lock the user’s account?
How that we know how to check if our user’s account is locked or not. Let’s see how we can lock the user’s account. To do that we will need the name of the user which in our case is ‘sam’ and the hostname which is ‘localhost’ if you know these two things you’d be good to go. We use ALTER command to change the account settings for users. Below is the query for locking a user’s account.
ALTER USER sam ACCOUNT LOCK;Code language: SQL (Structured Query Language) (sql)Now if you’ll check again whether this particular user’s account is locked or not, you’ll see ‘Y’ instead of ‘N’ with respect to the accout_locked column. This means that now this user’s account is locked.
NOTE: Do not lock the root user or current user as they will mostly have all the privileges and errors might occur.
How to unlock the user’s account?
Finally, let’s see how we can unlock the user’s account. We’ll be using the same user account which we created earlier as ‘sam’ and the host name is also the same ‘localhost’. Below is the query for unlocking the user.
ALTER USER sam ACCOUNT UNLOCK;Code language: SQL (Structured Query Language) (sql)Now if you’ll check the status for this user, you’ll see that it’s again active which means that your user is unlocked. You can also try logging in with this user and it will work.
Conclusion
In this tutorial we learned how to check the users present in MySQL, we learned to create new users and granted them privileges. We also learned to check the account status of a user and in the end, we learned to lock and unlock the user.