MySQL Create User

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.

1. Creating a User:

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:

• CREATE USER is the statement used to create a new user.
• 'username'@'localhost' specifies the username and the host from which the user can connect. In this example, the user can only connect from the localhost. We can replace 'localhost' with the appropriate hostname or IP address if we want to allow connections from a different host.
• IDENTIFIED BY 'password' sets the password for the user. Replace 'password' with the desired password for the user.

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.

Granting Privileges:

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.

Revoking Privileges:

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.