MySQL Bugs: #33455: Can't retrieve Routine Parameters if the user has limited privileges.

Bug #33455	Can't retrieve Routine Parameters if the user has limited privileges.
Submitted:	21 Dec 2007 10:36	Modified:	19 Oct 2008 7:28
Reporter:	Santo Leto	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S2 (Serious)
Version:	5.1.22-rc	OS:	Windows (XPSP2)
Assigned to:		CPU Architecture:	Any
Tags:	information_schema.routines, routine parameters

Description:
If an user hasn't the SELECT privilege on mysql.proc, he can't retrieve parameters for his routines.
He can retrieve many information about his routines quering the information_schema.routines table (but he can't retrieve the routine parameters from that table).

There is no reason for an user to have the select privilege on mysql.proc.

This behaviour is very unpleasant for a lot of reasons. 
I know that v.6 introduces the information_schema.parameters table (see http://bugs.mysql.com/33106).

I just ask you if you can extend the information_schema.routines table adding the field param_list.

Please note that for Scheduled Events I can retrieve all information I need from the table information_schema.events.

How to repeat:
The metadata I need are:

mysql> SELECT `param_list`, `comment`, `sql_data_access`, `security_type`, `retu
rns`, `body`, `definer`, `created`, `modified`, `sql_mode`, `is_deterministic` F
ROM `mysql`.`proc` WHERE `db` ='database1' AND `name` ='test01' and `type` ='pro
cedure'\G
*************************** 1. row ***************************
      param_list: IN `param1` BIGINT(20),OUT `param2` CHAR(45)
         comment: just a comment
 sql_data_access: CONTAINS_SQL
   security_type: DEFINER
         returns:
            body: BEGIN

        # do nothing

END
         definer: root@localhost
         created: 2007-12-21 10:55:34
        modified: 2007-12-21 10:55:34
        sql_mode:
is_deterministic: NO
1 row in set (0.00 sec)

The metadata that a limited-privileges user can retrieve are:

mysql> SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='database1
' AND ROUTINE_NAME = 'test01' AND ROUTINE_TYPE = 'procedure'\G
*************************** 1. row ***************************
       SPECIFIC_NAME: test01
     ROUTINE_CATALOG: NULL
      ROUTINE_SCHEMA: database1
        ROUTINE_NAME: test01
        ROUTINE_TYPE: PROCEDURE
      DTD_IDENTIFIER: NULL
        ROUTINE_BODY: SQL
  ROUTINE_DEFINITION: BEGIN

        # do nothing

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: 2007-12-21 10:55:34
        LAST_ALTERED: 2007-12-21 10:55:34
            SQL_MODE:
     ROUTINE_COMMENT: just a comment
             DEFINER: root@localhost
CHARACTER_SET_CLIENT: latin1
COLLATION_CONNECTION: latin1_swedish_ci
  DATABASE_COLLATION: latin1_swedish_ci
1 row in set (0.02 sec)

Many thanks for writing a feature request.

This was added in MySQL 6.0 via the INFORMATION_SCHEMA.PARAMETERS view:

http://dev.mysql.com/doc/refman/6.0/en/parameters-table.html

Please, check if INFORMATION_SCHEMA.PARAMETERS in 6.0.x solves the problem for you.

Valeriy,

Yes, INFORMATION_SCHEMA.PARAMETERS solves this problem. 

See also my comment here: http://www.honeysoftware.it/sleto/blog/2008/07/17/re-a-bugs-life/.

Implemented in 6.0.x.

Only to v6? Please fix this in 5.0 or 5.1 as well, the current ROUTINES table in the information schema in 5.x is half baked and this should have been taken care of with the first release of the information schema.