MySQL user permissions

Quick start of user permissions in MySQL database

Let's see how to grant permissions (called privileges) to users 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 too much. We can say that nothing can actually be done.

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

To enable users to perform any action, you mustGrant privilegesMake it happen.

you can use itGRANTcommand.

We can useGRANT <permission>, Use the following permission keywords:

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

Grant users permission to create new databases

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

Grant user privileges to create new tables in a specific database

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

Grant the user the privilege to read (query) a specific database

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

Grant permission to read a specific databasetableTo the user

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

Grant users the privileges to insert, update and delete rows in a specific database

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

Grant users the privilege to delete tables in a specific database

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

Grant the user permission to delete the database

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

Grant all privileges of a specific database to the user

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

Grant all privileges to the user

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

Revoke privileges

Undo exampleDROPprivilege<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 the following command:

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

More database tutorials: