MySQL Bugs: #90760: with admin option is not working

Bug #90760	with admin option is not working
Submitted:	5 May 2018 5:56	Modified:	25 Oct 2018 6:08
Reporter:	chen chen (OCA)	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Security: Roles	Severity:	S3 (Non-critical)
Version:	8.0.11	OS:	Linux (7.4)
Assigned to:		CPU Architecture:	x86

Description:
MySQL Version: mysql-8.0.11-linux-glibc2.12-x86_64.tar.gz

i granted a role to a user with admin option,then i login with this user，but "Access denied" when i try to grant the role to another user. 

How to repeat:
mysql> create user u1@'192.168.244.10' identified by '123';
Query OK, 0 rows affected (0.04 sec)

mysql> create user u2@'192.168.244.20' identified by '123';
Query OK, 0 rows affected (0.03 sec)

mysql> create role r1;
Query OK, 0 rows affected (0.07 sec)

mysql> grant r1 to u1@'192.168.244.10' with admin option;
Query OK, 0 rows affected (0.10 sec)

then i login with u1,and try to grant r1 to u2

[root@node1 ~]# mysql8 -h 192.168.244.10 -uu1 -p123 -P3308 

mysql> show grants;
+-----------------------------------------------------------+
| Grants for u1@192.168.244.10                              |
+-----------------------------------------------------------+
| GRANT USAGE ON *.* TO `u1`@`192.168.244.10`               |
| GRANT `r1`@`%` TO `u1`@`192.168.244.10` WITH ADMIN OPTION |
+-----------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> grant r1 to u2@'192.168.244.20' with admin option;
ERROR 1227 (42000): Access denied; you need (at least one of) the WITH DMIN, ROLE_ADMIN, SUPER privilege(s) for this operation

In Official document，Here is the explaination for "WITH ADMIN OPTION":

If the GRANT statement includes the WITH ADMIN OPTION clause, each named user becomes able to grant the named roles to other users or roles, or revoke them from other users or roles. This includes the ability to use WITH ADMIN OPTION itself.

https://dev.mysql.com/doc/refman/8.0/en/grant.html.

I'm sorry,than i forgot the "ROLE_ADMIN" Privilege.

Here is the correct and final repeat step

create user u1@'192.168.244.10' identified by '123';

create user u2@'192.168.244.20' identified by '123';

create user u3@'192.168.244.30' identified by '123';

create role r1;

grant r1 to u1@'192.168.244.10' with admin option;

grant ROLE_ADMIN on *.* to u1@'192.168.244.10';

then i login with u1@'192.168.244.10'

[root@node1 data]# mysql8 -h 192.168.244.10 -uu1 -p123 -P3308

mysql> show grants;
+-----------------------------------------------------------+
| Grants for u1@192.168.244.10                              |
+-----------------------------------------------------------+
| GRANT USAGE ON *.* TO `u1`@`192.168.244.10`               |
| GRANT ROLE_ADMIN ON *.* TO `u1`@`192.168.244.10`          |
| GRANT `r1`@`%` TO `u1`@`192.168.244.10` WITH ADMIN OPTION |
+-----------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> grant `u1`@`192.168.244.10` to  u2@'192.168.244.20' with admin option;
Query OK, 0 rows affected (0.10 sec)

Until now,there is no problem,then i login with u2@'192.168.244.20'

[root@node2 bin]# ./mysql -h192.168.244.10 -uu2 -p123 -P3308
mysql> show grants;
+------------------------------------------------------------------------+
| Grants for u2@192.168.244.20                                           |
+------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `u2`@`192.168.244.20`                            |
| GRANT `u1`@`192.168.244.10` TO `u2`@`192.168.244.20` WITH ADMIN OPTION |
+------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show grants for `u2`@`192.168.244.20` using `u1`@`192.168.244.10`;
+---------------------------------------------------------------------------------+
| Grants for u2@192.168.244.20                                                    |
+---------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `u2`@`192.168.244.20`                                     |
| GRANT ROLE_ADMIN ON *.* TO `u2`@`192.168.244.20`                                |
| GRANT `r1`@`%`,`u1`@`192.168.244.10` TO `u2`@`192.168.244.20` WITH ADMIN OPTION |
+---------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

i tried to grant r1 to u3,but "Access denied"

mysql> grant `r1`@`%` to u3@'192.168.244.30';
ERROR 1227 (42000): Access denied; you need (at least one of) the WITH ADMIN, ROLE_ADMIN, SUPER privilege(s) for this operation

but now u2 did both have "ROLE_ADMIN" and "WITH ADMIN OPTION".

Looking forward for your reply.

Hi,

Thank you for your bug report.

Have you tried to create user WITH GRANT OPTION and see if it works ????

It is not working.
Here is the privilege of u2.

