MySQL Bugs: #11577: 'show procedure/function status' doesn't work for user with limited access

Bug #11577	'show procedure/function status' doesn't work for user with limited access
Submitted:	25 Jun 2005 22:00	Modified:	29 Jul 2005 18:11
Reporter:	Markus Popp	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.7-beta	OS:	Windows (Windows XP, SuSE Linux 9)
Assigned to:	Sergei Glukhov	CPU Architecture:	Any

Description:
I'm not sure whether it's a bug or some intended behavior that I don't understand.

Given, a user has only access to a specific database and defines a SP or UDF, he can't call 'show procedure/function status'. This command returns the error message

#1142 - SELECT command denied to user 'xxx'@'localhost' for table 'proc'

because the user doesn't have access to the mysql database. If I give the user permission to run SELECT commands at the table mysql.proc, he can see all SPs and UDFs, also from other users (which is not so good).

However, using the query

select * from information_schema.routines

the user gets the proper information for his (and only his) SPs und UDFs.

Also the commands

show create procedure/function procedure_or_function_name

work the way I expected and show the creation command.

How to repeat:
Create a user and limit his permissions to one database. Create a stored procedure and/or user defined function being logged in as this user. Then run the command 'show procedure status' and/or 'show function status'.

Suggested fix:
I would find it better if MySQL would deliver the data for 'show procedure/function status' the same way that it is available in the INFORMATION_SCHEMA database. In my opinion it should always return the SPs and UDFs that the user who's logged in may execute.

c:\mysql\bin>mysqladmin -uroot create db0

c:\mysql\bin>mysql -uroot -e"grant all on db0.* to 'buguser'@'localhost' identified by 'hispass'"

c:\mysql\bin>mysql -ubuguser -p db0
Enter password: *******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.9-beta-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> delimiter //
mysql> create procedure bugsp()
    -> begin
    -> select 1+1;
    -> end//
Query OK, 0 rows affected (0.03 sec)

mysql> delimiter ;
mysql> call bugsp();
+-----+
| 1+1 |
+-----+
|   2 |
+-----+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

mysql> show procedure status like "bugsp";
ERROR 1142 (42000): SELECT command denied to user 'buguser'@'localhost' for table 'proc'

mysql> select * from information_schema.routines\G
*************************** 1. row ***************************
     SPECIFIC_NAME: bugsp
   ROUTINE_CATALOG: NULL
    ROUTINE_SCHEMA: db0
      ROUTINE_NAME: bugsp
      ROUTINE_TYPE: PROCEDURE
    DTD_IDENTIFIER: NULL
      ROUTINE_BODY: SQL
ROUTINE_DEFINITION: begin
select 1+1;
end
     EXTERNAL_NAME: NULL
 EXTERNAL_LANGUAGE: NULL
   PARAMETER_STYLE: SQL
  IS_DETERMINISTIC: NO
   SQL_DATA_ACCESS: CONTAINS SQL
          SQL_PATH: NULL
     SECURITY_TYPE: DEFINER
           CREATED: 2005-06-25 21:13:25
      LAST_ALTERED: 2005-06-25 21:13:25
          SQL_MODE:
   ROUTINE_COMMENT:
           DEFINER: buguser@localhost
1 row in set (0.00 sec)

mysql>

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/26507

Ok to push.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/27288

Fixed in 5.0.10

Documented in 5.0.10 changelog: 

<listitem><para><literal>SHOW PROCEDURE/FUNCTION STATUS</literal> didn't work for users with limited access. (Bug #11577)</para></listitem>