How To Create a New User and Grant Permissions in MySQL

MySQL is an open-source database management system that is often used as part of the LAMP stack (Linux, Apache, MySQL, and PHP). In this guide, we will show you how to create a new MySQL user and grant them the necessary permissions to perform various actions.

To begin, log in to the MySQL prompt as the root user:

1

mysql -u root -p

To create a new user, use the CREATE USER statement in the following format:

1

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

Replace username with the desired username and host with the hostname where the user will connect from. If the user will only be accessing the database locally, you can use localhost or 0.0.0.0. It is also possible to specify a specific netmask, such as 192.168.1.%, to allow access from a specific range of IP addresses.

If you are planning to use the database with a PHP application (such as phpMyAdmin), you may want to create a user that authenticates with the mysql_native_password plugin instead of the default caching_sha2_password plugin due to a known issue with some versions of PHP. To do this, use the following CREATE USER statement:

1

CREATE USER 'app'@'host' IDENTIFIED WITH mysql_native_password BY 'password';

If you have already created a user with the caching_sha2_password plugin, you can use the ALTER USER statement to change the authentication method:

1

ALTER USER 'app'@'host' IDENTIFIED WITH mysql_native_password BY 'password';

To grant a user privileges, use the following syntax:

1

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

The available privileges include ALL PRIVILEGES, which allows full server administration, and various privileges for specific contexts such as tables, routines, and databases. You can also use ALL to grant all privileges within a specific context.

For example, to grant all privileges on a database called mydatabase to the user app@localhost, use the following command:

1

GRANT ALL PRIVILEGES ON mydatabase.* TO 'app'@'localhost';

You can also grant specific privileges rather than all privileges. For example, to grant the SELECT, INSERT, and UPDATE privileges on a table called mytable in the mydatabase database to the user app@localhost, use the following command:

1

GRANT SELECT, INSERT, UPDATE ON mydatabase.mytable TO 'app'@'localhost';

Once you have granted the desired privileges to the user, don’t forget to flush the privileges to apply the changes:

1

FLUSH PRIVILEGES;

Summary of Available Privileges

Revoke a permission

1

REVOKE type_of_permission ON database_name.table_name FROM 'app'@'host';

Delete a user

1

DROP USER 'app'@'host';

That’s it! You have now successfully created a new MySQL user and granted them the necessary permissions.