Skip to content

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:

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

→ Here's my latest YouTube video

→ I wrote 17 books to help you become a better developer, download them all at $0 cost by joining my newsletter

JOIN MY CODING BOOTCAMP, an amazing cohort course that will be a huge step up in your coding career - covering React, Next.js - next edition February 2025

Bootcamp 2025

Join the waiting list