How to create a MySQL user and grant privileges

To create a MySQL user, connect to your MySQL server with an account that has the required permissions, use the CREATE USER command, and assign database privileges with GRANT. You can then check the user’s access with SHOW GRANTS, remove permissions with REVOKE, or delete the account with DROP USER.

In this tutorial, you’ll learn how to create and manage MySQL users through the command line, grant only the permissions they need, and restrict access to specific databases.

Prerequisites for creating a MySQL user

Before creating a MySQL user, make sure you have a running MySQL server, command-line access, and an administrative account with permission to create users and grant privileges.

This tutorial uses a Linux VPS. Connect to your server through SSH, then access MySQL with an administrative account. For a password-based root account, use:

mysql -u root -p

Depending on your server’s authentication setup, you may need to use sudo mysql instead.

If you use Hostinger Web or Cloud hosting, you can create and manage database users through Websites → Dashboard → Databases → Management, without running these administrative SQL commands.

How to create a MySQL user and grant privileges

Just as you start using MySQL, you’ll be given a username and a password. These initial credentials will grant you root access or full control of all your databases and tables.

However, there are times when you’ll need to give the database access to someone else without granting them full control.

For instance, you hire developers to maintain your databases, but you don’t want to provide them with the ability to delete or modify any sensitive information.

In that case, you should give them the credentials of a non-root user. This way, you can keep track of what the developers can and cannot do with your data.

In this part, we will explain how to create a user account in MySQL with all privileges to your database. In a practical sense, it’s not wise to give full control to a non-root user. However, it’s still a good entry-point to learn about user privileges.

To create a new user account in MySQL, follow these steps:

1. Access command line and enter MySQL server:

   mysql

2. The script will return this result, which verifies that you are accessing a MySQL server.

   mysql>

3. Then, execute the following command:

   CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'strong_password';

4. new_user is the name we’ve given to our new user account and the IDENTIFIED BY ‘password’ section sets a passcode for this user. You can replace these values with your own, inside the quotation marks.
5. In order to grant all privileges of the database for a newly created user, execute the following command:

   GRANT SELECT, INSERT, UPDATE ON example_db.* TO 'new_user'@'localhost';

Replace new_user with your preferred username and strong_password with a secure password.

The localhost value restricts the account to local connections. If an application connects from another server, specify its hostname or IP address instead. Using 'new_user'@'%' matches connections from any host, so prefer a specific host whenever possible.

If you’re running a setup script that may execute more than once, you can use CREATE USER IF NOT EXISTS to avoid an error when the account already exists. This won’t change an existing account’s password or permissions.

This grants the user permission to read, add, and update records in example_db without giving access to other databases. Replace the database name and privileges according to your application’s needs.

If the account requires all privileges within a particular database, use GRANT ALL PRIVILEGES ON example_db.* instead of granting privileges globally with *.*.

Once that is done, your new user account has the same access to the database as the root user.

How to grant specific MySQL privileges

In this part, we will explain how to grant privileges separately for a user account in MySQL.

When specifying the database name and table name, separate them with a . (period) and no spaces. This will give the root user fine-grain control over certain data.

Also, replace the PERMISSION_TYPE value with the kind of access you want to grant to your new user account.

Here are the most used commands in MySQL:

• CREATE — enable users to create a database or table
• SELECT — permit users to retrieve data
• INSERT — let users add new entries in tables
• UPDATE — allow users to modify existing entries in tables
• DELETE — enable users to erase table entries
• DROP — let users delete entire database tables

To use any of these options, simply replace PERMISSION_TYPE with the appropriate keyword. To apply multiple privileges, separate them with a comma. For example, we can assign CREATE and SELECT to our non-root MySQL user account with this command:

GRANT CREATE, SELECT ON * . * TO 'user_name'@'localhost';

Sometimes, you might come across a situation where you need to revoke given privileges from a user. You can do so by entering:

REVOKE PERMISSION_TYPE ON database_name.table_name FROM ‘user_name’@‘localhost’;

For example, to withdraw all privileges for our non-root user we should use:

REVOKE ALL PRIVILEGES ON * . * FROM 'user_name'@'localhost';

Finally, you can entirely delete an existing user account by using the following command:

DROP USER ‘user_name’@‘localhost’;

How to check MySQL user privileges

In order to find what privileges have already been granted to a MySQL user, you can use the SHOW GRANTS command:

SHOW GRANTS FOR 'ner)user'@'localhost';

The output will look similar to this:

+---------------------------------------------------------------------------+
| Grants for user_name@localhost                                             |
+---------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `new_user`@`localhost`|
| GRANT SELECT, INSERT, UPDATE ON `example_db`.* TO `new_user`@`localhost`      |

+---------------------------------------------------------------------------+
3 rows in set (0.00 sec)

SHOW GRANTS displays the permissions assigned to the account. Review the results to confirm that the user has access only to the intended databases and operations.

How to revoke MySQL user privileges

To remove permissions from an existing MySQL user without deleting the account, use the REVOKE command.

For example, to remove permission to add and modify records in a specific database, run:

REVOKE INSERT, UPDATE ON example_db.* FROM 'new_user'@'localhost';

The user will retain any other privileges that haven’t been revoked.

You can then run SHOW GRANTS FOR 'new_user'@'localhost'; to verify the changes.

How to delete a MySQL user

If you no longer need a MySQL account, remove it using DROP USER:

DROP USER 'new_user'@'localhost';

This removes the account and its associated privileges without deleting the database or its tables.

You can also use DROP USER IF EXISTS to avoid an error if the specified account doesn’t exist. Before deleting an account, make sure no applications or database objects still depend on it.

Next steps: Review your MySQL user permissions

After creating a MySQL user and granting the required permissions, run SHOW GRANTS to confirm which databases and operations the account can access.

Review existing accounts as well, especially those used by applications or developers. Remove unnecessary privileges with REVOKE and delete accounts that are no longer needed.

For better database security, give each account only the permissions it needs and restrict connections to trusted hosts whenever possible.

This article is a localized version of How to create a MySQL user and grant privileges, originally published in English.

The author

Domantas G.

Add Hostinger as a preferred source on Google

Domantas leads the content and SEO teams forward with fresh ideas and out of the box approaches. Armed with extensive SEO and marketing knowledge, he aims to spread the word of Hostinger to every corner of the world. During his free time, Domantas likes to hone his web development skills and travel to exotic places.

More from Domantas G.