How To Create a New User and Grant Permissions in MySQL

If you want to learn how to back up a MySQL database to an Object Storage (e.g. Zumiv S3-compatible Object Storage)

Prerequisites/Requirements

To make use of this tutorial, you will require access to a MySQL database. The tutorial assumes that the database is installed on a virtual private server (VPS) running Ubuntu or Debian. However the process for creating a new MySQL user and assigning permissions is generally similar, regardless of the underlying operating system of your server.

Creating a MySQL User

After MySQL installation, a root user account is automatically created, granting full privileges over the MySQL server, including control over all databases, tables, and users. However, it is recommended to restrict the use of the root account to administrative tasks only. This section will detail how to utilize the root MySQL user to create a new user account and assign appropriate privileges.

Use the following command to login to your MySQL Server as root:

mysql –u root –p 

You will be prompted to enter your root-password.

Now that you have access to the MySQL prompt, you can create a new user with the “CREATE USER”-command. It follows the following general syntax:

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

Following the “CREATE USER” command, you need to provide a username, followed by the “@” sign and the hostname from which the user will connect. If you only intend to allow local access from your local server, you can specify “localhost” as the hostname. While enclosing the username and host in single quotes may not always be mandatory, it can be beneficial in preventing potential errors.

Granting Permissions to a User

The purpose of granting privileges to a user in MySQL is to define the actions and operations that the user is allowed to perform on a database or specific database objects, such as tables, views, procedures, and functions. Privileges determine the level of access and control that a user has over the database and its objects.

By granting privileges, you can control what actions a user can perform. This includes things like creating, modifying, or deleting data, as well as managing database structures and configurations. Privileges allow you to set fine-grained access control. This ensures that users have the necessary permissions to perform their intended tasks while restricting them from unauthorized actions. With that you eliminate the risk that a user could potentially compromise the security or integrity of the database.

General Syntax

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

Granting Privileges

The PRIVILEGE value in the provided syntax determines the actions that a user is authorized to perform on the specified database and table. Multiple privileges can be granted to the same user in a single command, separated by commas. Additionally, global privileges can be granted by using asterisks (*) in place of specific database and table names, as asterisks represent “all” databases or tables in SQL.

For example, the following command grants a user the following global privileges, actions on databases, tables, and users:

CREATE

ALTER

DROP

It also allows the user to perform INSERT, UPDATE, and DELETE operations on any table, query data with SELECT, create foreign keys with REFERENCES, and perform FLUSH operations with RELOAD privilege.

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

However, it is important to only grant users the permissions they actually require, and adjust user privileges accordingly.

The complete list of available privileges can be found in the official MySQL documentation.

Some users require all permissions (e.g. if you are planning to create a user to install WordPress or Nextcloud). To do this, replace the names of the individual permissions with the word “all”. The command will then look like this:

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

Refresh the Database

To refresh the user’s permissions, use this command:

FLUSH PRIVILEGES; 

Revoke Permissions from a User

If you need to revoke a permission, the structure is almost identical to granting it:

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

Note that when revoking permissions, the syntax requires that you use FROM, instead of TO. TO would be used when granting the permissions.

Other useful MySQL Commands

Display permissions of a user

SHOW GRANTS FOR 'username'@'host'; 

Delete a user

DROP USER 'username'@'localhost';