MariaDB Roles
CC Attribution 2.0 Belgium:
You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal purpose as long as the work is attributed to the author in the manner specified by the author or licensor.
Ciorbaru, Vicentiu
MariaDB has had roles all the way back in 10.0 (2013). MySQL now supports roles as well, starting with 8.0. This talk will go through an overview of Roles in MariaDB and how they can be used. The talk will also highlight the differences, as well as the migration requirements, should you need to move to (or from) MariaDB. Roles in MariaDB follow the SQL Standard implementation. Roles are a very useful feature for ensuring ease of use for a DBA. Not only that, but thanks to DEFAULT ROLE, applications not previously aware of roles can make use of them transparently. In this talk we will cover the following aspects of Roles: * Creating, granting, activating. * Roles granted to roles (the role graph) * Default Roles * Information schema tables * Differences to MySQL, migration with mariadb-dump.
 Hello, my name is Vicenzo Ciorbaro and today we're going to talk about MariaDB roles as part of the MariaDB FOSDEM Dev Room. So a little bit about myself. Now I've been a MariaDB developer for more than six years now and I've taken part in implementing a significant number of features including roles, window functions, as well as compatibility layers for MySQL and MariaDB migration, as well as distributions and other topics. So today we're going to dig into roles. The purpose of this talk is to give you an introduction into MariaDB roles, how they're supposed to be used and some of the benefits and differences with other databases. So roles initially were part of the open source database world released by POSgress in 2005. MariaDB followed in 2013 with the release of 10.0 and then they were added into MySQL 8.0 at a later date, 2016. The good part about roles is that most features correspond to the SQL standards. So the implementation is pretty much similar across databases, each with its own differences and extensions. MariaDB follows the standard closest, so whatever you see here, that's what the standard generally requires. So what are roles? Well, roles are kind of like users, but they have some special powers. Roles just like users can access objects in a database. They can read, write tables, create and drop databases or do any other different operation like creating users or setting up replication. What cannot be done, however, with roles is to log into the database server. And this is the first difference we can encounter. MySQL does allow authentication with roles. Now the special powers of roles is that they can inherit writes from other roles. And this is very powerful. We're going to see this a bit later. Roles can be granted to users, much like it would grant a regular privilege to a user, and then they can be subsequently activated. Simply granting the role doesn't mean that the user gets the access. They need to enable the role by the command called set role. Now the benefit of roles is that due to their nature, they simplify administering the database, which means easier maintenance and potential auditing to see which users have access to which resources. And speaking of access to resources, it's trivial to implement the least privileged principle using roles. Just don't enable roles that you don't need, and the solution is achieved. Now you might think that roles have a performance impact. However, we've worked very hard to make sure that it's not the case. For most practical examples, this will not be a problem. And with the addition of default role, which came in the version of 10.1 of MariaDB, with default role, you can integrate legacy applications and make use of roles functionality without having to modify the application at all. To better showcase this, I like to use examples and use cases. So here we're going to take a simplified data warehouse, and we're going to work with transactions. We have a number of users that want to do different tasks. So Rachel is part of the reporting department, so she wants to look at data. We also have an import robot, which its own purpose is to import data into the database, nothing else. Then we have Dave, who is a developer, which means that Dave needs to have full access to the data warehouse databases, including the staging one. And then we have Alex, he's an admin, and he can do all that Dave can do, but also create other users and create basically other developers. Now the way we do this, using roles, is we create roles specifically for this task. So we're going to have to create a report role, an import role, a developer role, and an admin role. And I'm going to only grant select to the report role, insert to the import role, all privileges on the databases to the dev, and also to the admin, but also the admin gets the ability to create more users. Now let's have a look at what's going on here. So the final part is what we call the syntax for granting roles. And here we grant Rachel the report role, we grant the import robot the import role, then we grant Dave the developer role, and finally Alex the admin role. If we want to look at this visually, roles are in circles, brown, we have users at the bottom, and arrow represents a grant. So Rachel got the report role, and then the report role in turn has the select privilege, the same for import, dev, and admin. Notice that there is a lot of redundancy between the dev and the admin role. And the question is, can we do better? The point is to simplify the way we do database administration. So how do we do it? Well, this is where roles hierarchies come into play, and this is the best that roles have to offer. You can eliminate any duplication by using inheritance, much like you would when creating a class hierarchy in a object-oriented programming language. Now the rights that were granted to a role propagate to grantees, and that means that we can, here's our example from before. Now here are the duplicate commands. Now we can get rid of all this by simply giving the admin role, granting it the developer role. And by doing this, we've eliminated duplication, and we've achieved the same thing. So admin now inherits all the privileges on the data warehouse and staging databases. And here's the visual example. These are the grants we are removing, and we're simply removing them by granting admin to dev directly. Now we need to be careful here, because simply granting roles can introduce problems if, for some reason, trying to grant a role to another role would create a cycle. So for example, if you have role A granted to role B, and then role B granted to role C, and then finally you're trying to grant a role C to role A, you'd create a loop within the roles. And MariaDB will disallow this. You'll get an error when you try to create the loop. MySQL on the other hand will let you create cycles. Now the problem with cycles is that effectively they nullify the hierarchy, and every role in the cycle has the same rights. That's why you need to be careful, because you might end up giving a certain role more rights than you expect. Now when it comes to performance, you might be wondering how is this achieved, how is this whole role hierarchy stored, computed, and used for a privileged checking? Well, you don't want to check the whole hierarchy for every query. That just takes too much time. So to fix this, MariaDB and MySQL implemented caching methodologies. And the way MariaDB does caching is when we first start up the server, we load all the grants into memory and pre-compute the effective rights for every role in the graph, which means that we no longer need to go deep inside the graph to find the effective privileges. The first jump has what we need. We only recompute this graph when stuff changes. For example, during a grant statement, which usually doesn't happen very often. MySQL on the other hand has a slightly different approach in that MySQL will, whenever a user connects, it will create what we call an authentication ID. And this authentication ID is a combination of the username and all the roles that it has active at that point. And it will compute the effective rights for this whole combination and store it in the cache once, which means that for subsequent queries, the checks are going to be fast. But the first time you do this connection, it's going to take a bit longer to get the correct access. Now, with that said, both databases do optimize for the critical path and that is frequent queries and not the less frequent grants changes. MySQL's implementation effectively has no performance penalty on grants, whilst it does have a slower first access check. MyIDB has slightly slower grant operations by computing the necessary steps during the grant, but overall, the performance should be very similar when it comes to checking. Now the other thing we need to look for is how can we do the least privileged principle? And this is simple, but let's see what can happen if we don't do it properly. So let's imagine Dave has a tricky bug to fix and he needs to clone some tables to investigate. So with our previous example, he activates his developer role by doing set role and then he gets all the rights necessary to work. He creates a transactions staging table, just like the one in production, and gets to experimenting with different transactions. Finally, at the end of the day, he finishes, finds the problem and wants to clean up after himself. He does this by issuing drop tables transactions and suddenly he realizes he forgot to change the database to be staging. So because he had the rights on both the production and the staging database, he didn't drop the correct one and now he has other problems to deal with. Now all this can be avoided and the way you should do this is you should spill it roles into safe and dangerous ones. And by dangerous, it can be any role that has access to data and is able to modify it. Safe roles are probably just read-only roles or something that it cannot mistakenly make your application have some downtime. And you should only activate dangerous roles whenever you need them, but not for longer than that. And the good part about roles is that changing a role is very cheap, whilst previously you would have to change users, which would incur potentially a bigger overhead. So this is the advantage of using roles. Roles between MySQL and MariaDB are, there are a few, but they're not insurmountable. So MySQL allows enabling multiple roles at once and this can be done with MariaDB by doing an aggregate role. So just create an intermediate role and you're going to activate that one. The intermediate role should have all the multiple active roles granted to it. Additionally, MySQL implements mandatory roles and these are always active for a user and they are granted automatically when creating a user. MariaDB does not have this, but you can probably achieve a similar setup with default roles. Both MySQL and MariaDB have implemented default role and the thing about default role is that it effectively runs a set role command during connection, which means that you have the rights as soon as you connect to the database, no need to run a separate query for that. Now let's talk about system tables. So MariaDB stores its roles in system tables. Before 10.4, the table name was MySQL user and now it's been renamed and changed in 10.4 to be MySQL global priv. For both cases, you need to look for the is role flag, either as a column or part of the JSON description document. If the is role is marked as yes, then you cannot log in with that entry because it is designated as a role. Other than that, the roles are completely identical to users. You will find the same fields such as the access bits for each individual grant. Now, secondly, we need to store the grants somewhere and these are basically stored as edges in the graph. MySQL roles mapping is what MariaDB uses. There is an additional column inside, which is admin option. This column means that if a role is granted, it can be granted to others by that user. It lets you propagate the role to other roles or users. Metadata about roles can be fetched from information schema. We have enabled roles and applicable roles. MySQL 8019 has introduced some more tables that are part of the SQL standard and MariaDB should probably implement them as well, most likely in the next release. These roles are role table grants, role routine grants, role column grants, and admin role authorizations. All these are things that MariaDB can implement and it probably will. For the moment, MySQL has an edge when it comes to information schema completeness. Overall, both databases do allow for role-based access control. From a functional perspective, they pretty much get the job done. There are some differences, but most of them should probably not impact your use case much. There are some differences when it comes to migrating from MySQL to MariaDB. This is what I'm going to cover next. Given that MySQL introduced roles in 80, the only way to migrate from MySQL to MariaDB is by using a dump and restore. For system tables, this is not enough because the corresponding tables do not match. MySQL uses different tables to store roles information. It's been working this area, though, and thanks to a task called MDEV 23630, it is possible to now drop to dump roles, not as insert statements, but as create user and role statements. You can do this by running MariaDB dump on MySQL. The flag is a dash dash system and the switch for it is user to get all the user grants. There still are problems that cannot be solved automatically, at least not at this point. There are privileges in MySQL 80 that do not map to MariaDB's privilege system. The names do not match. You can get a dump from MySQL with the correct create role and grants, but you will have to edit them manually whenever you encounter things that do not work on MariaDB. We have created a task to track progress on this. I believe there is a need for as much automation as possible, but there is a limit to how much we probably should invest into simply maintaining the differences between MySQL and MariaDB. This is actually a question for those listening. Do you think we should pursue the use of migration from MySQL to MariaDB more? Or do you think that the current setup is enough? Let us know. With that said, I couldn't be here without FOSDEM having organized this great conference. I also couldn't do my day-to-day job if it weren't for my DB's Foundation sponsors. They helped enable me to work on MariaDB and be part of the MariaDB community. Thank you for everyone involved. You can reach out to me on Zulip. You can also find me via email, vchansu.mariaDB.org, and you can find more about me at MariaDB.org slash vchansu. I hope this information was useful. Thank you very much and enjoy the rest of FOSDEM.
 Hello, my name is Vicenzo Ciorbaro and today we're going to talk about MariaDB roles as  part of the MariaDB FOSDEM Dev Room.  So a little bit about myself.  Now I've been a MariaDB developer for more than six years now and I've taken part in  implementing a significant number of features including roles, window functions, as well  as compatibility layers for MySQL and MariaDB migration, as well as distributions and other  topics.  So today we're going to dig into roles.  The purpose of this talk is to give you an introduction into MariaDB roles, how they're  supposed to be used and some of the benefits and differences with other databases.  So roles initially were part of the open source database world released by POSgress in 2005.  MariaDB followed in 2013 with the release of 10.0 and then they were added into MySQL  8.0 at a later date, 2016.  The good part about roles is that most features correspond to the SQL standards.  So the implementation is pretty much similar across databases, each with its own differences  and extensions.  MariaDB follows the standard closest, so whatever you see here, that's what the standard generally  requires.  So what are roles?  Well, roles are kind of like users, but they have some special powers.  Roles just like users can access objects in a database.  They can read, write tables, create and drop databases or do any other different operation  like creating users or setting up replication.  What cannot be done, however, with roles is to log into the database server.  And this is the first difference we can encounter.  MySQL does allow authentication with roles.  Now the special powers of roles is that they can inherit writes from other roles.  And this is very powerful.  We're going to see this a bit later.  Roles can be granted to users, much like it would grant a regular privilege to a user,  and then they can be subsequently activated.  Simply granting the role doesn't mean that the user gets the access.  They need to enable the role by the command called set role.  Now the benefit of roles is that due to their nature, they simplify administering the database,  which means easier maintenance and potential auditing to see which users have access to  which resources.  And speaking of access to resources, it's trivial to implement the least privileged principle  using roles.  Just don't enable roles that you don't need, and the solution is achieved.  Now you might think that roles have a performance impact.  However, we've worked very hard to make sure that it's not the case.  For most practical examples, this will not be a problem.  And with the addition of default role, which came in the version of 10.1 of MariaDB, with  default role, you can integrate legacy applications and make use of roles functionality without  having to modify the application at all.  To better showcase this, I like to use examples and use cases.  So here we're going to take a simplified data warehouse, and we're going to work with  transactions.  We have a number of users that want to do different tasks.  So Rachel is part of the reporting department, so she wants to look at data.  We also have an import robot, which its own purpose is to import data into the database,  nothing else.  Then we have Dave, who is a developer, which means that Dave needs to have full access  to the data warehouse databases, including the staging one.  And then we have Alex, he's an admin, and he can do all that Dave can do, but also create  other users and create basically other developers.  Now the way we do this, using roles, is we create roles specifically for this task.  So we're going to have to create a report role, an import role, a developer role, and  an admin role.  And I'm going to only grant select to the report role, insert to the import role, all  privileges on the databases to the dev, and also to the admin, but also the admin gets  the ability to create more users.  Now let's have a look at what's going on here.  So the final part is what we call the syntax for granting roles.  And here we grant Rachel the report role, we grant the import robot the import role,  then we grant Dave the developer role, and finally Alex the admin role.  If we want to look at this visually, roles are in circles, brown, we have users at the  bottom, and arrow represents a grant.  So Rachel got the report role, and then the report role in turn has the select privilege,  the same for import, dev, and admin.  Notice that there is a lot of redundancy between the dev and the admin role.  And the question is, can we do better?  The point is to simplify the way we do database administration.  So how do we do it?  Well, this is where roles hierarchies come into play, and this is the best that roles  have to offer.  You can eliminate any duplication by using inheritance, much like you would when creating  a class hierarchy in a object-oriented programming language.  Now the rights that were granted to a role propagate to grantees, and that means that  we can, here's our example from before.  Now here are the duplicate commands.  Now we can get rid of all this by simply giving the admin role, granting it the developer  role.  And by doing this, we've eliminated duplication, and we've achieved the same thing.  So admin now inherits all the privileges on the data warehouse and staging databases.  And here's the visual example.  These are the grants we are removing, and we're simply removing them by granting admin  to dev directly.  Now we need to be careful here, because simply granting roles can introduce problems if,  for some reason, trying to grant a role to another role would create a cycle.  So for example, if you have role A granted to role B, and then role B granted to role  C, and then finally you're trying to grant a role C to role A, you'd create a loop within  the roles.  And MariaDB will disallow this.  You'll get an error when you try to create the loop.  MySQL on the other hand will let you create cycles.  Now the problem with cycles is that effectively they nullify the hierarchy, and every role  in the cycle has the same rights.  That's why you need to be careful, because you might end up giving a certain role more  rights than you expect.  Now when it comes to performance, you might be wondering how is this achieved, how is  this whole role hierarchy stored, computed, and used for a privileged checking?  Well, you don't want to check the whole hierarchy for every query.  That just takes too much time.  So to fix this, MariaDB and MySQL implemented caching methodologies.  And the way MariaDB does caching is when we first start up the server, we load all the  grants into memory and pre-compute the effective rights for every role in the graph, which  means that we no longer need to go deep inside the graph to find the effective privileges.  The first jump has what we need.  We only recompute this graph when stuff changes.  For example, during a grant statement, which usually doesn't happen very often.  MySQL on the other hand has a slightly different approach in that MySQL will, whenever a user  connects, it will create what we call an authentication ID.  And this authentication ID is a combination of the username and all the roles that it  has active at that point.  And it will compute the effective rights for this whole combination and store it in the  cache once, which means that for subsequent queries, the checks are going to be fast.  But the first time you do this connection, it's going to take a bit longer to get the  correct access.  Now, with that said, both databases do optimize for the critical path and that is frequent  queries and not the less frequent grants changes.  MySQL's implementation effectively has no performance penalty on grants, whilst it does have a slower  first access check.  MyIDB has slightly slower grant operations by computing the necessary steps during the  grant, but overall, the performance should be very similar when it comes to checking.  Now the other thing we need to look for is how can we do the least privileged principle?  And this is simple, but let's see what can happen if we don't do it properly.  So let's imagine Dave has a tricky bug to fix and he needs to clone some tables to investigate.  So with our previous example, he activates his developer role by doing set role and then  he gets all the rights necessary to work.  He creates a transactions staging table, just like the one in production, and gets to experimenting  with different transactions.  Finally, at the end of the day, he finishes, finds the problem and wants to clean up after  himself.  He does this by issuing drop tables transactions and suddenly he realizes he forgot to change  the database to be staging.  So because he had the rights on both the production and the staging database, he didn't drop  the correct one and now he has other problems to deal with.  Now all this can be avoided and the way you should do this is you should spill it roles  into safe and dangerous ones.  And by dangerous, it can be any role that has access to data and is able to modify it.  Safe roles are probably just read-only roles or something that it cannot mistakenly make  your application have some downtime.  And you should only activate dangerous roles whenever you need them, but not for longer  than that.  And the good part about roles is that changing a role is very cheap, whilst previously you  would have to change users, which would incur potentially a bigger overhead.  So this is the advantage of using roles.  Roles between MySQL and MariaDB are, there are a few, but they're not insurmountable.  So MySQL allows enabling multiple roles at once and this can be done with MariaDB by  doing an aggregate role.  So just create an intermediate role and you're going to activate that one.  The intermediate role should have all the multiple active roles granted to it.  Additionally, MySQL implements mandatory roles and these are always active for a user and  they are granted automatically when creating a user.  MariaDB does not have this, but you can probably achieve a similar setup with default roles.  Both MySQL and MariaDB have implemented default role and the thing about default role is that  it effectively runs a set role command during connection, which means that you have the  rights as soon as you connect to the database, no need to run a separate query for that.  Now let's talk about system tables.  So MariaDB stores its roles in system tables.  Before 10.4, the table name was MySQL user and now it's been renamed and changed in 10.4  to be MySQL global priv.  For both cases, you need to look for the is role flag, either as a column or part of the  JSON description document.  If the is role is marked as yes, then you cannot log in with that entry because it is designated  as a role.  Other than that, the roles are completely identical to users.  You will find the same fields such as the access bits for each individual grant.  Now, secondly, we need to store the grants somewhere and these are basically stored as  edges in the graph.  MySQL roles mapping is what MariaDB uses.  There is an additional column inside, which is admin option.  This column means that if a role is granted, it can be granted to others by that user.  It lets you propagate the role to other roles or users.  Metadata about roles can be fetched from information schema.  We have enabled roles and applicable roles.  MySQL 8019 has introduced some more tables that are part of the SQL standard and MariaDB  should probably implement them as well, most likely in the next release.  These roles are role table grants, role routine grants, role column grants, and admin role  authorizations.  All these are things that MariaDB can implement and it probably will.  For the moment, MySQL has an edge when it comes to information schema completeness.  Overall, both databases do allow for role-based access control.  From a functional perspective, they pretty much get the job done.  There are some differences, but most of them should probably not impact your use case much.  There are some differences when it comes to migrating from MySQL to MariaDB.  This is what I'm going to cover next.  Given that MySQL introduced roles in 80, the only way to migrate from MySQL to MariaDB  is by using a dump and restore.  For system tables, this is not enough because the corresponding tables do not match.  MySQL uses different tables to store roles information.  It's been working this area, though, and thanks to a task called MDEV 23630, it is possible  to now drop to dump roles, not as insert statements, but as create user and role statements.  You can do this by running MariaDB dump on MySQL.  The flag is a dash dash system and the switch for it is user to get all the user grants.  There still are problems that cannot be solved automatically, at least not at this point.  There are privileges in MySQL 80 that do not map to MariaDB's privilege system.  The names do not match.  You can get a dump from MySQL with the correct create role and grants, but you will have  to edit them manually whenever you encounter things that do not work on MariaDB.  We have created a task to track progress on this.  I believe there is a need for as much automation as possible, but there is a limit to how much  we probably should invest into simply maintaining the differences between MySQL and MariaDB.  This is actually a question for those listening.  Do you think we should pursue the use of migration from MySQL to MariaDB more?  Or do you think that the current setup is enough?  Let us know.  With that said, I couldn't be here without FOSDEM having organized this great conference.  I also couldn't do my day-to-day job if it weren't for my DB's Foundation sponsors.  They helped enable me to work on MariaDB and be part of the MariaDB community.  Thank you for everyone involved.  You can reach out to me on Zulip.  You can also find me via email, vchansu.mariaDB.org, and you can find more about me at MariaDB.org  slash vchansu.  I hope this information was useful.  Thank you very much and enjoy the rest of FOSDEM.
mariadb_roles_0_179.png mariadb_roles_180_419.png mariadb_roles_420_1055.png mariadb_roles_1056_2531.png mariadb_roles_2532_4331.png mariadb_roles_4332_5939.png mariadb_roles_5940_7223.png mariadb_roles_7224_8411.png mariadb_roles_8412_8855.png mariadb_roles_8856_9419.png mariadb_roles_9420_9791.png mariadb_roles_9792_10559.png mariadb_roles_10560_10835.png mariadb_roles_10836_11315.png mariadb_roles_11316_11603.png mariadb_roles_11604_12299.png mariadb_roles_12300_13091.png mariadb_roles_13092_15587.png mariadb_roles_15588_15911.png mariadb_roles_15912_16835.png mariadb_roles_16836_17339.png mariadb_roles_17340_17759.png mariadb_roles_17760_18575.png mariadb_roles_18576_19835.png mariadb_roles_19836_20435.png mariadb_roles_20436_21419.png mariadb_roles_21420_23543.png mariadb_roles_23544_24599.png mariadb_roles_24600_25307.png mariadb_roles_25308_28151.png mariadb_roles_28152_28703.png mariadb_roles_28704_29159.png mariadb_roles_29160_29435.png mariadb_roles_29436_29549.png