Blogs

How To Create a New User and Grant Permissions in MySQL

Posted on by admin

Introduction

MySQL is an open source relational database management system. It is commonly implemented as part of the LAMP stack (which stands for Linux, Apache, M, and SQL, and PHP) and, at the time of writing, is the world’s most popular open source database.

This guide describes how to create a new MySQL user and grant it 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 this database is installed on a virtual private server running Ubuntu 20.04, although the principles it describes should be applicable regardless of how you access your database.

If you don’t have access to a MySQL database and want to set one up yourself, you can follow one of our guides on how to install MySQL. Again, regardless of your server’s underlying operating system, the methods for creating a new MySQL user and granting them permissions will generally be the same.

Note that any part of the example commands that you need to change or customize will be highlighted in this way throughout this guide.

Creating a new

user

After installation, MySQL creates a root user account that you can use to manage your database. This user has full privileges over the MySQL server, which means they have complete control over every database, table, user, etc. Because of this, it is best to avoid using this account outside of administrative functions. This step describes 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 configured to authenticate using the auth_socket plugin by default instead of with a password. This plug-in requires that the name of the operating system user invoked by the MySQL client match the name of the MySQL user specified in the command. This means that you must precede the mysql command with sudo to invoke it with Ubuntu root user privileges to gain access to the MySQL root user: sudo mysql Once you have access to the MySQL

prompt, you can create a new user with a CREATE USER statement. These follow this general syntax:

CREATE USER ‘username’@’host’

  1. IDENTIFIED WITH authentication_plugin BY ‘password’;

After CREATE USER, specify a user name. This is immediately followed by an @ sign and then the hostname from which this user will connect. If you only plan to access this user locally from your Ubuntu server, you can specify localhost. It is not always necessary to wrap the user name and host in single quotation marks, but doing so can help avoid errors.

You have several options when it comes to choosing your user’s authentication plugin. The auth_socket above-mentioned plugin can be convenient as it provides great security without requiring valid users to enter a password to access the database. But it also prevents remote connections, which can complicate things when external programs need to interact with MySQL.

Alternatively, you can omit the WITH authentication_plugin part of the syntax entirely so that the user authenticates 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.

Run the following command to create a user who authenticates with caching_sha2_password. Be sure to change sammy to your preferred username and password to a strong

password of your choice: CREATE USER ‘sammy’@’

  1. localhost’ IDENTIFIED BY

‘password’;

After creating your new user, you can grant it the appropriate privileges

.

Granting user permissions

The general syntax

for granting user privileges is as follows:

  1. GRANT PRIVILEGES IN database.table TO ‘username’@’host’;

The PRIVILEGE value in this sample syntax defines what actions the user can perform on the specified database and table. You can grant multiple privileges to the same user in a single command by separating each with a comma. You can also grant privileges to a user globally by entering asterisks (*) instead of the database and table names. In SQL, asterisks are special characters used to represent “all” databases or tables.

To illustrate, the following command grants a 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 gives the user the ability to query data with SELECT, create foreign keys with the REFERENCES keyword, and perform FLUSH operations with the RELOAD privilege. However, you should only grant users the permissions they need, so feel free to adjust your own user’s privileges as needed.

You can find the full list of available privileges in the official MySQL documentation.

Execute this GRANT statement, replacing sammy

with your own MySQL username, to grant these privileges to your user:

GRANT

  1. CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO ‘sammy’@’localhost’

WITH GRANT OPTION;

Please note that this statement also includes WITH GRANT OPTION. This will allow your MySQL user to grant any permissions you have to other users on the system.

Many guides suggest running the FLUSH PRIVILEGES

command immediately after a CREATE USER or GRANT statement to reload the grant tables and ensure that the new privileges take effect:

  1. FLUSH PRIVILEGES;

However, according to the official MySQL documentation, when you modify the grant tables indirectly with an account management statement such as GRANT, the database will immediately load the lease tables back into memory, which means that the FLUSH PRIVILEGES command is not necessary in our case. On the other hand, running it will not have any negative effect on the system.

If you need to revoke a permission, the

structure is almost identical to granting it

:

  1. REVOKE type_of_permission ON database_name.table_name FROM ‘username’@’host’;

Note that when

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

You can review a user’s current permissions by running the command SHOW GRANTS:

  1. SHOW ALLOWANCES FOR ‘username

‘@’host’;

Just as you can delete databases with

DROP, you can use DROP to delete a user: DROP USER ‘username’@’

  1. localhost’;

After creating your MySQL user

and granting it privileges, you can exit

the MySQL client:

  1. Exit

In the future, to

log in as your new MySQL user, you would use a command like the following:

  1. mysql -u sammy-p

The -p flag will cause the MySQL client to prompt you for your MySQL user’s password to authenticate

.

Conclusion By

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

To learn more about the basics of MySQL, you can refer to the following tutorials:

How to create and manage databases in MySQL

  • and MariaDB on a cloud server

    • How to set up replication in MySQL

  • How to Set Up
  • MySQL Group Replication in Ubuntu 20.04
admin