Read in 4 minutes

last updated 

How to Create MySQL Users Accounts and Grant Privileges

MySQL is the most popular open-source relational database management system. MySQL server allows us to create numerous user accounts and grant appropriate privileges, so that users can access and access and manage databases.

This tutorial describes how to create MySQL user accounts and grant privileges.

Before you begin

Before you start with this tutorial, we are assuming that you already have MySQL or MariaDB server installed on your system. If not, you can easily install it by following one of the tutorials below:

All commands will be executed inside the MySQL shell as an administrative user (the minimum privileges required to create user accounts and define their privileges is CREATE USER and GRANT) or with a root account.

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

mysql -u root -p

If you haven’t set a password for your MySQL root user you can omit the -p switch.

Create a new MySQL User Account

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

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

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

Replace the placeholder value newuser with your intended new user name, and placeholder value user_password with the user password.

In the command above the host name 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 host name part (localhost) 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';

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 – user account is allowed to create databases and tables.
  • DROP - user account is allowed to drop databases and tables.
  • DELETE - user account is allowed to delete rows from specific table.
  • INSERT - user account is allowed to insert rows into specific table.
  • SELECT – user account is allowed to read a database.
  • UPDATE - user account is allowed to update table rows.

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

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

Here are some examples:

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

    GRANT ALL PRIVILEGES ON database_name.* TO 'database_user'@'localhost';
  • Grand all privileges to a user account over all databases:

    GRANT ALL PRIVILEGES ON *.* TO 'database_user'@'localhost';
  • Grand all privileges to a user account over a specific table from a database:

    GRANT ALL PRIVILEGES ON database_name.table_name TO 'database_user'@'localhost';
  • Grant multiple privileges to a user account over a specific database:

    GRANT SELECT, INSERT, DELETE ON database_name.* TO [email protected]'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';
+---------------------------------------------------------------------------+
| Grants for [email protected]                                       |
+---------------------------------------------------------------------------+
| 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.

For example to revoke all privileges from a user account over a specific database, use the following command:

REVOKE ALL PRIVILEGES ON database_name.* TO '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.

Conclusion

This tutorial covers only the basics, but it should be a good starting for anyone who wants to learn how to create new MySQL user accounts and grant privileges.

You can also check the following MySQL related tutorials:

If you have any question or feedback feel free to leave a comment.