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

    Details

    • Type: Bug
    • Status: Closed
    • 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.

        Gliffy Diagrams

          Attachments

            Activity

            Hide
            psergey Sergei Petrunia added a comment -

            I don't have any meaningful objections to the patch.

            Show
            psergey Sergei Petrunia added a comment - I don't have any meaningful objections to the patch.

              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:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 2 hours
                  2h