How to Create MySQL User Accounts and Grant Privileges

MySQL is the most popular open-source relational database management system. It lets you create separate user accounts and grant each account only the privileges it needs.

This guide shows how to create MySQL user accounts, grant and revoke privileges, list existing grants, and remove users.

Before you Begin #

We are assuming that MySQL or MariaDB is already installed on your system.

All commands are executed inside the MySQL shell as root or an administrative user. The minimum privileges required to create user accounts and manage their privileges are CREATE USER and GRANT.

To access the MySQL shell type the following command and enter your MySQL root user password when prompted:

mysql -u root -p

On Debian and Ubuntu, the root MySQL user authenticates through the auth_socket (MySQL) or unix_socket (MariaDB) plugin by default. In that case, connect as the system root:

sudo mysql

Create a new MySQL User Account #

A user account in MySQL consists of two parts: user name and host name.

To create a new MySQL user account, run the following command:

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'user_password';

In the example above, the hostname part is set to localhost, which means that the user will be able to connect to the MySQL server only from the localhost (i.e. from the system where MySQL Server runs).

To grant access from another host, change the hostname part with the remote machine IP. For example, to grant access from a machine with IP 10.8.0.5 you would run:

CREATE USER 'newuser'@'10.8.0.5' IDENTIFIED BY 'user_password';

To create a user that can connect from any host, use the '%' wildcard as a host part:

CREATE USER 'newuser'@'%' IDENTIFIED BY 'user_password';

MySQL 8 uses the caching_sha2_password authentication plugin by default. If you are connecting from an older client that does not support it, update the client when possible. Some older setups use mysql_native_password, but that plugin is deprecated and may be disabled by default on newer MySQL releases.

Grant Privileges to a MySQL User Account #

There are multiple types of privileges that can be granted to a user account. You can find a full list of privileges supported by MySQL here .

The most commonly used privileges are:

• ALL PRIVILEGES - Grants all privileges to a user account.
• CREATE - The user account is allowed to create databases and tables.
• DROP - The user account is allowed to drop databases and tables.
• DELETE - The user account is allowed to delete rows from a specific table.
• INSERT - The user account is allowed to insert rows into a specific table.
• SELECT - The user account is allowed to read a database.
• UPDATE - The user account is allowed to update table rows.

To grant specific privileges to a user account, use the following syntax:

GRANT permission1, permission2 ON database_name.table_name TO 'database_user'@'localhost';

Here are some examples:

• Grant all privileges to a user account over a specific database:

  Terminal

  GRANT ALL PRIVILEGES ON database_name.* TO 'database_user'@'localhost';

• Grant all privileges to a user account on all databases:

  Terminal

  GRANT ALL PRIVILEGES ON *.* TO 'database_user'@'localhost';

• Grant all privileges to a user account over a specific table from a database:

  Terminal

  GRANT ALL PRIVILEGES ON database_name.table_name TO 'database_user'@'localhost';

• Grant multiple privileges to a user account over a specific database:

  Terminal

  GRANT SELECT, INSERT, DELETE ON database_name.* TO database_user@'localhost';

Display MySQL User Account Privileges #

To find the privilege(s) granted to a specific MySQL user account, use the SHOW GRANTS statement:

SHOW GRANTS FOR 'database_user'@'localhost';

The output will look something like below:

+---------------------------------------------------------------------------+
| Grants for database_user@localhost                                       |
+---------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'database_user'@'localhost'                        |
| GRANT ALL PRIVILEGES ON `database_name`.* TO 'database_user'@'localhost' |
+---------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Revoke Privileges from a MySQL User Account #

The syntax to revoke one or more privileges from a user account is almost identical as when granting privileges.

To revoke all privileges from a user account over a specific database, run the following command:

REVOKE ALL PRIVILEGES ON database_name.* FROM 'database_user'@'localhost';

Remove an Existing MySQL User Account #

To delete a MySQL user account use the DROP USER statement:

DROP USER 'user'@'localhost';

The command above will remove the user account and its privileges.

Quick Reference #

For a printable quick reference, see the MySQL and MariaDB cheatsheet .

Troubleshooting #

ERROR 1396 (HY000): Operation CREATE USER failed for ‘user’@‘host’
The account already exists, or a previous user with the same name and host was not fully removed. Drop it first with DROP USER, or use CREATE USER IF NOT EXISTS.

ERROR 1410 (42000): You are not allowed to create a user with GRANT
MySQL 8.0 no longer creates users implicitly through GRANT. Run CREATE USER first, then GRANT.

ERROR 1045 (28000): Access denied for user ‘user’@‘host’ (using password: YES)
Check the host part of the account. 'user'@'localhost' is a different account from 'user'@'%'. List accounts with SELECT User, Host FROM mysql.user; to confirm the right host.

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
The server rejected the password because it does not meet the active password policy. Use a stronger password first. If you manage the server yourself, review the password-validation settings for your MySQL or MariaDB version before relaxing the policy.

Conclusion #

Creating dedicated MySQL accounts with the least required privileges is the safest way to connect applications to a database. For broader user and database management, see our guide on managing MySQL databases and users from the command line .

About the authors

Dejan Panovski

Dejan Panovski is the founder of Linuxize, an RHCSA-certified Linux system administrator and DevOps engineer based in Skopje, Macedonia. Author of 800+ Linux tutorials with 20+ years of experience turning complex Linux tasks into clear, reliable guides.

View author page