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:
mysql -u root -p
To create a new user, use the CREATE USER
statement in the following format:
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:
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:
ALTER USER 'app'@'host' IDENTIFIED WITH mysql_native_password BY 'password';
To grant a user privileges, use the following syntax:
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:
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:
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:
FLUSH PRIVILEGES;
Summary of Available Privileges
Revoke a permission
REVOKE type_of_permission ON database_name.table_name FROM 'app'@'host';
Delete a user
DROP USER 'app'@'host';
That’s it! You have now successfully created a new MySQL user and granted them the necessary permissions.