How to Create MySQL Users Accounts and Grant Privileges

Introduction

MySQL is the most widely used open-source relational database management system. In order to give users access to control databases, MySQL server enables us to create numerous user accounts and grant appropriate privileges.

Prerequisites

We presume that your system already has a MySQL or MariaDB server installed.

All commands are run as root or an administrative user inside the MySQL shell. CREATE USER and GRANT are the two basic minimum privileges needed to create user accounts and determine their privileges.

When prompted, type the following command to access the MySQL shell and provide your MySQL root user password:

mysql -u root -p

Enter the following command to log in as root if your MySQL version is 5.7 or above and uses the auth_socket plugin:

sudo mysql

Create a new MySQL User Account

In MySQL, a user account consists of two components: username and host name.

Run this command to create a new MySQL user account:

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

The user will only be able to connect to the MySQL server from the localhost (i.e. from the system where MySQL Server runs) in the example above because the hostname part is set to localhost.

Change the hostname part to the IP of the remote machine to allow access from another host. As an example, to permit access from a machine with the IP 10.8.0.5, you would execute:

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

Use the wildcard '%' as a host part to create a user that can connect from any host:

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

Grant Privileges to a MySQL User Account

A user account may be given several kinds of privileges. A complete list of privileges that MySQL supports is available here.

The privileges that are most frequently used are:

• ALL PRIVILEGES: Gives all privileges to a user account.
• CREATE: The user account is permitted to create databases and tables.
• DROP: Drop databases and tables.
• DELETE: Delete rows from a specific table.
• INSERT: Insert rows into a specific table.
• SELECT: Permitted to read a database.
• UPDATE: The user account is permitted to update table rows.

Use the following syntax to give a user account specific privileges:

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

Here are a few examples:

• Grant a user account all privileges over a given database:

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

• Grant a user account all privileges over each and every database:

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

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

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

• Grant a user account multiple privileges over a given database:

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

Display MySQL User Account Privileges

Use the SHOW GRANTS statement to determine the privilege(s) given to a specific MySQL user account:

SHOW GRANTS FOR 'database_user'@'localhost';

The output will look like this:

Output

+---------------------------------------------------------------------------+
| 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 nearly identical to that used to grant privileges.

Run the following command to revoke a user account's access to all privileges over a specific database:

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

Remove an Existing MySQL User Account

Use the DROP USER statement to delete a MySQL user account:

DROP USER 'user'@'localhost'

The user account and its privileges will be deleted by the above command.

Conclusion

This tutorial should be a useful starting point for anyone interested in learning how to create new MySQL user accounts and grant privileges.

If you have any queries, feel free to leave a comment below, and we'll be happy to help.