Last Updated: 28 June, 2023
User management in MySQL involves creating, modifying, and removing user accounts and assigning appropriate privileges to control their access and actions within the database system.
MySQL provides various commands and methods for managing users and their privileges.
On this tutorial page, we are going to learn how to create a user in MySQL as well as how to grant and revoke user's privileges.
To create a user in MySQL, we can use the CREATE USER statement.
Here's an syntax of how to create a user with a username and password:
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
Let's break down the syntax:
Let's see the example below to create a new user:
CREATE USER 'shweta'@'localhost' IDENTIFIED BY 'test123';
After executing this statement, the user will be created with the provided username and password. Note that the user won't have any privileges initially. You will need to grant appropriate privileges to the user using the GRANT statement to define what actions the user can perform in the database.
After creating a user, we need to grant appropriate privileges to that user. For example, to grant all privileges on a specific database to a user, we can use the following command:
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'hostname';
We can specify specific privileges or limit them to specific tables or columns as per the requirements.
If we need to revoke previously granted privileges from a user, we can use the REVOKE command. For example:
REVOKE ALL PRIVILEGES ON database_name.* FROM 'username'@'hostname';
This command revokes all privileges on the 'database_name' database from the user 'username' on the 'localhost' host.
That's all, guys. I hope this MySQL article is helpful for you.
Happy Learning... 😀
feedback@javabytechie.com