How to create a new user and grant permissions in MySQL

Introduction

MySQL is an open source relational database management system. It is commonly used as part of the LAMP stack (which stands for Linux, Apache, MySQL, and PHP) and is the most popular open source database in the world as of this writing. This guide will explain how to create a new MySQL user and grant them the necessary permissions to perform a variety of actions.

Prerequisites

To follow this guide, you will need access to a MySQL database. This guide assumes that the database is installed on a virtual private server running Ubuntu 20.04, although the principles explained in it should apply regardless of how you access your database. If you don't have access to a MySQL database and want to set it up yourself, you can follow one of our guides on how to install MySQL. Again, regardless of your server's underlying operating system, the procedures for creating a new MySQL user and granting them permissions will usually be the same.

Create a new user

After installation, MySQL creates a root user account that you can use to manage your database. This user has full privileges on the MySQL server, meaning it has complete control over every database, table, user, etc. For this reason, it is best to avoid using this account outside of administrative functions. This step explains how to use the MySQL root user to create a new user account and grant it privileges.

On Ubuntu systems running MySQL 5.7 (and later), the MySQL root user is authenticated by default using the auth_socket plugin, rather than a password. This plugin requires that the operating system user name invoking the MySQL client match the MySQL user name specified in the command. This means that you must precede the mysql command with sudo to invoke it with the privileges of the Ubuntu root user to gain access to the MySQL root user:

sudo mysql

If your MySQL root user is configured for password authentication, you will need to use a different command to access the MySQL shell. The following will run your MySQL client with regular user privileges, and you will gain administrator privileges on the database simply by authenticating with the correct password:

mysql -u root -p

Once you have access to the MySQL prompt, you can create a new user with the phrase CREATE USER Create. These follow this general syntax:

CREATE USER 'username'@'host' IDENTIFIED WITH authentication_plugin BY 'password';

After CREATE USER You specify a username. This is immediately followed by an @ sign and then the hostname from which this user will connect. If you only intend to access this user locally from your Ubuntu server, you can specify localhost. It is not always necessary to enclose the username and hostname in quotes, but doing so can help prevent errors.

When choosing a user authentication plugin, you have several options. Plugin auth_socket As mentioned earlier, this can be convenient because it provides strong security without requiring authenticated users to enter a password to access the database. But it also prevents remote connections, which can complicate things when external applications need to interact with MySQL.

As an alternative, you can leave out the WITH authentication_plugin part of the syntax entirely to have the user authenticate with the default MySQL plugin, caching_sha2_password. The MySQL documentation recommends this plugin for users who want to log in with a password because of its strong security features.

To create a user that authenticates with caching_sha2_password, run the following command. Make sure that Sammy Choose a strong password for your preferred username and password:

CREATE USER 'sammy'@'localhost' IDENTIFIED BY 'password';

There is a known issue in some versions of PHP that causes caching_sha2_password to fail. If you plan to use this database with a PHP application – for example phpMyAdmin – you may want to create a user that authenticates with the older, though still secure, mysql_native_password extension:

CREATE USER 'sammy'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

If you're not sure, you can always create a user that authenticates with caching_sha2_plugin and then change it with this command:

ALTER USER 'sammy'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

After creating your new user, you can give them the appropriate privileges.

Granting user permissions

The general command for granting user privileges is as follows:

GRANT PRIVILEGE ON database.table TO 'username'@'host';

The PRIVILEGE value in this example syntax specifies what actions the user is allowed to perform on the specified database and table. You can grant multiple privileges to a user in a single statement by separating each with a comma. You can also grant global privileges to a user by entering an asterisk (*) in place of the database and table name. In SQL, asterisks are special characters used to represent “all” databases or tables.

To illustrate, the following command gives the user global privileges to create, alter, and drop databases, tables, and users, as well as the power to insert, update, and delete data from any table on the server. It also allows the user to query the data with SELECT Search, foreign keys with keyword REFERENCES Create and operate FLUSH with points RELOAD However, you should only give users the permissions they need, so you can adjust your user privileges as needed.

You can see the full list of available points at Official MySQL documentation Find it.

Run this GRANT statement, replacing Sammy with your MySQL username, to grant these privileges to your user:

GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'sammy'@'localhost' WITH GRANT OPTION;

Note that this statement also includes the WITH GRANT OPTION. This allows your MySQL user to grant any permissions they have to other users on the system.

Some users may want to grant their MySQL user ALL PRIVILEGES, which gives them broad superuser privileges similar to root user privileges, such as:

GRANT ALL PRIVILEGES ON *.* TO 'sammy'@'localhost' WITH GRANT OPTION;

Such broad privileges should not be granted lightly, as anyone with access to this MySQL user will have complete control over every database on the server.

Many guides suggest running the FLUSH PRIVILEGES command immediately after a CREATE USER or GRANT statement to reload the grant tables to ensure that the new privileges are applied:

FLUSH PRIVILEGES;

However, according to Official MySQL documentationWhen you modify the grant tables indirectly with an account management statement such as GRANT, the database loads the grant tables into memory immediately, meaning that the FLUSH PRIVILEGES command is not necessary in our case. On the other hand, executing it will not have any negative impact on the system.

If you need to revoke a license, the structure is almost the same as granting it:

REVOKE type_of_permission ON database_name.table_name FROM 'username'@'host';

Note that when revoking permissions, the syntax requires that instead of TOthat you used when granting permissions, FROMUse.

By executing the command SHOW GRANTS You can check the current permissions of the user:

SHOW GRANTS FOR 'username'@'host';

As you can use databases with DROP Delete, you can from DROP To delete a user, use:

DROP USER 'username'@'localhost';

After creating the MySQL user and giving them privileges, you can exit MySQL:

exit

In the future, to log in as the new MySQL user, you will use a command like the following:

mysql -u sammy -p

-p causes the MySQL client to prompt you for the MySQL user password for authentication.

Result

By following this tutorial, you learned how to add new users and grant them various permissions in a MySQL database. From here, you can continue to explore and experiment with different permission settings for your MySQL user, or you may want to learn more about some of the higher-level MySQL settings.