How to Get a List of Permissions of MySQL Users

MySQL has advanced database access system. A database user can have access to the whole database, separate tables, or to separate columns of tables. Also there is a restriction for actions a user may perform with records. MySQL server uses several tables in a special database to organize such a complicated database access structure. The access policy is based on the values available in these tables.

The database that MySQL server uses to store internal data about users is called mysql by default. There are tables for storing information about users’ accounts in this database:

    • user contains a list of all users of the MySQL server and their permissions to access the database;
    • db contains a list of databases with a matching list of database users and their privileges for executing operations;
    • tables_priv contains a list of database tables (views) that a user has access to;
    • columns_priv contains a list of columns from the database tables (views) a user has access to;
    • procs_priv contains a list of database procedures (functions) a user has access to.

To get the list of users’ privileges concerning data access, the following queries may be executed:

  • the list of global privileges:
  • the list of privileges for a database:
  • the list of privileges for tables:
  • the list of privileges for views:
  • the list of privileges for table columns:
  • the list of privileges for view columns:
  • the list of privileges for procedures:
  • the list of privileges for functions:
You may need to create a decent printable report with this data, and to give it as a report, for example, by the demand of a customer or authority. For this purpose you may use a special MySQL GUI tool that includes a data report designer.

If you have ready queries, you can make advantage of an easy-to-use wizard and create a report using a predefined template and data grouped, for example, by host, in several minutes.

New Data Report menu

New Data Report menu

Data Report Wizard

Data Report Wizard

Data Report Custom Query

Data Report Custom Query

Data Report Load Query

Data Report Load Query

Data Report Group by Host

Data Report Group by Host

Data Report Title

Data Report Title

Data Report Preview

Data Report Preview

As you can see on these screenshots, we have created a report using dbForge Studio’s wizard without tedious designing the report itself.

Leave a Comment