Bug #19920 SHOW GRANTS can crash the server, if host information doesn't match.
Submitted: 18 May 2006 21:08 Modified: 31 May 2006 13:38
Reporter: Markus Popp Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.22-BK, 5.0.21, 5.1.9/4.1BK OS:Linux (Linux, others?)
Assigned to: Assigned Account CPU Architecture:Any

[18 May 2006 21:08] Markus Popp
Description:
If there's a user xxx@'%' who has privileges for a specific table and you manually change the host information in the tables_priv table (e.g. to '' - empty string, but it might apply to other entries, too) and you afterwards issue a SHOW GRANTS for xxx@'%' command, the server crashes.

How to repeat:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.21-max

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

mysql> CREATE USER testuser@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE DATABASE test1;
Query OK, 1 row affected (0.02 sec)

mysql> CREATE DATABASE test2;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE TABLE test2.tt (
    ->   id INT NOT NULL PRIMARY KEY);
Query OK, 0 rows affected (0.06 sec)

mysql> GRANT ALL ON test1.* TO testuser@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL ON test2.tt TO testuser@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE mysql.tables_priv
    ->   SET host=''
    ->   WHERE user='testuser';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR testuser@'%';
ERROR 2013 (HY000): Lost connection to MySQL server during query
[18 May 2006 22:00] Valeriy Kravchuk
Thank you for a bug report. Verified just as described with 5.0.22-BK (ChangeSet@1.2122.24.1, 2006-05-18 00:55:28+04:00) on Linux:

mysql> CREATE USER testuser@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE DATABASE test1;
Query OK, 1 row affected (0.01 sec)

mysql> CREATE DATABASE test2;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE TABLE test2.tt (id INT NOT NULL PRIMARY KEY);
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL ON test1.* TO testuser@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL ON test2.tt TO testuser@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE mysql.tables_priv SET host='' WHERE user='testuser';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR testuser@'%';
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
Number of processes running now: 0
060518 21:36:12  mysqld restarted

mysql> select version();
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: test

+-----------+
| version() |
+-----------+
| 5.0.22    |
+-----------+
1 row in set (0.26 sec)

Test case:

CREATE USER testuser@'%';
CREATE DATABASE test1;
CREATE DATABASE test2;
CREATE TABLE test2.tt (id INT NOT NULL PRIMARY KEY);
GRANT ALL ON test1.* TO testuser@'%';
GRANT ALL ON test2.tt TO testuser@'%';
UPDATE mysql.tables_priv SET host='' WHERE user='testuser';
FLUSH PRIVILEGES;
SHOW GRANTS FOR testuser@'%';
[18 May 2006 22:10] Miguel Solorzano
Also crash 4.1:
miguel@hegel:~/dbs/4.1> bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.19-debug

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

mysql> CREATE USER testuser@'%';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USER testuser@'%'' at line 1
mysql> CREATE DATABASE test1;
Query OK, 1 row affected (0.03 sec)

mysql> CREATE DATABASE test2;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE TABLE test2.tt (id INT NOT NULL PRIMARY KEY);
Query OK, 0 rows affected (0.02 sec)

mysql> GRANT ALL ON test1.* TO testuser@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL ON test2.tt TO testuser@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE mysql.tables_priv SET host='' WHERE user='testuser';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW GRANTS FOR testuser@'%';
ERROR 2013 (HY000): Lost connection to MySQL server during query

[New Thread 1114811312 (LWP 23361)]
/home/miguel/dbs/4.1/libexec/mysqld: ready for connections.
Version: '4.1.19-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
[New Thread 1129880496 (LWP 23391)]

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 1129880496 (LWP 23391)]
0x08433952 in my_strcasecmp_utf8 (cs=0x85d1e40, s=0x8c97d08 "%", t=0x0)
    at ctype-utf8.c:2130
2130      while (s[0] && t[0])
Current language:  auto; currently c
(gdb)
[19 May 2006 4:31] Shane Bester
didn't crash my 4.0.26

<cut>
mysql> SHOW GRANTS FOR testuser@'%';
+-----------------------------------------------------+
| Grants for testuser@%                               |
+-----------------------------------------------------+
| GRANT USAGE ON *.* TO 'testuser'@'%'                |
| GRANT ALL PRIVILEGES ON `test1`.* TO 'testuser'@'%' |
+-----------------------------------------------------+
2 rows in set (0.00 sec)
[31 May 2006 13:38] Tatiana Azundris Nuernberg
duplicate of #16297: In memory grant tables not flushed when users's hostname is ""
[31 May 2006 13:39] 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/7094