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

    • 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

          Ascending order - Click to sort in descending order
          jb-boin Jean Weisbuch created issue -
          serg Sergei Golubchik made changes -
          Field Original Value New Value
          Priority Trivial [ 5 ] Major [ 3 ]
          serg Sergei Golubchik made changes -
          Fix Version/s 5.5.36 [ 14600 ]
          serg Sergei Golubchik made changes -
          Priority Major [ 3 ] Minor [ 4 ]
          serg Sergei Golubchik made changes -
          Fix Version/s 5.5.37 [ 15000 ]
          Fix Version/s 5.5.36 [ 14600 ]
          serg Sergei Golubchik made changes -
          Fix Version/s 5.5.38 [ 15400 ]
          Fix Version/s 5.5.37 [ 15000 ]
          serg Sergei Golubchik made changes -
          Fix Version/s 5.5.39 [ 15800 ]
          Fix Version/s 5.5.38 [ 15400 ]
          serg Sergei Golubchik made changes -
          Workflow defaullt [ 33714 ] MariaDB v2 [ 44611 ]
          jb-boin Jean Weisbuch made changes -
          Summary A view with a non existing definer can block "SHOW TABLE STATUS" with an unclear error message A view or procedure with a non existing definer can block "SHOW TABLE STATUS" with an unclear error message
          jb-boin Jean Weisbuch made changes -
          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 :
          {noformat}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){noformat}

          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.           		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 :
          {noformat}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){noformat}

          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.
          jb-boin Jean Weisbuch made changes -
          Priority Minor [ 4 ] Major [ 3 ]
          serg Sergei Golubchik made changes -
          Attachment mdev-5553.diff [ 34600 ]
          serg Sergei Golubchik made changes -
          Assignee Sergei Golubchik [ serg ]
          serg Sergei Golubchik made changes -
          Status Open [ 1 ] In Progress [ 3 ]
          serg Sergei Golubchik made changes -
          Status In Progress [ 3 ] Stalled [ 10000 ]
          serg Sergei Golubchik made changes -
          Assignee Sergei Golubchik [ serg ] Sergei Petrunia [ psergey ]
          Status Stalled [ 10000 ] In Review [ 10002 ]
          psergei Sergei Petrunia added a comment -

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

          psergei Sergei Petrunia added a comment - I don't have any meaningful objections to the patch.
          psergei Sergei Petrunia made changes -
          Status In Review [ 10002 ] Stalled [ 10000 ]
          serg Sergei Golubchik made changes -
          Component/s Views [ 10111 ]
          Fix Version/s 5.5.40 [ 17100 ]
          Fix Version/s 5.5 [ 15800 ]
          Resolution Fixed [ 1 ]
          Status Stalled [ 10000 ] Closed [ 6 ]
          ratzpo Rasmus Johansson (Inactive) made changes -
          Workflow MariaDB v2 [ 44611 ] MariaDB v3 [ 64805 ]
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 64805 ] MariaDB v4 [ 147426 ]

          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.