Introduction
MySQL is an open-source management solution for relational databases. It is typically deployed as part of the LAMP stack (which stands for Linux, Apache, MySQL, and PHP) and is the most used open-source database in the world as of this writing.
This article describes how to establish a new MySQL user and grant them the necessary permissions to execute certain tasks.
Prerequisites
You must have access to a MySQL database in order to follow along with this instruction. This guide assumes that this database is installed on an Ubuntu 20.04-based virtual private server, but the principles it covers should apply regardless of how you access your database.
If you do not have access to a MySQL database and wish to create one yourself, you can use one of our instructions on How To Install MySQL. Again, regardless of your server’s underlying operating system, the techniques for creating a new MySQL user and granting them privileges are largely identical.
Please notice that any elements of example instructions that require modification or customization are indicated in this manner throughout the documentation.
Creating a New User
MySQL creates a root user account upon installation, which you can use to manage your database. This user has complete power over every database, table, user, and so on on the MySQL server, as it has full rights. Due to this, it is advisable to avoid using this account for anything other than administration. This step describes how to use the root MySQL user to create and provide privileges to a new user account.
The default authentication method for the root MySQL user on Ubuntu systems running MySQL 5.7 (and later) is the auth socket plugin rather than a password. This plugin requires that the operating system user who launches the MySQL client and the MySQL user given in the command have identical names. This indicates that the mysql command must be executed with the rights of the root Ubuntu user in order to obtain access to the root MySQL user:
sudo mysql
Note: If your MySQL root user is configured to utilize a password for authentication, you must use a different command to enter the MySQL shell. The following will execute your MySQL client with standard user credentials; gaining administrator capabilities within the database will need authentication 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. These conform to the following syntax:
CREATE USER 'username'@'host' IDENTIFIED WITH authentication_plugin BY 'password';
After CREATE USER, a username is specified. This is followed immediately by a @ symbol and the hostname from which the user will connect. Specify localhost if you only intend to access this user locally from your Ubuntu server. It is not always required to enclose the username and host in single quotes, but doing so can assist prevent issues.
You have numerous options for selecting the authentication plugin for your users. The previously mentioned auth socket plugin might be convenient, as it provides strong database security without requiring valid users to input a password. However, this inhibits remote connections, which might cause complications when external programs need to interface with MySQL.
Alternately, you can omit the WITH authentication plugin component of the code to have MySQL’s default plugin, caching sha2 password, authenticate the user. Due to its robust security features, the MySQL documentation recommends this plugin for users who wish to log in using a password.
Execute the following command to create a user with caching sha2 password authentication. Ensure that you replace sammy with your preferred username and password with a secure password.
CREATE USER 'sammy'@'localhost' IDENTIFIED BY 'password';
Note: There is a known bug with certain PHP versions that causes caching sha2 password issues. If you intend to utilize this database with a PHP program, such as phpMyAdmin, you should create a user that will authenticate with the older, but still safe, mysql native password plugin.
CREATE USER 'sammy'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
If you are unsure, you can always create a user that authenticates with the caching sha2 plugin plugin and then ALTER it with the following command:
ALTER USER 'sammy'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
After establishing a new user, you can assign them the necessary permissions.
Granting a User Permissions
Following is the general syntax for granting user privileges:
GRANT PRIVILEGE ON database.table TO 'username'@'host';
This syntax example’s PRIVILEGE value specifies what activities the user is permitted to execute on the specified database and table. By separating each permission with a comma, you can provide several privileges to a single user in a single command. You can also provide a user global permissions by substituting asterisks (*) for the database and table names. Asterisks are special characters used to denote “all” databases or tables in SQL.
As an example, the following command allows a user the ability to CREATE, ALTER, and DROP databases, tables, and users, as well as INSERT, UPDATE, and DELETE data from any table on the server. It also allows the user to run SELECT queries, add foreign keys with the REFERENCES keyword, and perform FLUSH operations with the RELOAD privilege. Nevertheless, you should only grant people the access they require, so feel free to alter your own user’s privileges as needed.
The whole list of allowed privileges can be found in the official MySQL manual.
Replace sammy with the name of your MySQL user to provide the following capabilities to your MySQL user:
GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'sammy'@'localhost' WITH GRANT OPTION;
Note that WITH GRANT OPTION is also included in this statement. This permits your MySQL user to grant any permissions it possesses to other system users.
Some users may wish to offer their MySQL user the ALL Powers privilege, which will grant them extensive superuser privileges similar to those of the root user.
GRANT ALL PRIVILEGES ON *.* TO 'sammy'@'localhost' WITH GRANT OPTION;
This MySQL user will have complete control over every database on the system if granted such broad capabilities.
Many guides recommend using the FLUSH PRIVILEGES command immediately following a CREATE USER or GRANT statement to reload the grant tables and guarantee the new privileges take effect:
FLUSH PRIVILEGES;
Nevertheless, according to the official MySQL literature, when you edit the grant tables indirectly with an account management statement such as GRANT, the database will immediately reload the grant tables into memory, thus the FLUSH PRIVILEGES command is not required in our instance. On the other hand, its execution will not negatively impact the system.
If you need to revoke a permit, the format is nearly identical to when it was granted:
REVOKE type_of_permission ON database_name.table_name FROM 'username'@'host';
Note that the terminology for withdrawing permissions requires the use of FROM instead of TO, which was used for granting access.
By executing the SHOW GRANTS command, you can view a user’s current permissions:
SHOW GRANTS FOR 'username'@'host';
DROP can be used to delete users just like it can be used to destroy databases:
DROP USER 'username'@'localhost';
After creating your MySQL user and providing them rights, you can exit the MySQL client by clicking the following:
exit
In the future, to log in as your new MySQL user, you would execute the subsequent command:
mysql -u sammy -p
The -p switch prompts the MySQL client for the MySQL user’s password in order to authenticate the user.
Conclusion
You have learned how to add new users and grant them various permissions in a MySQL database by following this tutorial. You might now continue to investigate and experiment with alternative rights settings for your MySQL user, or you could learn more about MySQL configurations at a higher level.
