How to create a new User in MySQL

In this MySQL Database tutorial, we will discuss creating a new User in MySQL. We will learn to add users through MySQL Workbench and MySQL command line client.

In MySQL we have a root user through which we access our database but many times while working on any project we need to give access to others also. To limit the access of another person we create a new user in the database with limited grants and privileges.

Let’s move and learn the steps to create a user in MySQL using Workbench and Command-Line with an example.

Create a User in MySQL Workbench

Firstly, open MySQL Workbench and log in with the “root” user with its password, and after that follow the steps below steps.

Step 1: On your screen, you can see the schemas in the upper left corner. Near the Schemas there is an Administration button, open that Management menu by clicking it as shown in the below image.

Step 2: After clicking ‘Administration‘ you will see that under the ‘Management’ menu there is an option for the “User and privileges” window, click on it as shown in the below image.

Step 3: In this step, we will see the “User and Privileges” window in which we can see our current user. To add a new user click on the “Add Account” as shown in the below image.

Step 4: On clicking Add Account it will open the new window Details for account new user, where you need to write your preferred user name in the Login name field and then enter the password and confirm password. After entering these fields click Apply.

Step 5: On entering the Account limits tab we have to set limits for newly created user like Maximum queries, Maximum updates, Maximum connections, and Concurrent Connections. We will set all limits to 1000 and you also set these limits as per your preference.

Step 6: Next tab will be Administrative Roles and here we have to allow or disallow certain global privileges. By selecting DBA (Database Administrative) we can allow all the permissions.

Step 7: The next tab will be Schema Privileges, in this tab we can allow or restrict the new users to access certain databases or schemas. We will move ahead without any customization in this and click Apply.

Step 8: Now close the MySQL workbench and at this point, we will log in the credentials of the newly created user. So, open the MySQL workbench again and next to the MySQL connections there is a “+” button, click this button to create a new connection in MySQL.

Step 9: On clicking the “+” button it will open the Setup New Connection window and there we will enter the fields Connection Name as “TestConnection” and Username as “newuser”. Make sure the Hostname will be 127.0.0.1 and the Port will be 3306.

Here, newuser is the username that we have just created. Then click on the Store in Vault to set the password as shown in the below image.

Step 10: When we will click Store in Vault it will open a window where it will ask for the password of the newly created user. Here, enter the password which you have set for the newuser and click on the OK button as shown in the below image.

Step 11: On clicking ok, it will open the window where we can see our newly created user. In the below image, you can see created user newuser with port 127.0.0:3306 and network name TestConnection.

Now the User is created and it can be accessed by clicking on TestConnection and entering the credentials of the newly created User. Next, we will move and learn to create a user in MySQL using a command line client.

Read How to create database using MySQL

Create User in MySQL Command line client

In MySQL command line client we use CREATE USER statement to create a new user. In the below code syntax for creating a new user in MySQL command line client is shown.

Syntax:

CREATE USER  account_name IDENTIFIED BY 'password'; 

In the further steps, we will see how to create a new user in the MySQL command line and give access to the grants to that user.

Step 1: Open the MySQL command line client and enter the credentials of the root user and press enter.

Step 2: To create a new user follow the below command.

mysql> create user 'testuser'@'localhost' identified by 'admin@123';

It is optional to choose ‘localhost’ and instead we can use ‘%’ then it will choose the default server.

Step 3: In this step, we will view our created user by using the following command.

mysql> select user from mysql.user; 

It will show us the list of the users present in the database of the MySQL command line client as mentioned in the below picture.

Step 4: In this step, we will grant privileges to the newly created user. To grant ALL access to the user use the below command.

mysql> GRANT ALL PRIVILEGES ON * . * TO testuser@localhost;  

In case you want to give a specific grant of privileges, you can customize the privileges using the below command.

mysql> GRANT CREATE, SELECT, INSERT ON * . * TO testuser@localhost;  

We can also revoke the privileges by using the flush statement as mentioned in the below command.

mysql> FLUSH PRIVILEGES;

Step 5: Now we will view the grants accessed to the new user. Since we have used “ALL”, we will see the list of all grants present in the database.

Now we have created a new user in the MySQL Command line client and we have also viewed the created user and the privileges we have granted to the new user.

Conclusion:

In this MySQL Database tutorial, We have learned How to create a user in MySQL Workbench and in MySQL Command Line Client. We also learned about how to give grants and privileges and how we can customize those grants while giving access to the newly created user.

We also learned to view the created database and get access to it on the database in MySQL Workbench.

You may also like to read the following articles:

• How to Install MySQL on Windows
• Different Datatypes in MySQL