tag:blogger.com,1999:blog-16959946.post6472785899669595714..comments2023-12-09T16:44:47.897+01:00Comments on The Data Charmer: Revisiting roles in MySQL 8.0Giuseppe Maxiahttp://www.blogger.com/profile/15801583338057324813noreply@blogger.comBlogger10125tag:blogger.com,1999:blog-16959946.post-60708241190532867242017-09-26T16:24:33.618+02:002017-09-26T16:24:33.618+02:00Giuseppe:
* Thanks for the link to Bug#85562. Su...Giuseppe:<br /><br />* Thanks for the link to Bug#85562. Subscribed.<br />* I've talked to people about this (FR for splitting replication related privileges) but not made a FR. I already make too many and some are still open after several years. I can add such a FR if others think this would be useful.<br /><br />Kristofer:<br /><br />* "I think it should be possible to create an enforced sync point for a particular user": this would be useful and perhaps to be honest it wouldn't be used frequently but given the bug referenced above for some cases this would be useful.<br />* I'll also have to come up with a use case but just consider applications that run for a long time and need after connecting to have access to objects that weren't configured previously. Having a way to allow this would be good. right now there's no way to monitor if the user that's connected actually has the same grants as indicated by SHOW GRANTS FOR 'user'@'host' and I'm not sure if there's an indication of when the grants were last changed which might at least give you a reference point. Right now the typical solution is to kill connections and let them come back. That works but of course will generate client errors.<br />* SUPER isn't a privilege? Well I think it is. With it you can do all sorts of exciting things, especially with replication. So I think that it would be good to decompose all of the individual things that you can do with super into a role called super and contemplate the migration to use the super role rather than GRANT SUPER on *.* TO xxxx. This would allow me to enable devs or applications to stop or start replication but not to change the master or wipe out the bin logs or relay logs which I'd prefer to be left to those that manage the mysql servers. So a stop_start_slave type privilege, a change_master type privilege, and clearly reset_slave and reset_master type privileges fall into those I'd like to see.<br />Simon J Muddhttps://www.blogger.com/profile/02803644910012672552noreply@blogger.comtag:blogger.com,1999:blog-16959946.post-71234537905426457062017-09-26T13:52:51.828+02:002017-09-26T13:52:51.828+02:00@Kristofer,
Yes, thanks. If the result is that a r...@Kristofer,<br />Yes, thanks. If the result is that a role assignment becomes effective immediately, I don't care much about the syntax.Giuseppe Maxiahttps://www.blogger.com/profile/15801583338057324813noreply@blogger.comtag:blogger.com,1999:blog-16959946.post-8800376787377809302017-09-26T13:47:51.457+02:002017-09-26T13:47:51.457+02:00@giuseppe Yes, and I see your point very well. It ...@giuseppe Yes, and I see your point very well. It has been discussed to some length but out conclusion after consolidating multiple views is that the current pattern, although it has it's downsides, is a flexible all around solution.<br /><br />What we can do though is to make a "GRANT r1 TO giuseppe@localhost" statement automatically set r1 the default role for giuseppe@localhost unless and exception clause is added to GRANT. Example: "GRANT r1 TO giuseppe@localhost NOT DEFAULT".<br /><br />I believe this will simplify the workflow and sufficiently mimic what you are asking for. How does that sound to you?Entry 23 Eco of Kristoferhttps://www.blogger.com/profile/08660248305487815756noreply@blogger.comtag:blogger.com,1999:blog-16959946.post-78104113707672683442017-09-26T12:28:35.751+02:002017-09-26T12:28:35.751+02:00@Kristofer,
When I gave my feedback to the MySQL t...@Kristofer,<br />When I gave my feedback to the MySQL team back in April I explained that SET ROLE (including SET DEFAULT ROLE) is a break of users expectations. Until now, when a new privilege was needed, the DBAs had only to run a GRANT for that privilege, and the job was done. The necessity of having a SET ROLE and SET DEFAULT ROLE means that we're taking the workload backwards.<br /><br />The ideal workload is be that a "GRANT rolename to username" would work like "GRANT SELECT, INSERT ON someobject TO username" (with no SET ROLE needed.) Having a disabled role should be the exception, not the rule.<br /><br />We should have something like "GRANT rolename DISABLED TO username" for the cases when DBAs need to give roles that are not active immediately.<br />Giuseppe Maxiahttps://www.blogger.com/profile/15801583338057324813noreply@blogger.comtag:blogger.com,1999:blog-16959946.post-12564425185622803762017-09-26T11:53:07.539+02:002017-09-26T11:53:07.539+02:00@simon, @giuseppe Please note that you now also ca...@simon, @giuseppe Please note that you now also can set the DEFAULT ROLE when you create the user. When a user has a default role, he won't need to concern himself with SET ROLE - default roles are activated by default if the user also have the appropriate GRANT.<br /><br />As for privilege caching, we're trying to minimize the performance overhead of security and in doing so we must make some compromises. I think it should be possible to create an enforced sync point for a particular user, but the privilege object maps are stored in global containers and must be locked for writing. Changing the privilege object maps will of course always invalidate the role aggregates so they need to be recalculated too. We make no effort to calculate the optimal recalculation plan for roles instead we just recreate all of them when a GRANT is issued.<br /><br />It would be great if you could write down the use case that concerns you as a feature request on bugs.mysql.com.<br /><br />We've introduced quite a few new privileges primarily with the intent to offer a more fine grained alternative to the somewhat peculiar privilege SUPER (which isn't really a super privilege). Example: BACKUP_ADMIN,BINLOG_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SET_USER_ID,SYSTEM_VARIABLES_ADMIN,XA_RECOVER_ADMIN<br /><br />If there are some missing here you should definitely toss in a feature request as it's now super easy to add new privilege objects.<br /><br />Entry 23 Eco of Kristoferhttps://www.blogger.com/profile/08660248305487815756noreply@blogger.comtag:blogger.com,1999:blog-16959946.post-26842624194105605302017-09-26T09:45:47.414+02:002017-09-26T09:45:47.414+02:00@Simon,
* The remedy for SET ROLE problem is in t...@Simon,<br /><br />* The remedy for SET ROLE problem is in the auto activation of roles (see above in the article);<br /><br />* Changing grants is an issue that has been reported already (See <a href="https://bugs.mysql.com/bug.php?id=85562" rel="nofollow">Bug#85562</a>, but I guess that it will require some global refactoring of the privilege system to be properly addressed with either traditional privileges or roles. In my tests, FLUSH PRIVILEGES has no effect on running sessions. Even a global change will only affect the user after a reconnect.<br /><br />* I haven't seen any increase in granularity for replication related privileges. Is there a feature request for that?Giuseppe Maxiahttps://www.blogger.com/profile/15801583338057324813noreply@blogger.comtag:blogger.com,1999:blog-16959946.post-77151248610752234672017-09-26T09:25:43.339+02:002017-09-26T09:25:43.339+02:00A couple of related comments to roles:
* Someone s...A couple of related comments to roles:<br />* Someone said to me the other day "MySQL will implement SET ROLE". I think that misses the point for those of us who have a large number of application users, especially if they use a pooled connection to the backend database. They will NEVER use that command (unless the pool connector knows what this means and which connected has which role). All the app cares about is that it has the privileges it needs to do its job. If that can be more easily defined in terms of "role access" (several apps may share similar permissions) then using roles is cleaner than providing individual grants to each user. I think this will simplify grant management and is welcome. While I do also manage "user" accounts where I could see SET ROLE being potentially useful this is really nothing more than unix's sudo command, and having to explain to the user to use it requires more effort. They might wonder why I didn't just give them the rights directly in the first place.<br />* Changing grants is a problem these days. You can run the GRANT statement but unless I'm mistaken active connections will not notice. The solution has traditionally to do FLUSH PRIVILEGES but that happens globally and so may have more impact so I'd love to see something like FLUSH PRIVILEGES FOR 'user'@'domain' or FLUSH PRIVILEGES FOR 'user'@'domain' NOW to just update the specific grants for the specific MySQL user. Change management would probably prefer that as any changes can be suffixed with such a command and you'd know that all users now are applying the grants whether new connections or not.<br />* Looking at https://dev.mysql.com/doc/refman/8.0/en/grant.html I don't see any more fine grained privileges than was present in 5.7. I'd like to see privileges for STOP SLAVE/START SLAVE (but perhaps a different one for CHANGE MASTER TO) as some replication related activities require REPLICATION CLIENT, REPLICATION SLAVE or SUPER and these grants include more than a single command and some commands I might give to a user and others I might prefer not to. Finer level grants are still not present yet and would be good to add.Simon J Muddhttps://www.blogger.com/profile/02803644910012672552noreply@blogger.comtag:blogger.com,1999:blog-16959946.post-21242546298781975742017-09-25T16:53:32.496+02:002017-09-25T16:53:32.496+02:00@giuseppe Ok, I see that you write that this is a ...@giuseppe Ok, I see that you write that this is a significant inconvenience that can cost time and cause confusion. I'll reopen the bug and see if I can address it somehow.Entry 23 Eco of Kristoferhttps://www.blogger.com/profile/08660248305487815756noreply@blogger.comtag:blogger.com,1999:blog-16959946.post-80525745709885821562017-09-25T13:48:02.699+02:002017-09-25T13:48:02.699+02:00@Kristofer,
Thanks for your comments.
With the cur...@Kristofer,<br />Thanks for your comments.<br />With the current code, when I assign a default role that does not exist, I don't get any error or warning. If my purpose was to enable a user to start working with a given set of privileges, I will consider the lack of errors as correct assignment.<br />The problem will be felt only when the user starts operations, and by then we will have errors that need to be addressed and operations that can't continue. We may think of "users" as humans who will realize that they lack the proper privileges and take action to activate the proper role. But more often in modern operations a "user" is just an account for an automated process in an application server or in a nighttime cron job. <br />Here, the lack of error during the assignment will result in lost minutes or hours during operations.<br />When I try to assign a non-existing role to a user, i get a rightful error. I would like to have the same loud error when I try to assign a non-existing default role to an user. <br />Giuseppe Maxiahttps://www.blogger.com/profile/15801583338057324813noreply@blogger.comtag:blogger.com,1999:blog-16959946.post-3577890071603416362017-09-25T12:32:12.021+02:002017-09-25T12:32:12.021+02:00A sophism is loosely translated as "a clever ...A sophism is loosely translated as "a clever but false argument, especially one used deliberately to deceive". I naturally disagree that I used a false argument and I also disagree that I deliberately tried to deceive you. I do respect your very strong opinion on this matter and I'm willing to reconsider if you could better help me to realize the practice complications which makes this bug critical for you. Please note that relaxing constrains sometimes aids the overall goal of simplicity. You seem concerned that you're likely to make a severe mistake because this constraint isn't in place. Can you elaborate more on this please?Entry 23 Eco of Kristoferhttps://www.blogger.com/profile/08660248305487815756noreply@blogger.com