mysql> show grants for `u2`@`192.168.244.20` using `u1`@`192.168.244.10`;
+---------------------------------------------------------------------------------+
| Grants for u2@192.168.244.20                                                    |
+---------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `u2`@`192.168.244.20` WITH GRANT OPTION                   |
| GRANT ROLE_ADMIN ON *.* TO `u2`@`192.168.244.20` WITH GRANT OPTION              |
| GRANT `r1`@`%`,`u1`@`192.168.244.10` TO `u2`@`192.168.244.20` WITH ADMIN OPTION |
+---------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> grant `r1`@`%` to u3@'192.168.244.30';
ERROR 1227 (42000): Access denied; you need (at least one of) the WITH ADMIN, ROLE_ADMIN, SUPER privilege(s) for this operation

Hi,

We do not see from what you have described what is the current user, that is the user which has been approved the login to the server. Id est, what is the user as the MySQL recognises it.

Here is the complete description.

mysql> select user(),current_user();
+-------------------+-------------------+
| user()            | current_user()    |
+-------------------+-------------------+
| u2@192.168.244.20 | u2@192.168.244.20 |
+-------------------+-------------------+
1 row in set (0.00 sec)

mysql> show grants;
+------------------------------------------------------------------------+
| Grants for u2@192.168.244.20                                           |
+------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `u2`@`192.168.244.20`                            |
| GRANT `u1`@`192.168.244.10` TO `u2`@`192.168.244.20` WITH ADMIN OPTION |
+------------------------------------------------------------------------+
2 rows in set (0.01 sec)

mysql> show grants for `u2`@`192.168.244.20` using `u1`@`192.168.244.10`;
+---------------------------------------------------------------------------------+
| Grants for u2@192.168.244.20                                                    |
+---------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `u2`@`192.168.244.20` WITH GRANT OPTION                   |
| GRANT ROLE_ADMIN ON *.* TO `u2`@`192.168.244.20` WITH GRANT OPTION              |
| GRANT `r1`@`%`,`u1`@`192.168.244.10` TO `u2`@`192.168.244.20` WITH ADMIN OPTION |
+---------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> grant `r1`@`%` to u3@'192.168.244.30';
ERROR 1227 (42000): Access denied; you need (at least one of) the WITH ADMIN, ROLE_ADMIN, SUPER privilege(s) for this operation

Hi,

As the text says, I do not see a SUPER privilege ...

There is no need for the 'super' privilege.

you can see the error message,

ERROR 1227 (42000): Access denied; you need (at least one of) the WITH ADMIN, ROLE_ADMIN, SUPER privilege(s) for this operation.

"at least one of" but not "all of".

Actually,ROLE_ADMIN is one of the privileges to replace super.

besides,super is not recommended to use in MySQL 8.0.

if you grant super to user,a warning message occur.

Hi,

This could a bug in our documentation.

In order to proceed , we need to know whether granting works when the current user has SUPER privilege.

sorry for late reply,i tried to grant super privileges.

but it is not working.

Here is the result.

ysql> select user(),current_user();
+-------------------+-------------------+
| user()            | current_user()    |
+-------------------+-------------------+
| u2@192.168.244.20 | u2@192.168.244.20 |
+-------------------+-------------------+
1 row in set (0.14 sec)

mysql> show grants;
+------------------------------------------------------------------------+
| Grants for u2@192.168.244.20                                           |
+------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `u2`@`192.168.244.20`                            |
| GRANT `u1`@`192.168.244.10` TO `u2`@`192.168.244.20` WITH ADMIN OPTION |
+------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show grants for `u2`@`192.168.244.20` using `u1`@`192.168.244.10`;
+---------------------------------------------------------------------------------+
| Grants for u2@192.168.244.20                                                    |
+---------------------------------------------------------------------------------+
| GRANT SUPER ON *.* TO `u2`@`192.168.244.20` WITH GRANT OPTION                   |
| GRANT ROLE_ADMIN ON *.* TO `u2`@`192.168.244.20` WITH GRANT OPTION              |
| GRANT `r1`@`%`,`u1`@`192.168.244.10` TO `u2`@`192.168.244.20` WITH ADMIN OPTION |
+---------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> grant `r1`@`%` to u3@'192.168.244.30';
ERROR 1227 (42000): Access denied; you need (at least one of) the WITH ADMIN, ROLE_ADMIN, SUPER privilege(s) for this operation

Hi,

This looks like a bug.

Verified.

Posted by developer - Kristofer Pettersson

Original example is flawed because it doesn't activate the role which carries
the privileges. This works:
 CREATE USER u1@localhost IDENTIFIED BY '123';
 CREATE USER u2@localhost IDENTIFIED BY '123';
 CREATE ROLE r1;
 GRANT r1 TO u1@localhost WITH ADMIN OPTION;
 connect(conn, localhost, u1,123,test);
 SELECT CURRENT_USER();
 SHOW GRANTS;
 SET ROLE r1;
 GRANT r1 TO u2@localhost WITH ADMIN OPTION;
 connection default;
 SHOW GRANTS FOR u2@localhost;

note SET ROLE r1

Last example seems to share the same issue. The roles aren't activated. To
activate per default, use "SET DEFAULT ROLE".

This is described in our manual in the chapter:

13.7.1.11 SET ROLE Syntax