MySQL 8.0 Roles and Graphml

on
Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedIn

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 😉

Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedIn

4 thoughts on “MySQL 8.0 Roles and Graphml

    1. 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.

    2. 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

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

recent

Last Tweets Last Tweets

    Locations of visitors to this page
    categories