Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5.34
-
None
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.