In this blog, we will discuss user permissions in a MySQL database and how to grant privileges to a user.
By default, when you create a new MySQL user using the following syntax:
CREATE USER '<username>'@'localhost' IDENTIFIED BY '<password>';
the user has very limited capabilities. They cannot read or modify data from any existing database, nor can they create a new database. To allow a user to perform actions, you need to grant them privileges.
You can grant privileges using the GRANT command, followed by the specific permissions keywords. Here are some examples:
- Granting privilege to create new databases to a user:
GRANT CREATE ON *.* TO '<username>'@'localhost';
- Granting privileges to a user to create new tables in a specific database:
GRANT CREATE ON <database>.* TO '<username>'@'localhost';
- Granting privilege to read (query) a specific database to a user:
GRANT SELECT ON <database>.* TO '<username>'@'localhost';
- Granting privilege to read a specific database table to a user:
GRANT SELECT ON <database>.<table> TO '<username>'@'localhost';
- Granting privilege to insert, update, and delete rows in a specific database to a user:
GRANT INSERT, UPDATE, DELETE ON <database>.* TO '<username>'@'localhost';
- Granting privilege to delete tables in a specific database to a user:
GRANT DROP ON <database>.* TO '<username>'@'localhost';
- Granting privilege to delete databases to a user:
GRANT DROP ON *.* TO '<username>'@'localhost';
- Granting all privileges on a specific database to a user:
GRANT ALL PRIVILEGES ON <database>.* TO '<username>'@'localhost';
- Granting all privileges to a user:
GRANT ALL PRIVILEGES ON *.* TO '<username>'@'localhost';
To revoke a privilege, you can use the REVOKE command. For example, to revoke the DROP privilege on a specific database:
REVOKE DROP ON <database>.* TO '<username>'@'localhost';
To revoke all privileges, you can 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';
Tags: MySQL, Database, User Permissions, GRANT, REVOKE