How To Create a New User and Grant Permissions in MySQL

220 views 0 September 30, 2022 host_know_user

Introduction

MySQL is a relational database management system that is free and open source. It is commonly used as part of the LAMP stack (which stands for Linux, Apache, MySQL, and PHP) and is the most popular open-source database in the world as of this writing.

This document describes how to create a new MySQL user and grant them the necessary permissions to perform a variety of tasks.

Prerequisites

You will need access to a MySQL database to follow along with this guide. This guide assumes that your database is installed on a virtual private server running Ubuntu 20.04, but the principles outlined should apply regardless of how you access your database.

Please keep in mind that any parts of the example commands that need to be changed or customised will be highlighted in this manner throughout this guide.

Creating a New User

MySQL creates a root user account during installation that you can use to manage your database. This user has full control over the MySQL server, which means it has access to every database, table, user, and so on. As a result, it’s best to avoid using this account for anything other than administrative purposes. This section describes how to create a new user account and grant it privileges using the root MySQL user

In Ubuntu systems running MySQL 5.7 (and later versions), the root MySQL user is configured by default to authenticate using the auth_socket plugin rather than a password. The name of the operating system user who invokes the MySQL client must match the name of the MySQL user specified in the plugin:

$ sudo mysql

Note: If your root MySQL user is set to require password authentication, you must use a different command to access the MySQL shell. The following commands will run your MySQL client as a regular user, and you will only gain administrator privileges within the database by authenticating with the correct password:

$ mysql -u root -p

Once you have access to the MySQL prompt, you can use the CREATE USER statement to create a new user. This is the general syntax:

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

You specify a username after clicking CREATE USER. This is followed by a @ symbol and the hostname from which this user will connect. You can specify localhost if you only intend to access this user from your Ubuntu server. It isn’t always necessary to surround both the username and the host in single quotes, but it can help to prevent errors.

When it comes to selecting an authentication plugin for your user, you have several options. The previously mentioned auth socket plugin can be useful because it provides strong security without requiring valid users to enter a password to access the database. However, it also prevents remote connections, which can make things more difficult when external programmes need to interact with MySQL.

You can also omit the WITH authentication plugin section entirely to have the user authenticate with MySQL’s default plugin, caching sha2 password. Because of its strong security features, the MySQL documentation recommends this plugin for users who want to log in with a password.

You can also omit the WITH authentication plugin section entirely to have the user authenticate with MySQL’s default plugin, caching sha2 password. Because of its strong security features, the MySQL documentation recommends this plugin for users who want to log in with a password.

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

Note: Some versions of PHP have a known bug that causes issues with caching sha2 password. If you intend to use this database with a PHP application, such as phpMyAdmin, you should create a user who will authenticate with the older, but still secure, mysql native password plugin instead:

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

If you’re not sure, you can always create a user who authenticates with caching sha2 plugin and then ALTER it with the following command:

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

After you’ve created your new user, you can grant them the necessary permissions.

Granting a User Permissions

The following is the general syntax for granting user privileges:

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

In this example syntax, the PRIVILEGE value specifies which actions the user is permitted to perform on the specified database and table. By separating each privilege with a comma, you can grant multiple privileges to the same user in a single command. You can also grant a user global privileges by replacing the database and table names with asterisks (*). Asterisks are special characters in SQL that are 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 ability to INSERT, UPDATE, and DELETE data from any table on the server. It also allows the user 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 require, so feel free to change your own user’s privileges as needed.

To grant these privileges to your user, execute the following GRANT statement, replacing sammy with the name of your own MySQL user:

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

Take note of the WITH GRANT OPTION, which allows your MySQL user to grant any permissions it has to other users on the system.

Note: Some users may wish to grant their MySQL user the ALL PRIVILEGES privilege, which will grant them broad superuser privileges similar to those of the root user, as shown below:

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

Such broad privileges should not be granted lightly, as anyone who has access to this MySQL user has complete control over all databases on the server.

Many guides recommend running the FLUSH PRIVILEGES command immediately following a CREATE USER or GRANT statement to reload the grant tables and ensure that the new privileges are applied:

mysql> 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 reload the grant tables into memory, implying that the FLUSH PRIVILEGES command isn’t required in our case. Running it, on the other hand, will have no negative impact on the system.

The structure for revoking a permission is nearly identical to that for granting it:

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

It’s worth noting that when revoking permissions, the syntax requires you to use FROM rather than TO, as you did when granting them.

The SHOW GRANTS command can be used to view a user’s current permissions:

mysql> SHOW GRANTS FOR 'username'@'host';

You can use DROP to delete a user in the same way that you can delete a database:

mysql> DROP USER 'username'@'localhost';

You can exit the MySQL client after creating your MySQL user and granting them privileges:

mysql> exit

To log in as your new MySQL user in the future, use the following command:

$ mysql -u sammy -p

When you use the -p flag, the MySQL client will prompt you for your MySQL user’s password in order to authenticate.

Conclusion

You’ve learned how to add new users and grant them various permissions in a MySQL database by following this tutorial. You could explore and experiment with different permissions settings for your MySQL user from here, or you could learn more about some higher-level MySQL configurations.

Was this helpful?

 
Related Articles

Didn't find your answer? Contact Us