MySQL, one of the most popular relational databases. This allows for the creation of multiple users with varying levels of access and permissions. Managing user accounts and permissions is crucial for maintaining database security and ensuring that authorized users have the appropriate level of access to perform their tasks. If you are new to MySQL or want to learn how to create a new user and grant permissions, you’ve come to the right place. In this article, we will provide you with a step-by-step guide on how to create a new user in MySQL. Moreover we will cover how to set up their permissions, and manage their access to the database. Whether you are a developer, database administrator, or simply interested in learning MySQL, this article will walk you through the process of creating and managing users in MySQL for improved database security and access control.
If you want to learn how to back up a MySQL database to an Object Storage (e.g. Contabos S3-compatible Object Storage) check out this article.
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'; Exit MySQL
exit;