How to get a list of permissions of mysql users
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:
SELECT
FROM
ORDER BY
|
* the list of privileges for a database:
SELECT
FROM
ORDER BY
|
* the list of privileges for tables:
SELECT
FROM
WHERE
ORDER BY
|
* the list of privileges for views:
SELECT
FROM
WHERE
ORDER BY
|
* the list of privileges for table columns:
SELECT
FROM
WHERE
ORDER BY
|
* the list of privileges for view columns:
SELECT
FROM
WHERE
ORDER BY
|
* the list of privileges for procedures:
SELECT
FROM
WHERE
ORDER BY
|
* the list of privileges for functions:
SELECT
FROM
WHERE
ORDER BY
|
No comments to display
No comments to display