| Bug #93263 | DROP ROLE username should be rejected | ||
|---|---|---|---|
| Submitted: | 21 Nov 2018 5:17 | Modified: | 10 Jan 2019 16:41 |
| Reporter: | Tsubasa Tanaka (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Security: Roles | Severity: | S3 (Non-critical) |
| Version: | 8.0.13 | OS: | CentOS (7.5) |
| Assigned to: | CPU Architecture: | x86 | |
[21 Nov 2018 5:17]
Tsubasa Tanaka
[21 Nov 2018 6:15]
MySQL Verification Team
Hello Tanaka-San, Thank you for the report ad feedback. Discussed internally with our sec SPOC and confirmed that CREATE USER/CREATE ROLE and DROP USER/DROP ROLE are similar. Hence concluded that this is more of a documentation issue. Also, confirmed that this is something not new but exists since the day it was introduced. Leaving category as is for now. regards, Umesh
[21 Nov 2018 6:22]
MySQL Verification Team
## ## [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-8.0.13: bin/mysql -uroot -S /tmp/mysql_ushastry.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 8.0.13 MySQL Community Server - GPL Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> CREATE USER this_is_user_account IDENTIFIED WITH mysql_native_password BY 'account'; Query OK, 0 rows affected (0.04 sec) mysql> CREATE USER i_has_only_drop_role_priv; Query OK, 0 rows affected (0.03 sec) mysql> GRANT DROP ROLE ON *.* TO i_has_only_drop_role_priv; Query OK, 0 rows affected (0.06 sec) mysql> show grants for i_has_only_drop_role_priv; +-----------------------------------------------------------+ | Grants for i_has_only_drop_role_priv@% | +-----------------------------------------------------------+ | GRANT DROP ROLE ON *.* TO `i_has_only_drop_role_priv`@`%` | +-----------------------------------------------------------+ 1 row in set (0.00 sec) mysql> show grants for this_is_user_account; +--------------------------------------------------+ | Grants for this_is_user_account@% | +--------------------------------------------------+ | GRANT USAGE ON *.* TO `this_is_user_account`@`%` | +--------------------------------------------------+ 1 row in set (0.00 sec) mysql> \q Bye [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-8.0.13: bin/mysql -ui_has_only_drop_role_priv -h127.0.0.1 --port=9999 --protocol=tcp Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.13 MySQL Community Server - GPL Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show grants; +-----------------------------------------------------------+ | Grants for i_has_only_drop_role_priv@% | +-----------------------------------------------------------+ | GRANT DROP ROLE ON *.* TO `i_has_only_drop_role_priv`@`%` | +-----------------------------------------------------------+ 1 row in set (0.00 sec) mysql> DROP ROLE this_is_user_account; Query OK, 0 rows affected (0.04 sec) mysql> \q Bye - confirm that user 'this_is_user_account' doesn't exists [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-8.0.13: bin/mysql -uroot -S /tmp/mysql_ushastry.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.0.13 MySQL Community Server - GPL Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show grants for this_is_user_account; ERROR 1141 (42000): There is no such grant defined for user 'this_is_user_account' on host '%' mysql>
[21 Nov 2018 9:49]
Peter Laursen
See also: https://bugs.mysql.com/bug.php?id=84244 -- Peter -- not a MySQL/Oracle person
[26 Nov 2018 13:05]
Georgi Kodinov
Posted by developer: CREATE ROLE is a bit of a lesser privilege compared to CREATE USER since CREATE ROLE allows only creation of user accounts with login disabled. To enable this you need a CREATE USER privilege (for ALTER USER ...) or UPDATE on mysql.*. But I admit DROP ROLE allows one to execute DROP USER (i.e. drop user accounts with login enabled). And it should be checking if login is enabled or not.
[10 Jan 2019 16:41]
Paul DuBois
Posted by developer: Fixed in 8.0.15. Previously, users who had the DROP ROLE privilege could use the DROP ROLE statement to drop locked or unlocked accounts. Now, users who have the DROP ROLE privilege can use DROP ROLE only to drop accounts that are locked (unlocked accounts are presumably user accounts used to log in to the server and not just as roles). Users who have the CREATE USER privilege can use DROP ROLE to drop accounts that are locked or unlocked.
