Bug #93263 DROP ROLE username should be rejected
Submitted: 21 Nov 2018 5:17 Modified: 10 Jan 16:41
Reporter: tsubasa tanaka (OCA) Email Updates:
Status: Closed Impact on me:
None 
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
Description:
MySQL 8.0.13 does not distinct user-account and role.

SQLCOM_DROP_USER is checked "DELETE_ACL in mysql schema" *AND* "CREATE_USER_ACL" but

https://github.com/mysql/mysql-server/blob/mysql-8.0.13/sql/sql_parse.cc#L3608-L3617

Sql_cmd_drop_role::execute checks "DROP_ROLE_ACL" *OR* "CREATE_USER_ACL"

https://github.com/mysql/mysql-server/blob/mysql-8.0.13/sql/sql_admin.cc#L1728-L1736

Both functions call mysql_drop_user after privileges checking, they do not distinct
 role and user.

And then, user which have DROP_ROLE_Priv will be able to DROP USER without any other privileges.

How to repeat:
Operation by root@localhost.

mysql80 29> CREATE USER this_is_user_account IDENTIFIED WITH mysql_native_password BY 'account';
Query OK, 0 rows affected (0.06 sec)

mysql80 29> CREATE USER i_has_only_drop_role_priv;
Query OK, 0 rows affected (0.04 sec)

mysql80 29> GRANT DROP ROLE ON *.* TO i_has_only_drop_role_priv;
Query OK, 0 rows affected (0.08 sec)

By i_has_only_drop_role_priv User.

mysql80 30> 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)

mysql80 30> DROP ROLE this_is_user_account;
Query OK, 0 rows affected (0.05 sec)
[21 Nov 2018 6:15] Umesh Shastry
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] Umesh Shastry
##
##
[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 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.