MySQL 8.0 Roles and Graphml

You may already know that MySQL 8.0 is coming with a nice requested feature : ROLES

A role is a named collection of privileges. When you watch some slidedeck about MySQL 8.0 and ROLES, some times you can see a graph illustrating the ROLES and their eventual hierarchy. From the documentation, it seems those graphs are made using the ROLES_GRAPHML() function.

I tried it… and my first try is not really what I was expecting…

Input file

To create the input file I used the following command:

mysql> SELECT ROLES_GRAPHML() into outfile '/var/lib/mysql-files/test_roles.graphml';
Query OK, 1 row affected (0.19 sec)

Then I used yEd to open the file and generate a picture:

As I said, this is not really what I expected… Let see what we need to do to achieve the expected result:

 

This is the final result (click to enlarge):

Summary

  1. To export to a file use SELECT ROLES_GRAPHML()
  2. Open the graphml file in yEd
  3. Go to Edit –> Propert Mapper
  4. Add a new configuration using the upper left green + sign
  5. Add a new property using the right green + sign and Apply
  6. Go to tools –> Fit Label to Node
  7. Go to Layout –> Hierarchical

I hope this small procedure helps you.

In case you are looking for more information about roles, please check this presentation from @datacharmer.

And of course, thank you Kristofer Älvring for the help in finding the right settings 😉

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

7 Comments

    • Thx for the comment. However I usually try to never encourage pple to select in the mysql system tables… And even worse, write in it like pple used to do lonnnnnnnnnng time ago.

    • Doesn’t the following comment help ?

      [27 Mar 2017 12:37] Kristofer Pettersson
      Both roles and users are in fact authorization identifiers and share the same namespace per design.

      A role is functionally a role when it’s present in role_edges. Any authorization identifier can be a role and any user can be a role (ie you can authenticate to any authorization identifier unless it’s locked or otherwise restricted)

      There’s no artificial restriction embedded in the system to prevent this but you are free to do as you please.

      As a DBA you might still have a personal intention that a specific authorization identifier must be a role, and then using a name scheme to communicate this intention is in fact not a bad choice. You can for example add a prefix “r_” to those authorization IDs which you intend to be roles and nothing else.

Leave a Reply to MySQL 8.0 Roles and Graphml - SSWUG.ORGCancel Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

As MySQL Community Manager, I am an employee of Oracle and the views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

You can find articles I wrote on Oracle’s blog.