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

Group By in a View, called within a Stored Routine causes Error Code 1356 when a non-root user runs the routine for a second time

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.2, 10.2.7
    • Fix Version/s: 10.2.8
    • Environment:

      Description

      When logged in as a user that does not have global select privilege, If you have a table, which is then displaying in a view, with that view having a sub-query which has a 'GROUP BY' clause; and then you try to do any form of select* on that view in a stored function or procedure, the first time it will work fine, but then if you run it again before any form of metadata change +, you get the following error:

      1 queries executed, 0 success, 1 errors, 0 warnings
       
      Query: CALL `procViewProcedure`
       
      Error Code: 1356
      View 'bugTest.procViewSimple' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
       
      Execution Time : 0 sec
      Transfer Time  : 0 sec
      Total Time     : 0.002 sec
      

      *In regards to different forms of SELECT, I have tested (at least):

      • SELECT * FROM `viewName`;
      • SELECT * FROM `viewName` WHERE `aColumn` = 2;
      • SELECT `singleColumn` FROM `viewName` WHERE `differentColumn` = 2;
      • SELECT `singleColumn` INTO `aRoutineVar` FROM `viewName`;
      • SELECT `singleColumn` INTO `aRoutineVar` FROM `viewName` WHERE `aDifferentColumn` = 2;

      + I have noticed that the following changes will allow the procedure to complete again (one time only):

      • Altering the view
      • Dropping and recreating the procedure/function
        However, doing an INSERT on the base table does not allow the routine to complete.

      Commenting out either the GROUP BY (but leaving the sub-query) or taking out the sub-query (but leaving the GROUP BY) will resolve the issue. Further to this (as noted at the start), granting the global SELECT privilege will resolve the issue, however granting all global privileges and all privileges on all databases (except information_schema and performance_schema) does not resolve the issue.

      This is not a bug in MySQL 5.7.19 (on Windows 10 64bit), however there was a potentially related issue in the past. Despite this stack overflow thread, I was not able to find any related bug reports (in either MariaDB or MySQL), and the version that thread relates to (MySQL 5.1) disallows sub-queries in views, so I can't replicate it with my current code on anything older than MySQL 5.7.

      I have attached:

      • grantsForBuggedUser.csv -> This is a CSV of the output of SHOW GRANTS; for the procView user (the user which the bug occurs for). It will occur with a much smaller permission set of course, but I wanted the widest set of permissions where the bug would still occur.
      • bugReportDump.sql -> This is the relevant tables, view, function and procedure to replicate the bug. There is a second table (`subProcViewTable`), which I had initially as I was trying to work from the production code where I found the issue to the simplest test case where the bug still occurs.

      Finally, as a workaround if you manage the server, it is possible to have a user with global select privilege only, and then set the definer of the views which cause the bug to happen to that user, however obviously it is preferably to avoid that if possible just to avoid handing out global privileges needlessly - and where you can't grant global privileges you can't do that.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              sanja Oleksandr Byelkin
              Reporter:
              rmhumphries Robert Humphries
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: