Bug #98876 | All privileges on a database for role doesn't allow user to create | ||
---|---|---|---|
Submitted: | 9 Mar 2020 12:02 | Modified: | 25 Aug 2020 13:53 |
Reporter: | Michael Bausano | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Security: Roles | Severity: | S3 (Non-critical) |
Version: | 8.0.18 | OS: | Any |
Assigned to: | Kristofer Pettersson | CPU Architecture: | Any |
Tags: | authorization |
[9 Mar 2020 12:02]
Michael Bausano
[9 Mar 2020 16:29]
MySQL Verification Team
Hi Mr. Bausano, Thank you for your bug report. However, this is not a bug. For the restrictions of the SET ROLE command, please read paragraph 6.2.5 of our manual.
[10 Mar 2020 11:03]
Michael Bausano
Thank you for your response. I have read the article again more closely and I really cannot find anything which would hit this behavior. Could you please point me to a specific paragraph which says it should work like that? My reference when I work with roles are following articles: - https://dev.mysql.com/doc/refman/8.0/en/role-names.html - https://dev.mysql.com/doc/refman/8.0/en/roles.html - https://dev.mysql.com/doc/refman/8.0/en/set-role.html > Privileges that the user has been granted directly (rather than through roles) remain unaffected by changes to the active roles. This sentence is not true as shown in my example. Also there's no mention about that granting all privileges on a database works differently for roles and for users. Rather it is being noted that grants for users and roles are interchangeable.
[10 Mar 2020 13:25]
MySQL Verification Team
Hi Mr. Bausano, Roles are working just in the way that they are designed. If you think that this is not explained sufficiently, I can verify this report as a documentation bug.
[10 Mar 2020 14:01]
Michael Bausano
Thank you for your quick response. It would be great if the documentation mentioned that granting ALL privileges for a role doesn't actually grant ALL privileges, but just some. It sounds like a bug to me but if that's by design, my personal opinion is that the refman should have an enumeration of privileges which are assigned to a role on granting ALL privileges. Just to be clear, I let me give you a brief example on what am I complaining about and what doesn't make sense to me: mysql> show grants for 'joe_role'; +------------------------------------------------------+ | Grants for joe_role@% | +------------------------------------------------------+ | GRANT USAGE ON *.* TO `joe_role`@`%` | | GRANT ALL PRIVILEGES ON `joe_db`.* TO `joe_role`@`%` | +------------------------------------------------------+ mysql> show grants for `joe`; +-------------------------------------------------+ | Grants for joe@% | +-------------------------------------------------+ | GRANT USAGE ON *.* TO `joe`@`%` | | GRANT ALL PRIVILEGES ON `joe_db`.* TO `joe`@`%` | | GRANT `joe_role`@`%` TO `joe`@`%` | +-------------------------------------------------+ --- and as `joe` user --- mysql> set role `joe_role`; Query OK, 0 rows affected (0.00 sec) mysql> show grants; +-------------------------------------------------+ | Grants for joe@% | +-------------------------------------------------+ | GRANT USAGE ON *.* TO `joe`@`%` | | GRANT ALL PRIVILEGES ON `joe_db`.* TO `joe`@`%` | | GRANT `joe_role`@`%` TO `joe`@`%` | +-------------------------------------------------+ mysql> create database `joe_db`; ERROR 1044 (42000): Access denied for user 'joe'@'%' to database 'joe_db' mysql> set role all except 'joe_role'; Query OK, 0 rows affected (0.00 sec) mysql> show grants; +-------------------------------------------------+ | Grants for joe@% | +-------------------------------------------------+ | GRANT USAGE ON *.* TO `joe`@`%` | | GRANT ALL PRIVILEGES ON `joe_db`.* TO `joe`@`%` | | GRANT `joe_role`@`%` TO `joe`@`%` | +-------------------------------------------------+ 3 rows in set (0.00 sec) mysql> create database `joe_db`; Query OK, 1 row affected (0.02 sec) `joe_role` with the **very same** grants as the user, when assigned to the user, prevents them from creating database. This doesn't happen with global grants on the user or the role, neither with SELECT privileges on database. Only CREATE privileges exhibit this behavior. If this is by design, the documentation would benefit from list of all privileges which exhibit the same behavior as the CREATE privilege to unexpected errors. Thank you for your time
[10 Mar 2020 14:14]
MySQL Verification Team
Hi Mr. Bausano, Thank you for your thorough description of the lack of the documentation on this issue. Verified as a documentation bug.
[13 Mar 2020 16:58]
Paul DuBois
Posted by developer: Reclassifying as server issue. Kristofer wants to discuss with his group whether this should be happening.
[16 Mar 2020 13:01]
MySQL Verification Team
Thank you , Paul. It is, indeed, very difficult to decide whether this is in accordance with both SQL standard and with our own design.
[16 Mar 2020 16:27]
Michael Bausano
Thank you for your care in investigating whether this is an expected behavior.
[17 Mar 2020 12:57]
MySQL Verification Team
Thank you Mr. Bausano, But this is, indeed, our job.
[25 Aug 2020 13:53]
Paul DuBois
Posted by developer: Fixed in 8.0.22. Database-level privileges inherited through a role were not handled properly for database names that contained wildcard characters.
[25 Aug 2020 13:57]
MySQL Verification Team
Thank you, Paul.