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