MySQL Bugs: #14533: 'desc tbl' in stored procedure causes error 1142

Bug #14533	'desc tbl' in stored procedure causes error 1142
Submitted:	1 Nov 2005 1:57	Modified:	3 Feb 2006 15:32
Reporter:	Janak Ramakrishnan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.15, 5.0.16-BK	OS:	Linux (Linux, Windows)
Assigned to:	Per-Erik Martin	CPU Architecture:	Any

Description:
A stored procedure created with SQL SECURITY DEFINER which executes the SQL statement 'DESC' causes an error (ERROR 1142 (42000): SELECT command denied to user 'testuser'@'localhost' for table 'tbl1') when called by a user without DESC permissions on the table in question.  It appears that the 'DESC tbl1' (or 'SHOW COLUMNS FROM tbl1') command is not being checked against the definer's privileges, but rather the invoker's, since it works when the procedure is called by a user with the correct permissions for the table.

The procedure with 'SELECT * FROM tbl1' instead of 'DESC tbl1' does not cause an error, and when run gives the correct output, the contents of tbl1.

How to repeat:
/* logged in as root/admin */
create database proctest;
use proctest;
create table tbl1 (id int);
drop user testuser@localhost;
drop user testuser@'%';
create user testuser@localhost identified by 'testpass';
create procedure desctest ( ) SQL SECURITY DEFINER desc proctest.tbl1;
create procedure selecttest () SQL SECURITY DEFINER select * from proctest.tbl1;
grant execute on procedure proctest.desctest to testuser@localhost;
grant execute on procedure proctest.selecttest to testuser@localhost;
quit

/* logged in as testuser */
call proctest.desctest();
/* returns error 1142 */
call proctest.selecttest();
/* returns Empty set, no error */

Thank you for a bug report. Verified just as described on Linux Fedora Core 1 with 5.0.16-BK (ChangeSet@1.1957, 2005-10-29 13:11:34+04:00, konstantin@mysql.com):

mysql> create database proctest;
Query OK, 1 row affected (0,02 sec)

mysql> use proctest;
Database changed
mysql> create table tbl1 (id int);
Query OK, 0 rows affected (0,01 sec)

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

mysql> create procedure desctest ( ) SQL SECURITY DEFINER desc proctest.tbl1;
Query OK, 0 rows affected (0,00 sec)

mysql> create procedure selecttest () SQL SECURITY DEFINER select * from
    -> proctest.tbl1;
Query OK, 0 rows affected (0,00 sec)

mysql> grant execute on procedure proctest.desctest to testuser@localhost;
Query OK, 0 rows affected (0,01 sec)

mysql> grant execute on procedure proctest.selecttest to testuser@localhost;
Query OK, 0 rows affected (0,02 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 3 to server version: 5.0.16

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

mysql> call proctest.desctest();
ERROR 1142 (42000): SELECT command denied to user 'testuser'@'localhost' for table 'tbl1'
mysql> call proctest.selecttest();
Empty set (0,00 sec)

Query OK, 0 rows affected (0,00 sec)

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

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

mysql> grant all on proctest.tbl1 to testuser@localhost;
Query OK, 0 rows affected (0,02 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 5 to server version: 5.0.16

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

mysql> call proctest.desctest();
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0,00 sec)

Query OK, 0 rows affected (0,00 sec)

Just tested on 5.0.19, using today's bk pull.

mysql> call proctest.desctest();
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> call proctest.selecttest();
Empty set (0.00 sec)

This is no longer repeatable. (Fixed during the last three months, uncertain when.)

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/2000

Pushed to bk 5.0.19

Is this patch a fix or just a test case? Do we have a version number for the fix itself?

Closing as no changelog entry needed.