Bug #14564 show create function has a blank field
Submitted: 2 Nov 2005 6:16 Modified: 7 Jan 2008 15:20
Reporter: Janak Ramakrishnan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.0.16-BK, 5.0.15 OS:Linux (Linux, Windows)
Assigned to: Paul Dubois
Triage: D4 (Minor)

[2 Nov 2005 6:16] Janak Ramakrishnan
Description:
when a user does not have permissions to select from mysql.proc, the command
show create function fn_name
succeeds with a blank Create Function field, no matter what the permissions the user has for the routine itself.

Either the command should fail because of permissions, or users with the appropriate permissions should be able to view the code of the function, even without permission to view the entire mysql.proc table.  The appropriate permissions could be Execute, or an entirely new permission.

How to repeat:
drop user testuser@'%';
drop user testuser@localhost;
create user testuser@localhost identified by 'testpass';
create database showtest;
use showtest;
create function fntest () returns int return 1;
grant all on function fntest to testuser@localhost;
/* logged in as testuser */
show create function showtest.fntest;
/* Returns:
+----------+-----------------------------------------+-----------------+
| Function | sql_mode                                | Create Function |
+----------+-----------------------------------------+-----------------+
| fntest   | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER |                 |
+----------+-----------------------------------------+-----------------+
*/
[8 Nov 2005 1:16] Janak Ramakrishnan
on second thought, this really is a bug, since notification should be given about the blank field.
[8 Nov 2005 18:16] Valerii Kravchuk
Thank you for a bug report. Verified just as described using 5.0.16-BK build (ChangeSet@1.1972, 2005-11-05 22:45:54-08:00, igor@rurik.mysql.com) on Linux:

mysql> drop user testuser@'%';
ERROR 1396 (HY000): Operation DROP USER failed for 'testuser'@'%'
mysql> drop user testuser@localhost;
Query OK, 0 rows affected (0,02 sec)

mysql> create user testuser@localhost identified by 'testpass';
Query OK, 0 rows affected (0,00 sec)

mysql> create database showtest;
Query OK, 1 row affected (0,01 sec)

mysql> use showtest;
Database changed
mysql> create function fntest () returns int return 1;
Query OK, 0 rows affected (0,00 sec)

gmysql> grant all on function fntest to testuser@localhost;
Query OK, 0 rows affected (0,00 sec)

mysql> exit
Bye
[openxs@Fedora 5.0]$ bin/mysql -utestuser -ptestpass
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14 to server version: 5.0.16

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

mysql> show create function showtest.fntest;
+----------+----------+-----------------+
| Function | sql_mode | Create Function |
+----------+----------+-----------------+
| fntest   |          |                 |
+----------+----------+-----------------+
1 row in set (0,01 sec)

mysql> select showtest.fntest();
+-------------------+
| showtest.fntest() |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0,00 sec)

If this is the intended behaviour, it should be described in http://dev.mysql.com/doc/refman/5.0/en/show-create-procedure.html or some other prominent place inf the manual.
[15 Feb 2006 20:51] Miguel Solorzano
Bug http://bugs.mysql.com/bug.php?id=17424 was marked as
duplicate of this one.
[24 Aug 2006 11:07] Bugs System
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/commits/10822

ChangeSet@1.2227, 2006-08-24 16:24:43+05:00, gluh@mysql.com +3 -0
  Bug#14564 show create function has a blank field
  issue a warning if user does not have enough rights to 
  get full information about routine.
[13 Sep 2006 8:21] Alexander Barkov
The patch looks ok to push.

Also: consider sharing common code between 
sp_head::show_create_function() and sp_head::show_create_procedure().
[22 Sep 2006 18:44] Sergei Golubchik
is that right ? sql standard (Schemata part) specifies that if one does not have permissions he should not see the body. Not an error, not a warning. Empty body.
[6 Aug 2007 11:34] Konstantin Osipov
Putting back to verified, no progress during the past year.
[4 Dec 2007 21:00] Konstantin Osipov
After 2 years of GA, we should document this.
Agree with Serg's observation wrt standard semantics.
[12 Dec 2007 14:11] Valerii Kravchuk
OK, let's just document this, including the reasons.
[7 Jan 2008 15:20] Paul Dubois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

SHOW CREATE PROCEDURE/FUNCTION won't show you the routine body
if you don't have privileges for it. (The "Create Procedure" or "Create
Function" field will be NULL.)