Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.3.7, 10.3.13
-
None
Description
This KB article, https://mariadb.com/kb/en/library/show-create-procedure/, says:
"Both statements require that you be the owner of the routine or have SELECT access to the mysql.proc table. If you do not have privileges for the routine itself, the value displayed for the Create Procedure or Create Function field will be NULL."
It seems a user with just USAGE on . and SELECT ON `mysql`.`proc` privileges can perfectly see the body of any stored procedure or function, to the contrary of what that documentation says. Consider the following simple test:
[openxs@fc23 maria10.3]$ bin/mysql -uroot --socket=/tmp/mariadb.sock test
|
Reading table information for completion of table and column names
|
You can turn off this feature to get a quicker startup with -A
|
|
|
Welcome to the MariaDB monitor. Commands end with ; or \g.
|
Your MariaDB connection id is 8
|
Server version: 10.3.13-MariaDB Source distribution
|
|
|
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
|
|
|
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
|
|
|
MariaDB [test]> select current_user();
|
+----------------+
|
| current_user() |
|
+----------------+
|
| root@localhost |
|
+----------------+
|
1 row in set (0.000 sec)
|
|
|
MariaDB [test]> create database db2;
|
Query OK, 1 row affected (0.026 sec)
|
|
|
MariaDB [test]> use db2;
|
Database changed
|
MariaDB [db2]> create procedure prc1() select 1;
|
Query OK, 0 rows affected (0.026 sec)
|
|
|
MariaDB [db2]> call prc1();
|
+---+
|
| 1 |
|
+---+
|
| 1 |
|
+---+
|
1 row in set (0.000 sec)
|
|
|
Query OK, 0 rows affected (0.000 sec)
|
|
|
MariaDB [db2]> select user,host from mysql.user;
|
+------+-----------+
|
| user | host |
|
+------+-----------+
|
| root | 127.0.0.1 |
|
| root | ::1 |
|
| | fc23 |
|
| root | fc23 |
|
| | localhost |
|
| root | localhost |
|
+------+-----------+
|
6 rows in set (0.000 sec)
|
|
|
MariaDB [db2]> create user u1@localhost identified by 'u1';
|
Query OK, 0 rows affected (0.000 sec)
|
|
|
MariaDB [db2]> show grants for u1@localhost;
|
+-----------------------------------------------------------------------------------------------------------+
|
| Grants for u1@localhost |
|
+-----------------------------------------------------------------------------------------------------------+
|
| GRANT USAGE ON *.* TO 'u1'@'localhost' IDENTIFIED BY PASSWORD '*556BEF296211C2AF58F53DA3EDDD0A3371B6ECD5' |
|
+-----------------------------------------------------------------------------------------------------------+
|
1 row in set (0.000 sec)
|
When we connect as u1@localhost we obviously can not execute and can now SHOW the procedure prc1() we've just created (as root) in the new db2 database:
[openxs@fc23 maria10.3]$ bin/mysql -uu1 -pu1 --socket=/tmp/mariadb.sock test
|
Reading table information for completion of table and column names
|
You can turn off this feature to get a quicker startup with -A
|
|
|
Welcome to the MariaDB monitor. Commands end with ; or \g.
|
Your MariaDB connection id is 10
|
Server version: 10.3.13-MariaDB Source distribution
|
|
|
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
|
|
|
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
|
|
|
MariaDB [test]> select current_user();
|
+----------------+
|
| current_user() |
|
+----------------+
|
| u1@localhost |
|
+----------------+
|
1 row in set (0.000 sec)
|
|
|
MariaDB [test]> show grants;
|
+-----------------------------------------------------------------------------------------------------------+
|
| Grants for u1@localhost |
|
+-----------------------------------------------------------------------------------------------------------+
|
| GRANT USAGE ON *.* TO 'u1'@'localhost' IDENTIFIED BY PASSWORD '*556BEF296211C2AF58F53DA3EDDD0A3371B6ECD5' |
|
+-----------------------------------------------------------------------------------------------------------+
|
1 row in set (0.000 sec)
|
|
|
MariaDB [test]> show create procedure db2.prc1\G
|
ERROR 1305 (42000): PROCEDURE prc1 does not exist
|
MariaDB [test]> call db2.prc1();
|
ERROR 1370 (42000): execute command denied to user 'u1'@'localhost' for routine 'db2.prc1'
|
MariaDB [test]> exit
|
Bye
|
But if we GRANT SELECT on mysql.proc we can see the body, even though we have no privilege to execute the procedure and no privilege at all on anything db2.*:
[openxs@fc23 maria10.3]$ bin/mysql -uroot --socket=/tmp/mariadb.sock -e'grant select on mysql.proc to u1@localhost'
|
[openxs@fc23 maria10.3]$ bin/mysql -uu1 -pu1 --socket=/tmp/mariadb.sock test Reading table information for completion of table and column names
|
You can turn off this feature to get a quicker startup with -A
|
|
|
Welcome to the MariaDB monitor. Commands end with ; or \g.
|
Your MariaDB connection id is 12
|
Server version: 10.3.13-MariaDB Source distribution
|
|
|
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
|
|
|
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
|
|
|
MariaDB [test]> show grants;
|
+-----------------------------------------------------------------------------------------------------------+
|
| Grants for u1@localhost |
|
+-----------------------------------------------------------------------------------------------------------+
|
| GRANT USAGE ON *.* TO 'u1'@'localhost' IDENTIFIED BY PASSWORD '*556BEF296211C2AF58F53DA3EDDD0A3371B6ECD5' |
|
| GRANT SELECT ON `mysql`.`proc` TO 'u1'@'localhost' |
|
+-----------------------------------------------------------------------------------------------------------+
|
2 rows in set (0.000 sec)
|
|
|
MariaDB [test]> call db2.prc1();
|
ERROR 1370 (42000): execute command denied to user 'u1'@'localhost' for routine 'db2.prc1'
|
MariaDB [test]> show create procedure db2.prc1\G
|
*************************** 1. row ***************************
|
Procedure: prc1
|
sql_mode: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
|
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `prc1`()
|
select 1
|
character_set_client: utf8
|
collation_connection: utf8_general_ci
|
Database Collation: latin1_swedish_ci
|
1 row in set (0.000 sec)
|
This is against the statement in our KB (or MySQL manual for that matter).
As a side note, Percona Server 5.7.25-28 is affected in the same way.