[MDEV-18554] User with SELECT privilege on mysql.proc can see a body of the procedure/function it does not have any grant on Created: 2019-02-12  Updated: 2020-08-25  Resolved: 2019-05-13

Status: Closed
Project: MariaDB Server
Component/s: Authentication and Privilege System, Documentation, Information Schema
Affects Version/s: 10.3.7, 10.3.13
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Valerii Kravchuk Assignee: Kenneth Dyer (Inactive)
Resolution: Fixed Votes: 0
Labels: 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.

 Comments   
Comment by Kenneth Dyer (Inactive) [ 2019-03-08 ]

Reworked the cited text to include the SELECT privilege. Also added to the examples cases where the user doesn't have the relevant privileges.

Comment by Kenneth Dyer (Inactive) [ 2019-04-23 ]

sanja Sending to you. Text updated, please assign to someone to review that it's correct. Also, Julien's note above.

Comment by Oleksandr Byelkin [ 2019-04-23 ]

I think it was some misunderstanding during doc writing, because:

sanja@SanjaLaptop:~/maria/git/10.4$ ./client/mysql --user=u1 --password=u1 --port=16000 --host=127.0.0.1 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
 
Your MariaDB connection id is 4
 
Server version: 10.4.5-MariaDB-debug-log 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 [(none)]> select * from `mysql`.`proc`;
 
+-----+-----------------+-----------+-----------------+----------+-----------------+------------------+---------------+----------------------+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+---------------------+---------------------+-------------------------------------------------------------------------------------------+---------+----------------------+----------------------+-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+
 
| db  | name            | type      | specific_name   | language | sql_data_access | is_deterministic | security_type | param_list           | returns | body                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             | definer        | created             | modified            | sql_mode                                                                                  | comment | character_set_client | collation_connection | db_collation      | body_utf8                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        | aggregate |
 
+-----+-----------------+-----------+-----------------+----------+-----------------+------------------+---------------+----------------------+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+---------------------+---------------------+-------------------------------------------------------------------------------------------+---------+----------------------+----------------------+-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+
 
| mtr | check_warnings  | PROCEDURE | check_warnings  | SQL      | CONTAINS_SQL    | NO               | DEFINER       | OUT result INT       |         | BEGIN   DECLARE `pos` bigint unsigned;    SET SQL_LOG_BIN=0, SQL_SAFE_UPDATES=0;    UPDATE error_log el, global_suppressions gs     SET suspicious=0       WHERE el.suspicious=1 AND el.line REGEXP gs.pattern;    UPDATE error_log el, test_suppressions ts     SET suspicious=0       WHERE el.suspicious=1 AND el.line REGEXP ts.pattern;    SELECT COUNT(*) INTO @num_warnings FROM error_log     WHERE suspicious=1;    IF @num_warnings > 0 THEN     SELECT line         FROM error_log WHERE suspicious=1;     SELECT 2 INTO result;   ELSE     SELECT 0 INTO RESULT;   END IF;    TRUNCATE test_suppressions;   DROP TABLE error_log;  END                                                                                                                                                                                                                                                                                                                                                                                                               | root@localhost | 2019-04-23 21:07:31 | 2019-04-23 21:07:31 |                                                                                           |         | utf8                 | utf8_general_ci      | latin1_swedish_ci | BEGIN   DECLARE `pos` bigint unsigned;    SET SQL_LOG_BIN=0, SQL_SAFE_UPDATES=0;    UPDATE error_log el, global_suppressions gs     SET suspicious=0       WHERE el.suspicious=1 AND el.line REGEXP gs.pattern;    UPDATE error_log el, test_suppressions ts     SET suspicious=0       WHERE el.suspicious=1 AND el.line REGEXP ts.pattern;    SELECT COUNT(*) INTO @num_warnings FROM error_log     WHERE suspicious=1;    IF @num_warnings > 0 THEN     SELECT line         FROM error_log WHERE suspicious=1;     SELECT 2 INTO result;   ELSE     SELECT 0 INTO RESULT;   END IF;    TRUNCATE test_suppressions;   DROP TABLE error_log;  END                                                                                                                                                                                                                                                                                                                                                                                                               | NONE      |
 
