Show user permission to MySQL and MariaDb databases

User permission levels in MySQL (and MariaDb) can be drilled down into many layers. From database permission, table permission, column permission and routine permission. There could be more that I don't know of. Its great to have greater control over who have access to the database;

How do I show user permission just at database level? First step is to know where such information is stored, followed by how to retrieve the information.

Access MySQL login as admin OR root.

mysql.user and mysql.db Tables

Have a look at the following tables, as they contain the relevant information

mysql> describe * from mysql.user;
mysql> describe * from mysql.db;

Listing users

Let's say, you want to see which user to host have access

mysql> select user, host from mysql.user;

+------------+-------------+
| user       | host        |
+------------+-------------+
| oc_admin   | %           |
| auditor    | 10.1.20.204 |
| auditor    | 10.1.20.244 |
| root       | 10.1.20.44  |
| tbox       | localhost   |
| mysql.sys  | localhost   |
| oc_admin   | localhost   |
| pentaho    | localhost   |
| root       | localhost   |
+------------+-------------+

List grants for users

mysql> show grants;

+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+

List User Access to Databases, Tables and Columns

mysql> SELECT Host, Db, User from mysql.db

There are separate tables for each database, table or column specific access. Go thru and see what you need in the following tables.

mysql> SELECT Host, Db, User from mysql.db
mysql> SELECT * FROM mysql.db;
mysql> SELECT * FROM mysql.tables_priv;
mysql> SELECT * FROM mysql.columns_priv;