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.

up vote 2 down vote accepted

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;
  • Great, thanks Rolando! Exactly what I want to see. How about for roles? There are default administrative roles in MySQL already mapped to privileges (can see upon setting up new users). Is that something I can capture as well to IDM platform or is that only mySQL-level specific? – user68890 Mar 8 at 1:12
  • What you want will be in MySQL 8.0, which now has roles : dev.mysql.com/doc/refman/8.0/en/roles.html – RolandoMySQLDBA Mar 8 at 2:08

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.