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

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5.34
    • 5.5.40
    • Views
    • 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

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

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.