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
- To export to a file use SELECT ROLES_GRAPHML()
- Open the graphml file in yEd
- Go to Edit –> Propert Mapper
- Add a new configuration using the upper left green + sign
- Add a new property using the right green + sign and Apply
- Go to tools –> Fit Label to Node
- 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 😉
As long as this bug https://bugs.mysql.com/bug.php?id=84244 is not solved, roles in MySQL are not really usable IMO.
However in MariaDB they are. The difference between the 2 is that in MariaDB the mysql.user table has a column `is_role`. Due to this it is possible for clients to distingusih roles and plain users in MariaDB, but not in MySQL.
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.
[…] MySQL 8.0 Roles and Graphml – visualize roles, I like this (despite MariaDB Server having roles since 10.0.5, this is not one of the available features). […]
[…] MySQL 8.0 is coming with SQL Roles . I’ve already quoted them before in some posts (here and here). Giuseppe Maxia is doing a great job testing and promoting them (thank you for that!). Of course […]
[…] Read More (Community […]
[…] MySQL 8.0 Roles and Graphml – visualize roles, I like this (despite MariaDB Server having roles since 10.0.5, this is not one of the available features). […]