Bug #98570 Function privileges inherited as procedure privileges
Submitted: 12 Feb 2020 15:50 Modified: 1 Jun 2020 17:33
Reporter: Juan Ferrer Toribio Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Security: Privileges Severity:S3 (Non-critical)
Version:8.0.19-1debian9 OS:Debian
Assigned to: CPU Architecture:x86
Tags: role function privileges inherit

[12 Feb 2020 15:50] Juan Ferrer Toribio
Description:
When I assign function execution privileges to an user and subsequently (using the new mysql8 roles functionality) I assign that user's privileges to another, the latter inherits the function execution privileges on as routine execution privileges.

How to repeat:
-- Creating environment to reproduce the bug:

CREATE USER 'test1'@'%' IDENTIFIED BY '1234';
CREATE USER 'test2'@'%' IDENTIFIED BY '1234';

CREATE SCHEMA test;

DELIMITER $$
CREATE FUNCTION `test`.`testFn`() RETURNS INTEGER
BEGIN
	RETURN 1;
END$$
DELIMITER ;

GRANT 'test1' TO 'test2';
GRANT EXECUTE ON FUNCTION `test`.`testFn` TO 'test1'@'%';
FLUSH PRIVILEGES;

-- Reproducing the error:

-- Logged in as test1

MySQL [(none)]> SELECT test.testFn();
+---------------+
| test.testFn() |
+---------------+
|             1 |
+---------------+
1 row in set (0.001 sec)

MySQL [(none)]> SHOW GRANTS;
+----------------------------------------------------------+
| Grants for test1@%                                       |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO `test1`@`%`                        |
| GRANT EXECUTE ON FUNCTION `test`.`testfn` TO `test1`@`%` |
+----------------------------------------------------------+
2 rows in set (0.001 sec)

-- Logged in as test2

MySQL [(none)]> SELECT test.testFn();
ERROR 1370 (42000): execute command denied to user 'test2'@'%' for routine 'test.testFn'
MySQL [(none)]> SHOW GRANTS;
+-----------------------------------------------------------+
| Grants for test2@%                                        |
+-----------------------------------------------------------+
| GRANT USAGE ON *.* TO `test2`@`%`                         |
| GRANT EXECUTE ON PROCEDURE `test`.`testfn` TO `test2`@`%` | <-  *BUG HERE*
| GRANT `test1`@`%` TO `test2`@`%`                          |
+-----------------------------------------------------------+
3 rows in set (0.001 sec)

Suggested fix:
Function privileges should be inherited as function privileges, not procedure.
[13 Feb 2020 13:42] MySQL Verification Team
Hi Mr. Toribio,

I can't repeat your behaviour.

This is what I get:

mysql> show grants for 'test1'@'%';
+----------------------------------------------------------+
| Grants for test1@%                                       |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO `test1`@`%`                        |
| GRANT EXECUTE ON FUNCTION `test`.`testfn` TO `test1`@`%` |
+----------------------------------------------------------+

mysql> show grants for 'test2'@'%';
+-----------------------------------+
| Grants for test2@%                |
+-----------------------------------+
| GRANT USAGE ON *.* TO `test2`@`%` |
| GRANT `test1`@`%` TO `test2`@`%`  |
+-----------------------------------+
[13 Feb 2020 14:07] Juan Ferrer Toribio
Thanks for your reply Sinisa.

To accurately repeat my behavior you have to be logged  with the user itself, and use the "SHOW GRANTS" command without the "FOR [user]" (see my comments "Logged in as test1" and "Logged in as test2").
[13 Feb 2020 14:18] Juan Ferrer Toribio
In addition, the main problem isn't that privileges aren't correctly displayed (which is also a bug), it's that MySQL doesn't allow the inheriting user to execute the function.
[13 Feb 2020 14:18] MySQL Verification Team
Hi Toribio,

You are quite right.

So I did and got:

mysql> show grants;
+-------------------------------------------------+
| Grants for test2@%                              |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO `test2`@`%`               |
| GRANT `test1`@`%` TO `test2`@`%`                |
+-------------------------------------------------+
[13 Feb 2020 14:34] Juan Ferrer Toribio
Sorry I forgot to say that you need to have this option enabled in configuration file:

[mysql]
activate_all_roles_on_login = ON
[13 Feb 2020 14:54] Juan Ferrer Toribio
[mysqld]*
[13 Feb 2020 15:35] MySQL Verification Team
Hi Mr. Toribio,

In future, please provide all relevant info. Simply, we do not all use the same options.

I repeated the behaviour that you are reporting:

Logged as user test1:

mysql> select testFn();
+----------+
| testFn() |
+----------+
|        1 |
+----------+
1 row in set (0.01 sec)

mysql> show grants;
+----------------------------------------------------------+
| Grants for test1@%                                       |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO `test1`@`%`                        |
| GRANT EXECUTE ON FUNCTION `test`.`testfn` TO `test1`@`%` |
+----------------------------------------------------------+
2 rows in set (0.00 sec)

Logged as test2:

mysql> select testFn();
ERROR 1370 (42000): execute command denied to user 'test2'@'%' for routine 'test.testFn'
mysql> show grants;
+-----------------------------------------------------------+
| Grants for test2@%                                        |
+-----------------------------------------------------------+
| GRANT USAGE ON *.* TO `test2`@`%`                         |
| GRANT EXECUTE ON PROCEDURE `test`.`testfn` TO `test2`@`%` |
| GRANT `test1`@`%` TO `test2`@`%`                          |
+-----------------------------------------------------------+
3 rows in set (0.00 sec)

Verified as reported.
[28 Feb 2020 5:32] MySQL Verification Team
Bug #98767 marked as duplicate of this one
[28 Feb 2020 12:49] MySQL Verification Team
Thank you, Umesh.
[19 Mar 2020 14:17] Georgi Kodinov
Posted by developer:
 
FWIW I can repeat it using an 8.0.19 x64 windows zip community server:
C:\Users\gkodinov\dev\mysql-8.0.19-winx64>bin\mysql -u test1 -p
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.19 MySQL Community Server - GPL

Copyright (c) 2000, 2020, 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> SELECT test.testFn();
+---------------+
| test.testFn() |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)

mysql> SHOW GRANTS;
+----------------------------------------------------------+
| Grants for test1@%                                       |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO `test1`@`%`                        |
| GRANT EXECUTE ON FUNCTION `test`.`testfn` TO `test1`@`%` |
+----------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> exit;
Bye

C:\Users\gkodinov\dev\mysql-8.0.19-winx64>bin\mysql -u test2 -p
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.19 MySQL Community Server - GPL

Copyright (c) 2000, 2020, 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> SELECT test.testFn();
ERROR 1370 (42000): execute command denied to user 'test2'@'%' for routine 'test.testFn'
mysql> SHOW GRANTS;
+-----------------------------------------------------------+
| Grants for test2@%                                        |
+-----------------------------------------------------------+
| GRANT USAGE ON *.* TO `test2`@`%`                         |
| GRANT EXECUTE ON PROCEDURE `test`.`testfn` TO `test2`@`%` |
| GRANT `test1`@`%` TO `test2`@`%`                          |
+-----------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.19    |
+-----------+
1 row in set (0.00 sec)
[1 Jun 2020 17:33] Paul DuBois
Posted by developer:
 
Fixed in 8.0.21.

SHOW GRANTS could display function privileges as procedure
privileges.
[2 Jun 2020 11:55] MySQL Verification Team
Thank you, Paul.