Knowledge Base
MilesWeb / MySQL

Learn to Create a MySQL User and Grant Permissions via Command Line

Approx. read time : 3 min

MySQL is open-source database management software that enables users to store, manage, and retrieve data later. It offers multiple options to grant specific users permissions within the tables and databases.

In this tutorial, you will learn to create a MySQL user and several commands to grant permissions, revoke them and delete existing users.

At first ensure that you have installed MySQL.

When you start using, you are provided with a username and a password. These credentials will help you to get root access or full control of all your databases and tables.

But sometimes you will need to grant the database access to someone else without giving them complete control.

Suppose you hire developers for your database management but don’t want them to delete or modify any sensitive information.

In this case, you will need to provide them with the credentials of a non-root user. With this, you can keep a watch on the activity developers are doing with your data.

Create a New User Account in MySQL

At first, access the command line and enter your MySQL server using the below command:

This will return the result below which confirms that you are accessing a MySQL server:

Now execute the below command to create a new user:

Here the ‘new_user’ represents the name of the user and IDENTIFIED BY ‘password’ represents the password for that user. You can replace these values with your own.

Execute the below command to grant all the privileges to the newly created user:

If you want the changes to take effect immediately, flush these privileges by running the below command:

Now, your new user account will have the same privileges as the root user has.

Grant Privileges Separately for a MySQL User

It is important that you mention the database name and table name and separate them with a . (period) and without spaces. With this, the root user will have more control over particular data.

Also, replace the PERMISSION_TYPE value with the type of access you want to give to the new user account.

Below are the commonly used commands in MySQL:

  • CREATE — helps users to create a database or table
  • SELECT — allows users to retrieve data
  • INSERT — allows users to add new entries in tables
  • UPDATE — allows users to modify existing entries in tables
  • DELETE — enables users to erase table entries
  • DROP — enables users delete entire database tables

Note: If you use ALL PRIVILEGES permission type, it will allow all of the permissions listed above to the new user.

For using any option from the above list, just replace PERMISSION_TYPE with the appropriate keyword. To grant multiple privileges, separate them with a comma. For example, you can assign CREATE and SELECT to your non-root MySQL user account with the below command:

If there is a situation wherein you need to revoke given privileges from a user, use the below command:

For example, to remove all privileges for your non-root user run the below command:

Finally, to completely delete an existing user account run the following command:

Note: You should have root access to run any of these commands. Ensure to execute FLUSH PRIVILEGES; command after making the changes.

Display Account Privileges for a MySQL User

To find out the privileges already granted to a MySQL user, use the SHOW GRANTS command:

The output will look similar to the below one:

Conclusion

All user data is stored in the form of database and so, is a significant part of every website and web application. A database management system helps in managing and communicating with the database more efficiently. Therefore, you should use the most popular and recommended option — MySQL, because it is reliable as well as user-friendly.

Hope the above commands will help you to create a new MySQL user and grant privileges to its account.

Also Read:

How to Delete a MySQL User?

Disable SSH Login for the Root User

 

Avatar
Pallavi is a Digital Marketing Executive at MilesWeb and has an experience of over 4 years in content development. She is interested in writing engaging content on business, technology, web hosting and other topics related to information technology.
Need help? We’re always here for you.
Register Your Free Domain Name
OFFER EXPIRES IN
04
min
59
sec