How to Create a New User and Grant Permissions in MySQL

Introduction

Before we begin talking about how to create a new user and grant permissions in MySQL, let's briefly understand – What is MySQL?

MySQL is an open-source and free relational database management system. It allows users to store, retrieve, and manage vast amounts of data efficiently. MySQL offers flexible features, scalability, and high performance, making it suitable for small websites or large enterprise applications.

MySQL caters to both small websites and large enterprise applications. It seamlessly integrates with different platforms and programming languages. Being reliable and cost-effective, MySQL is the preferred choice for developers and businesses seeking optimized data management solutions. Harness the power of MySQL for efficient data handling and storage.

In this tutorial, you will create a new user and grant permissions in MySQL. We will also address a few FAQs on how to create a new user and grant permissions in MySQL.

Prerequisites

You will require access to a MySQL database in order to follow along with this course. Although the guidelines in this tutorial assume that your database is deployed on a virtual private server running Ubuntu 20.04, they should still hold true regardless of how you access your database.

Again, the procedures for creating a new MySQL user and granting them rights will typically be the same, independent of the server's underlying operating system.

Please take note that throughout this guide, any elements of example commands that you need to modify or customize will be noted in this way.

Creating a New User

During installation, MySQL creates a root user account that you may use to maintain your database. With full privileges, this user can access all databases, tables, users, and other resources on the MySQL server. Therefore, it's recommended to refrain from using this account outside administrative functions. This step describes how to establish a new user account and provide it access using the root MySQL user.

The root MySQL user is configured by default to authenticate via the auth_socket plugin, rather than using a password in Ubuntu systems running MySQL 5.7 (and later versions). The name of the operating system user who launches the MySQL client must coincide with the name of the MySQL user supplied in the command for this plugin to function. This means that in order to run the mysql command with the privileges of the root Ubuntu user and access the root MySQL user, sudo must come before the command:

sudo mysql

Note: You will need to use a different command to enter the MySQL shell if your root MySQL user is set up to require a password for authentication. Your MySQL client will run with regular user permissions if you do the following, and you will only be granted administrator permissions within the database upon successful password authentication:

mysql -u root -p

You can use a CREATE USER statement to create a new user after you have access to the MySQL prompt. These use the general syntax as follows:

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

You enter a username after CREATE USER. The hostname from which this user will connect immediately follows this with a @ symbol. You can specify localhost if you only intend to use your Ubuntu server to access this user locally. Although it's not always essential, including the username and host in single quotes can aid in error prevention.

When it comes to selecting your user's authentication plugin, you have multiple choices. The previously stated auth_socket plugin might be useful because it offers high security without needing legitimate users to input a password in order to access the database. However, it also blocks distant connections, complicating matters when additional programmes are required to communicate with MySQL.

If you prefer to have the user authenticate using MySQL's caching_sha2_password plugin, you can completely omit the WITH authentication_plugin clause from the syntax. Due to its potent security features, the MySQL documentation suggests this plugin for users who want to log in using a password.

Run the following command to create a user that uses caching_sha2_password for authentication. Make sure to replace sammy with your preferred username and your preferred secure password:

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

There are a few versions of PHP that have a known bug that interferes with caching_sha2_password. Instead, you might wish to create a user that will authenticate with the older, but still safe, mysql_native_password plugin if you intend to utilize this database with a PHP application, such as phpMyAdmin:

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

If you're uncertain, you can always make a user who authenticates using the caching_sha2_plugin and later ALTER it using the following command:

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

You can give your new user the necessary permissions after creating them.

Granting User Permissions

Given below is the general syntax for granting user privileges:

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

The actions that a user is permitted to take on the specified database and table are specified by the PRIVILEGE value in this example syntax. By separating each permission with a comma, you can grant the same user several privileges in a single command. Asterisks (*) can be used instead of the database and table names to provide a user worldwide privileges. Asterisks, which are special characters, are used in SQL to denote "all" databases or tables.

To give an example, the user can CREATE, ALTER, and DROP databases, tables, and users as well as INSERT, UPDATE, and DELETE data from any table on the server by using the following command. Additionally, it gives the user access to SELECT data querying, the REFERENCES keyword creating foreign keys, and the RELOAD privilege allowing FLUSH operations. You should, however, only provide users the access they actually need, so feel free to change your own user's permissions as appropriate.

The official MySQL documentation has a complete list of the possible privileges.

To give your user access to these privileges, execute the following GRANT statement, substituting sammy with the name of your own MySQL user:

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

Also take note that this statement includes WITH GRANT OPTION. By doing this, your MySQL user will be able to grant other system users any permissions it currently has.

Warning: Giving their MySQL user the ALL PRIVILEGES privilege will grant them with broad superuser privileges similar to the root user’s privileges, like so:

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

Given that anyone with access to this MySQL user will have full control over every database on the system, such broad privileges should not be offered carelessly.

In order to ensure that the new privileges are applied, many guidelines advise running the FLUSH PRIVILEGES command right after following a CREATE USER or GRANT statement to reload the grant tables:

FLUSH PRIVILEGES;

The FLUSH PRIVILEGES command is redundant in our case since, in accordance with the official MySQL documentation, the database will immediately reload the grant tables into memory when you indirectly edit the grant tables with the help of an account management statement like GRANT. On the other side, running it won't harm the system in any way.

To revoke a mission, you need to follow the same structure as granting it:

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

Please take note that the terminology for withdrawing rights calls for you to utilize FROM rather than TO as you did when providing the permissions.

The SHOW GRANTS command can be used to check a user's current permissions:

SHOW GRANTS FOR 'username'@'host';

DROP can be used to remove users, same as it can remove databases:

DROP USER 'username'@'localhost';

You can close the MySQL client after creating your MySQL user and giving them access:

exit

In the future, you would use a command similar to the one below to log in as your new MySQL user:

mysql -u sammy -p

The MySQL client will ask you for your password for your MySQL user when you use the -p flag to authenticate.

FAQs to Create a New User and Grant Permissions in MySQL

Conclusion

You now know how to add new users and grant them various permissions in a MySQL database by following this tutorial. From here, you may either learn more about some more advanced MySQL configurations or keep exploring and experimenting with different permission settings for your MySQL user.

Please leave your questions in the comments section below.