Search code examples
mysqlsqlinformation-schema

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.

Solution

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