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:
|
|
To create a new user, use the CREATE USER
statement in the following format:
|
|
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:
|
|
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:
|
|
To grant a user privileges, use the following syntax:
|
|
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:
|
|
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:
|
|
Once you have granted the desired privileges to the user, don’t forget to flush the privileges to apply the changes:
|
|
Summary of Available Privileges
Revoke a permission
|
|
Delete a user
|
|
That’s it! You have now successfully created a new MySQL user and granted them the necessary permissions.