Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-5553

A view or procedure with a non existing definer can block "SHOW TABLE STATUS" with an unclear error message

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 5.5.34
    • Fix Version/s: 5.5.40
    • Component/s: Views
    • Labels:
      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.

        Attachments

          Activity

            People

            Assignee:
            psergey Sergei Petrunia
            Reporter:
            jb-boin Jean Weisbuch
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: