Making a quick post about viewing grants in MySQL. This can be useful if you ever need to review or briefly audit access for users in MySQL. Below is a general syntax for reviewing permissions for a specific user on a specific host.

SHOW GRANTS FOR '<username>';

or

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

Like with this example. I’m looking at the grants for the user db-admin. Who is expected to access the database from the local server.

SHOW GRANTS FOR 'db-admin'@'localhost';

Based on the output. We can determine that the db-admin user can at lease login because they have the USAGE grant for all databases and tables; indicated by . (database.table). This privilege is given to users when they’re created and it’s just another way of saying, they can login. But, they cant really do anything of significance.

In the second line we see that the same user has access to ALL privileges on the testdatabase2 database and all of its tables. This means that it can do just about anything that it wants to that database and the tables in it. Though, this is with the exception to a few key things like granting privileges that you have on the databse. During account creation if the user was created with GRANT OPTION. This would be a different story.

The last section shows that the db-admin user has the following permissions on the testdb1 database and all of it’s tables.

  • SELECT: Enabled rows to be referenced from tables in the database.
  • INSERT: Allows rows and data to be added to tables in the database.
  • DELETE: Allows rows and data to be removed from tables in the database.
+-----------------------------------------------------------------------+
| Grants for db-admin@localhost                                         |
+-----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `db-admin`@`localhost`                          |
| GRANT ALL PRIVILEGES ON `testdatabase2`.* TO `db-admin`@`localhost`   |
| GRANT SELECT, INSERT, DELETE ON `testdb1`.* TO `db-admin`@`localhost` |
+-----------------------------------------------------------------------+
3 rows in set (0.00 sec)

Reference