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