MySQL- How To Create a New User and Grant Permissions

To follow this guide, you need access to a MySQL database. This guide assumes the database is installed on a virtual private server running Ubuntu 20.04, though the outlined principles apply to any database access method.

If you don’t yet have a MySQL database and wish to set one up, refer to our guides on How To Install MySQL. Regardless of your server’s operating system, the steps for creating a new MySQL user and granting permissions are generally consistent.

Creating a New User

Upon installation, MySQL creates a root user account with full privileges to manage the database, including control over all databases, tables, and users. It’s advisable to reserve this account for administrative tasks only. This section explains how to use the root MySQL user to create a new user account and assign privileges.

On Ubuntu systems running MySQL 5.7 or later, the root MySQL user authenticates by default using the auth_socket plugin rather than a password. This plugin requires the operating system user to invoke the MySQL client to match the specified MySQL user. Consequently, you must use sudo with the mysql command to access the root MySQL user with root Ubuntu user privileges.

sudo mysql

mysql -u root -p

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' IDENTIFIED WITH authentication_plugin BY 'password';

After the CREATE USER command, specify a username followed by an @ sign and the hostname from which the user will connect. If the user will only access MySQL locally from your Ubuntu server, you can specify localhost. Although wrapping the username and host in single quotes isn’t always necessary, it can help prevent errors.

When choosing your user’s authentication plugin, you have several options. The auth_socket plugin, mentioned earlier, offers strong security by allowing valid users to access the database without a password. However, it prevents remote connections, complicating interactions with external programs.

Alternatively, you can omit the WITH authentication_plugin part of the syntax to use MySQL’s default plugin, caching_sha2_password. This plugin is recommended by the MySQL documentation for users who prefer password authentication due to its robust security features.

To create a user that authenticates with caching_sha2_password, run the following command. Replace arya with your preferred username and password with a strong password of your choosing:

CREATE USER 'arya'@'localhost' IDENTIFIED BY 'password';

CREATE USER 'arya'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

ALTER USER 'arya'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

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

Granting a User Permissions

The general syntax for granting user privileges is as follows:

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

The PRIVILEGE value in this syntax defines the actions the user is allowed to perform on the specified database and table. Multiple privileges can be granted to the same user in one command by separating each privilege with a comma. To grant user privileges globally, use asterisks (*) instead of database and table names. In SQL, asterisks represent “all” databases or tables.

For example, 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. Adjust the privileges to match the user’s needs.

For a full list of available privileges, refer to the official MySQL documentation.

Run this GRANT statement, replacing arya with your MySQL user’s name, to grant these privileges:

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

GRANT ALL PRIVILEGES ON *.* TO 'arya'@'localhost' WITH GRANT OPTION;

Many guides recommend running the FLUSH PRIVILEGES command immediately after executing a CREATE USER or GRANT statement. This command reloads the grant tables, ensuring that the new privileges take effect:

FLUSH PRIVILEGES;

However, according to the official MySQL documentation, when you modify the grant tables indirectly with an account management statement like GRANT, the database reloads the grant tables immediately into memory. This means the FLUSH PRIVILEGES command isn’t necessary in our case. Nonetheless, running it won’t negatively impact the system.

If you need to revoke 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 using FROM instead of TO, which is used when granting permissions.

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

SHOW GRANTS FOR 'username'@'host';

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

exit

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

mysql -u arya -p

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