MySQL Bugs: #54449: Feature Request: Make EXECUTE more granular: allow grant for a single SP

Bug #54449	Feature Request: Make EXECUTE more granular: allow grant for a single SP
Submitted:	12 Jun 2010 3:35	Modified:	14 Jun 2010 13:25
Reporter:	Roel Van de Paar	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S3 (Non-critical)
Version:	5.1.47	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
mysql> GRANT EXECUTE ON roelt.test1 TO user1;
ERROR 1144 (42000): Illegal GRANT/REVOKE command; please consult the manual to s
ee which privileges can be used
mysql> GRANT EXECUTE ON roelt.* TO user1;
Query OK, 0 rows affected (0.00 sec)

How to repeat:
Workaround: create a separate db to host just that procedure

Suggested fix:
Allow GRANT EXECUTE ON db.procedure

Thank you for the feature request. In some cases GRANT ALL can be used as a workaround:

mysql> grant execute on test.pr1 to user1@localhost;
ERROR 1144 (42000): Illegal GRANT/REVOKE command; please consult the manual to see which privileges can be used
mysql> grant execute on test.* to user1@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on test.pr1 to user1@localhost;
Query OK, 0 rows affected (0.00 sec)

We already "Allow GRANT EXECUTE ON db.procedure".
The correct syntax looks like this:
GRANT EXECUTE ON PROCEDURE roelt.test1 to user;

Peter,

Why the inconsistency? (i.e. need to specify "PROCEDURE")

Also, the syntax shows object_type to be optional:

============
mysql> help grant
[...]
GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
[...]
object_type:
    TABLE
  | FUNCTION
  | PROCEDURE

priv_level:
    *
  | *.*
  | db_name.*
  | db_name.tbl_name
  | tbl_name
  | db_name.routine_name
============

The manual specifies it (the help option does not):
'The object_type clause, if present, should be specified as TABLE, FUNCTION, or PROCEDURE  when the following object is a table, a stored function, or a stored procedure.' - http://dev.mysql.com/doc/refman/5.1/en/grant.html
Still wondering why there is an inconsistency.

Roel,

It's possible for a procedure and a function to have
the same name, so requiring PROCEDURE | FUNCTION
prevents ambiguity.

Regardless of complaints about HELP statements,
Bug#54449 is "Feature Request: Make EXECUTE more
granular: allow grant for a single SP".
MySQL already supports that.