MySQL User Permissions

Let's see how to grant permissions (called privileges) to a user of the MySQL database

By default when you create a new MySQL user using the syntax

CREATE USER '<username>'@'localhost' IDENTIFIED BY '<password>';

the user cannot do much. We can say that it can't to anything, actually.

It can't read data from any existing database, let alone modifying the data. And it can't even create a new database.

To make a user do anything, you have to grant privileges to it.

You can do so using the GRANT command.

We can use GRANT <permission>, using the following permission keywords:

• CREATE
• DROP
• DELETE
• INSERT
• SELECT
• UPDATE
• ALL PRIVILEGES

GRANT CREATE ON *.* TO '<username>'@'localhost';

GRANT CREATE ON <database>.* TO '<username>'@'localhost';

GRANT SELECT ON <database>.* TO '<username>'@'localhost';

GRANT SELECT ON <database>.<table> TO '<username>'@'localhost';

GRANT INSERT, UPDATE, DELETE ON <database>.* TO '<username>'@'localhost';

GRANT DROP ON <database>.* TO '<username>'@'localhost';

GRANT DROP ON *.* TO '<username>'@'localhost';

GRANT ALL PRIVILEGES ON <database>.* TO '<username>'@'localhost';

GRANT ALL PRIVILEGES ON *.* TO '<username>'@'localhost';

Example to revoke the DROP privilege on <database>:

REVOKE DROP ON <database>.* TO '<username>'@'localhost';

To revoke all privileges, run:

REVOKE ALL PRIVILEGES ON *.* TO '<username>'@'localhost';

You can visualize the privileges of a single user by running:

SHOW GRANTS FOR '<username>'@'localhost';