List of All Privileges Table on MySQL

Question

I’m trying to connect MySQL to an identity management (IDM) platform - goal is, to be able to create roles from the IDM site out of the MySQL privileges and be able to provision it to new users, which should create as well in the MySQL.

To do this, I need to connect MySQL to the IDM platform, which I have already done. I’m stuck on the next step though: I need the list of all privileges that can be granted to a user in MySQL then map it as entitlements on the IDM site. I have checked the information_schema.user_privileges/mysql.user but found that it only contains the current privileges of existing users. The privileges were on columns, I think if there is a way I can get it as records then I may consider using it.

Can anyone please help? Or if someone knows any methodology to do this better please let me know.

Answer 1

The intervals for MySQL grants comes with different levels.

You simply extract the grants from the mysql schema.

Problem is: From which level do you need the extraction ???

You will need the following query to show you global, database, and table grants

SELECT
    IF(table_name='user','Global',IF(table_name='db','Database','Table')) grant_level,
    REPLACE(LEFT(column_name,LENGTH(column_name) - 5),'_',' ') grant_type
FROM (SELECT table_name,column_name FROM information_schema.columns
WHERE table_schema='mysql' AND table_name IN ('user','db','tables_priv')
AND column_name LIKE '%\_priv') AAA
ORDER BY table_name='user' DESC,table_name,column_name;

For MySQL 5.7.12, you will see this:

mysql> SELECT
    ->     IF(table_name='user','Global',IF(table_name='db','Database','Table')) priv_level,
    ->     REPLACE(LEFT(column_name,LENGTH(column_name) - 5),'_',' ') priv_type
    -> FROM (SELECT table_name,column_name FROM information_schema.columns
    -> WHERE table_schema='mysql' AND table_name IN ('user','db','tables_priv')
    -> AND column_name LIKE '%\_priv') AAA
    -> ORDER BY table_name='user' DESC,table_name,column_name;
+------------+-------------------+
| priv_level | priv_type         |
+------------+-------------------+
| Global     | Alter             |
| Global     | Alter routine     |
| Global     | Create            |
| Global     | Create routine    |
| Global     | Create tablespace |
| Global     | Create tmp table  |
| Global     | Create user       |
| Global     | Create view       |
| Global     | Delete            |
| Global     | Drop              |
| Global     | Event             |
| Global     | Execute           |
| Global     | File              |
| Global     | Grant             |
| Global     | Index             |
| Global     | Insert            |
| Global     | Lock tables       |
| Global     | Process           |
| Global     | References        |
| Global     | Reload            |
| Global     | Repl client       |
| Global     | Repl slave        |
| Global     | Select            |
| Global     | Show db           |
| Global     | Show view         |
| Global     | Shutdown          |
| Global     | Super             |
| Global     | Trigger           |
| Global     | Update            |
| Database   | Alter             |
| Database   | Alter routine     |
| Database   | Create            |
| Database   | Create routine    |
| Database   | Create tmp table  |
| Database   | Create view       |
| Database   | Delete            |
| Database   | Drop              |
| Database   | Event             |
| Database   | Execute           |
| Database   | Grant             |
| Database   | Index             |
| Database   | Insert            |
| Database   | Lock tables       |
| Database   | References        |
| Database   | Select            |
| Database   | Show view         |
| Database   | Trigger           |
| Database   | Update            |
| Table      | Column            |
| Table      | Table             |
+------------+-------------------+
50 rows in set (0.02 sec)

This should give you a good starting point.

You could probably create it as a table with something like this

CREATE TABLE grant_records ENGINE=MyISAM AS
SELECT
    IF(table_name='user','Global',IF(table_name='db','Database','Table')) grant_level,
    REPLACE(LEFT(column_name,LENGTH(column_name) - 5),'_',' ') grant_type
FROM (SELECT table_name,column_name FROM information_schema.columns
WHERE table_schema='mysql' AND table_name IN ('user','db','tables_priv')
AND column_name LIKE '%\_priv') AAA
ORDER BY table_name='user' DESC,table_name,column_name;