List of all table and column level privileges for a MySQL database

What is the easiest way to list all table-level and column-level privileges for a MySQL database? I need to know what level of customized access has been granted for a specific database and which users have fine-tuned access to certain tables and/or columns.

Answer

To list table level privileges, you can query the INFORMATION_SCHEMA.TABLE_PRIVILEGES table.

The TABLE_PRIVILEGES table has these columns:

• GRANTEE : the name of the account to which the privilege is granted, in 'user_name'@'host_name' format.

• […]

• TABLE_NAME : the name of the table.

• PRIVILEGE_TYPE : The privilege granted. The value can be any privilege that can be granted at the table level; […] . Each row lists a single privilege, so there is one row per table privilege held by the grantee.

To list column level privileges, have a look at the INFORMATION_SCHEMA.COLUMN_PRIVILEGES table.

All table/column level privileges for a specific database:

SELECT * FROM `INFORMATION_SCHEMA`.`TABLE_PRIVILEGES` WHERE TABLE_SCHEMA = 'my_database';
SELECT * FROM `INFORMATION_SCHEMA`.`COLUMN_PRIVILEGES` WHERE TABLE_SCHEMA = 'my_database';