MySQL User Permissions

A quick introduction at User Permissions in a MySQL Database

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

Give privilege to create new databases to a user

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

Give privileges to a user to create new tables in a specific database

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

Give privilege to read (query) a specific database to a user

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

Give privilege to read a specific database table to a user

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

Give privilege to insert, update and delete rows in a specific database to a user

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

Give privilege to delete tables in a specific database to a user

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

Give privilege to delete databases to a user

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

Give all privilege on a specific database to a user

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

Give all privileges to a user

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

Revoke a privilege

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';