| mtr | add_suppression | PROCEDURE | add_suppression | SQL      | CONTAINS_SQL    | NO               | DEFINER       | pattern VARCHAR(255) |         | BEGIN   INSERT INTO test_suppressions (pattern) VALUES (pattern);   FLUSH NO_WRITE_TO_BINLOG TABLE test_suppressions; END                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        | root@localhost | 2019-04-23 21:07:31 | 2019-04-23 21:07:31 |                                                                                           |         | utf8                 | utf8_general_ci      | latin1_swedish_ci | BEGIN   INSERT INTO test_suppressions (pattern) VALUES (pattern);   FLUSH NO_WRITE_TO_BINLOG TABLE test_suppressions; END                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        | NONE      |
 
| mtr | check_testcase  | PROCEDURE | check_testcase  | SQL      | CONTAINS_SQL    | NO               | DEFINER       |                      |         | BEGIN    SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES     WHERE variable_name NOT IN ('timestamp')      AND variable_name not like "Last_IO_Err*"      AND variable_name != 'INNODB_IBUF_MAX_SIZE'      AND variable_name != 'INNODB_USE_NATIVE_AIO'      AND variable_name != 'INNODB_BUFFER_POOL_LOAD_AT_STARTUP'      AND variable_name not like 'GTID%POS'      AND variable_name != 'GTID_BINLOG_STATE'    ORDER BY variable_name;    SELECT * FROM INFORMATION_SCHEMA.SCHEMATA ORDER BY BINARY SCHEMA_NAME;    SELECT * FROM INFORMATION_SCHEMA.SCHEMATA     WHERE SCHEMA_NAME NOT IN ('mtr_wsrep_notify', 'wsrep_schema')     ORDER BY BINARY SCHEMA_NAME;    SELECT table_name AS tables_in_test FROM INFORMATION_SCHEMA.TABLES     WHERE table_schema='test';    SELECT CONCAT(table_schema, '.', table_name) AS tables_in_mysql     FROM INFORMATION_SCHEMA.TABLES       WHERE table_schema='mysql'         ORDER BY tables_in_mysql;   SELECT CONCAT(table_schema, '.', table_name) AS columns_in_mysql,   	 column_name, ordinal_position, column_default, is_nullable,          data_type, character_maximum_length, character_octet_length,          numeric_precision, numeric_scale, character_set_name,          collation_name, column_type, column_key, extra, column_comment     FROM INFORMATION_SCHEMA.COLUMNS       WHERE table_schema='mysql'         ORDER BY columns_in_mysql;    SELECT * FROM INFORMATION_SCHEMA.EVENTS;   SELECT * FROM INFORMATION_SCHEMA.TRIGGERS          WHERE TRIGGER_NAME NOT IN ('gs_insert', 'ts_insert');   SELECT * FROM INFORMATION_SCHEMA.ROUTINES;    SHOW STATUS LIKE 'slave_open_temp_tables';    checksum table     mysql.columns_priv,     mysql.db,     mysql.func,     mysql.help_category,     mysql.help_keyword,     mysql.help_relation,     mysql.plugin,     mysql.proc,     mysql.procs_priv,     mysql.roles_mapping,     mysql.tables_priv,     mysql.time_zone,     mysql.time_zone_leap_second,     mysql.time_zone_name,     mysql.time_zone_transition,     mysql.time_zone_transition_type,     mysql.global_priv;    SELECT * FROM INFORMATION_SCHEMA.PLUGINS;    select * from information_schema.session_variables     where variable_name = 'debug_sync';  END | root@localhost | 2019-04-23 21:07:31 | 2019-04-23 21:07:31 |                                                                                           |         | utf8                 | utf8_general_ci      | latin1_swedish_ci | BEGIN    SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES     WHERE variable_name NOT IN ('timestamp')      AND variable_name not like "Last_IO_Err*"      AND variable_name != 'INNODB_IBUF_MAX_SIZE'      AND variable_name != 'INNODB_USE_NATIVE_AIO'      AND variable_name != 'INNODB_BUFFER_POOL_LOAD_AT_STARTUP'      AND variable_name not like 'GTID%POS'      AND variable_name != 'GTID_BINLOG_STATE'    ORDER BY variable_name;    SELECT * FROM INFORMATION_SCHEMA.SCHEMATA ORDER BY BINARY SCHEMA_NAME;    SELECT * FROM INFORMATION_SCHEMA.SCHEMATA     WHERE SCHEMA_NAME NOT IN ('mtr_wsrep_notify', 'wsrep_schema')     ORDER BY BINARY SCHEMA_NAME;    SELECT table_name AS tables_in_test FROM INFORMATION_SCHEMA.TABLES     WHERE table_schema='test';    SELECT CONCAT(table_schema, '.', table_name) AS tables_in_mysql     FROM INFORMATION_SCHEMA.TABLES       WHERE table_schema='mysql'         ORDER BY tables_in_mysql;   SELECT CONCAT(table_schema, '.', table_name) AS columns_in_mysql,   	 column_name, ordinal_position, column_default, is_nullable,          data_type, character_maximum_length, character_octet_length,          numeric_precision, numeric_scale, character_set_name,          collation_name, column_type, column_key, extra, column_comment     FROM INFORMATION_SCHEMA.COLUMNS       WHERE table_schema='mysql'         ORDER BY columns_in_mysql;    SELECT * FROM INFORMATION_SCHEMA.EVENTS;   SELECT * FROM INFORMATION_SCHEMA.TRIGGERS          WHERE TRIGGER_NAME NOT IN ('gs_insert', 'ts_insert');   SELECT * FROM INFORMATION_SCHEMA.ROUTINES;    SHOW STATUS LIKE 'slave_open_temp_tables';    checksum table     mysql.columns_priv,     mysql.db,     mysql.func,     mysql.help_category,     mysql.help_keyword,     mysql.help_relation,     mysql.plugin,     mysql.proc,     mysql.procs_priv,     mysql.roles_mapping,     mysql.tables_priv,     mysql.time_zone,     mysql.time_zone_leap_second,     mysql.time_zone_name,     mysql.time_zone_transition,     mysql.time_zone_transition_type,     mysql.global_priv;    SELECT * FROM INFORMATION_SCHEMA.PLUGINS;    select * from information_schema.session_variables     where variable_name = 'debug_sync';  END | NONE      |
 
