MySQL Bugs: #98570: Function privileges inherited as procedure privileges

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

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.

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`@`%`  |
+-----------------------------------+

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").

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.

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`@`%`                |
+-------------------------------------------------+

Sorry I forgot to say that you need to have this option enabled in configuration file:

[mysql]
activate_all_roles_on_login = ON

[mysqld]*

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.

Bug #98767 marked as duplicate of this one

Thank you, Umesh.

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)

Posted by developer:
 
Fixed in 8.0.21.

SHOW GRANTS could display function privileges as procedure
privileges.

Thank you, Paul.