Details
Description
Creating a view with a definer that does not exists can block a SHOW TABLE STATUS for the whole database if a GROUP BY is used on the view :
MariaDB [test2]> CREATE TABLE `testtable` (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> PRIMARY KEY (`id`)
-> );
Query OK, 0 rows affected (0.01 sec)
MariaDB [test2]> SHOW TABLE STATUS;
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| testtable | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2014-01-22 19:29:18 | NULL | NULL | latin1_swedish_ci | NULL | | |
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
1 row in set (0.00 sec)
MariaDB [test2]> CREATE DEFINER=`unknownuser`@`%` SQL SECURITY DEFINER VIEW `testview` AS SELECT testtable.id FROM testtable GROUP BY testtable.id;
Query OK, 0 rows affected, 1 warning (0.00 sec)
MariaDB [test2]> SHOW WARNINGS;
+-------+------+--------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------------------------------------+
| Note | 1449 | The user specified as a definer ('unknownuser'@'%') does not exist |
+-------+------+--------------------------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [test2]> SHOW TABLE STATUS;
ERROR 1143 (42000): SELECT command denied to user ''@'%' for column 'id' in table 'testtable'
MariaDB [test2]> SHOW WARNINGS;
+-------+------+---------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------------------------------------+
| Error | 1143 | SELECT command denied to user ''@'%' for column 'id' in table 'testtable' |
| Note | 1449 | The user specified as a definer ('unknownuser'@'%') does not exist |
+-------+------+---------------------------------------------------------------------------+
2 rows in set (0.00 sec)
MariaDB [test2]> SELECT USER();
+----------------+
| USER() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
The error message when the "SHOW TABLE STATUS" is issued is not clear, it should ideally reference to the incorrectly defined view and to the non-existing definer.
ps: the same happens with ROUTINES/PROCEDURES.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
I don't have any meaningful objections to the patch.