| db2 | prc1            | PROCEDURE | prc1            | SQL      | CONTAINS_SQL    | NO               | DEFINER       |                      |         | select 1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         | root@localhost | 2019-04-23 21:10:14 | 2019-04-23 21:10:14 | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |         | utf8                 | utf8_general_ci      | latin1_swedish_ci | select 1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         | NONE      |
 
+-----+-----------------+-----------+-----------------+----------+-----------------+------------------+---------------+----------------------+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+---------------------+---------------------+-------------------------------------------------------------------------------------------+---------+----------------------+----------------------+-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+
 
4 rows in set (0.002 sec)
 
 
 
MariaDB [(none)]>

i.e. with select access you can get the same and even more.

I meant that there is no sens to hide something from user with select right on `mysql`.`proc`. (I'd changed the docs)

Comment by Oleksandr Byelkin [ 2019-04-23 ]

I checked the documentation, looks good (especially as a warning).

Comment by Oleksandr Byelkin [ 2019-04-23 ]

I also checked source code, there is explicit allowance to see the text if user has SELECT access to the `mysql`.`proc`, so it is not a bug (which is logical because of my first comment):

bool check_show_routine_access(THD *thd, sp_head *sp, bool *full_access)
 
{
 
  TABLE_LIST tables;
 
  bzero((char*) &tables,sizeof(tables));
 
  tables.db= MYSQL_SCHEMA_NAME;
 
  tables.table_name= MYSQL_PROC_NAME;
 
  tables.alias= MYSQL_PROC_NAME;
 
 
 
  *full_access= ((!check_table_access(thd, SELECT_ACL, &tables, FALSE,
 
                                     1, TRUE) &&
 
                  (tables.grant.privilege & SELECT_ACL) != 0) ||
 
                 /* Check if user owns the routine. */
 
                 (!strcmp(sp->m_definer.user.str,
 
                          thd->security_ctx->priv_user) &&
 
                  !strcmp(sp->m_definer.host.str,
 
                          thd->security_ctx->priv_host)) ||
 
                 /* Check if current role or any of the sub-granted roles
 
                    own the routine. */
 
                 (sp->m_definer.host.length == 0 &&
 
                  (!strcmp(sp->m_definer.user.str,
 
                           thd->security_ctx->priv_role) ||
 
                   check_role_is_granted(thd->security_ctx->priv_role, NULL,
 
                                         sp->m_definer.user.str))));

Generated at Thu Feb 08 08:45:00 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.