Creating a New User and Giving Permissions in MySQL6 min read

Introduction

MySQL is a free and widely-used relational database management system. It’s commonly a part of the LAMP stack, standing for Linux, Apache, MySQL, and PHP. Currently, it holds the title of being the most popular open-source database globally.

This guide is designed to walk you through the process of creating a new MySQL user and providing them with the necessary permissions to execute various actions.

Prerequisites

To follow this guide, you must have access to a MySQL database. The guide assumes your database is installed on a virtual private server running Ubuntu 20.04. However, the principles outlined here are applicable, regardless of how you access your database.

If you don’t currently have access to a MySQL database and want to set one up, you can refer to our guides on How To Install MySQL. Keep in mind that regardless of your server’s underlying operating system, the methods for creating a new MySQL user and granting them permissions will generally remain the same.

Throughout this guide, any parts of example commands that require modification or customization will be highlighted like this.

Creating a New User

Upon installing MySQL, a root user account is automatically created. This account grants full privileges over the MySQL server, giving complete control over databases, tables, and users. However, it’s advisable to reserve this account for administrative tasks only. This step-by-step guide explains how to utilize the root MySQL user to create a new user account and assign it specific privileges.

Authentication Considerations in Ubuntu Systems

For Ubuntu systems running MySQL 5.7 and later versions, the root MySQL user defaults to authenticating using the auth_socket plugin, rather than a password. This plugin requires the operating system user invoking the MySQL client to match the MySQL user’s name specified in the command. Therefore, you need to precede the mysql command with sudo to invoke it with the root Ubuntu user’s privileges and gain access to the root MySQL user:

Note: If your root MySQL user uses password authentication, a different command is necessary to access the MySQL shell. The following command runs your MySQL client with regular user privileges, and you’ll gain administrator privileges within the database by authenticating with the correct password:

Creating a New User

Once you have access to the MySQL prompt, you can create a new user using the following syntax:

Replace ‘username,’ ‘host,’ ‘authentication_plugin,’ and ‘password’ with your desired values.

After executing the CREATE USER command, you need to specify a username, followed by an @ sign and the hostname from which the user will connect. If you intend to access this user only locally from your Ubuntu server, you can specify ‘localhost’. While enclosing both the username and host in single quotes isn’t always mandatory, it can help prevent errors.

When choosing the user’s authentication plugin, several options are available. The auth_socket plugin, mentioned earlier, can be convenient as it provides robust security without requiring users to enter a password to access the database. However, it restricts remote connections, which may complicate interactions with MySQL for external programs.

Alternatively, you can omit the ‘WITH authentication_plugin’ part of the syntax to have the user authenticate with MySQL’s default plugin, caching_sha2_password. The MySQL documentation suggests using this plugin for users who prefer logging in with a password because of its robust security features.

Run the following command to create a user authenticating with caching_sha2_password. Remember to change ‘sammy’ to your preferred username and ‘password’ to a strong, secure password:

Note: Some versions of PHP have known issues with caching_sha2_password. If you plan to use this database with a PHP application, such as phpMyAdmin, consider creating a user that authenticates with the older but still secure mysql_native_password plugin:

If you’re uncertain, you can initially create a user authenticating with caching_sha2_password and then later modify it with this command:

After creating the new user, proceed to grant them the necessary privileges.

Granting User Permissions

Granting privileges to a user involves using the following syntax:

In this example, the PRIVILEGE value specifies the actions the user can perform on the specified database and table. Multiple privileges can be granted to the same user in a single command by separating each with a comma. You can also provide a user with global privileges by using asterisks (*) instead of specific database and table names. In SQL, asterisks are special characters representing “all” databases or tables.

For instance, the following command grants a user global privileges to CREATE, ALTER, and DROP databases, tables, and users. It also allows the user to INSERT, UPDATE, and DELETE data from any table, query data with SELECT, create foreign keys using the REFERENCES keyword, and perform FLUSH operations with the RELOAD privilege. However, it’s essential to grant users only the permissions they truly need, so adjust your user’s privileges accordingly.

Refer to the official MySQL documentation for a complete list of available privileges.

Execute the following GRANT statement, replacing ‘sammy’ with your MySQL user’s name, to assign these privileges:

Note: This statement includes WITH GRANT OPTION, allowing your MySQL user to grant the same permissions to other users on the system.

Warning: Some users might be tempted to grant their MySQL user the ALL PRIVILEGES privilege, providing extensive superuser privileges similar to the root user’s. For example:

Exercise caution when using such broad privileges, as anyone with access to this MySQL user will gain complete control over every database on the server.

Many guides recommend running the FLUSH PRIVILEGES command immediately after a CREATE USER or GRANT statement to reload the grant tables and ensure the new privileges take effect:

Feel free to let me know if you have any further requests or if there’s more content you’d like assistance with!

Managing Permissions and User Accounts

According to the official MySQL documentation, modifying the grant tables indirectly through an account management statement like GRANT will automatically reload the grant tables into memory. This makes the FLUSH PRIVILEGES command unnecessary in this context. However, running it won’t have any adverse effects on the system.

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

Note that when revoking permissions, the syntax requires using FROM instead of TO, which is used when granting permissions.

To review a user’s current permissions, use the SHOW GRANTS command:

Just as you can delete databases with DROP, you can also use DROP to delete a user:

After creating your MySQL user and granting them privileges, you can exit the MySQL client:

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

The -p flag prompts the MySQL client to ask for your MySQL user’s password for authentication.

Conclusion

This tutorial has equipped you with the knowledge to add new users and grant them various permissions in a MySQL database. You can further explore and experiment with different permission settings for your MySQL user or delve into more advanced MySQL configurations.