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

Confusing Permissions denied error message on VIEWS with SQL SECURITY DEFINER

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 10.4.13
    • Fix Version/s: None
    • Component/s: Views
    • Labels:
      None

      Description

      When a VIEW is created with SQL SECURITY DEFINER (default setting), and the DEFINER user is subsequently deleted, the VIEW remains valid.

      Whenever a SELECT query is run on the VIEW, the following error message is shown:

      [28000][1045] (conn=25) Access denied for user <invoker> (using password: YES)

      (<invoker> stands for the user executing the SELECT query on the VIEW.)

      I think this error message is confusing, because it seems to imply that <invoker> does not have the correct permissions to execute the query on the VIEW, while the actual error is that the DEFINER of the VIEW does not have the correct permissions to execute the VIEW query (as it no longer exists).

      While this behaviour of VIEWs is documented, the fact that SQL SECURITY DEFINER is the default for new VIEWs makes it difficult to debug issues when one is not familiar with the difference between SQL SECURITY DEFINER and SQL SECURITY INVOKER.

      E.g. in my particular case I imported a dump created with the mysqldump tool, which included a VIEW with SQL SECURITY DEFINER. Since the users on my MariaDB instance are not defined in the same way as on the instance of which the dump was taken, I could not run SELECT queries on the VIEW until I manually changed the view to have SQL SECURITY INVOKER. While this is an easy task, finding out that it had to be done was not as easy.

        Attachments

          Activity

            People

            Assignee:
            Unassigned
            Reporter:
            vmsh0 Riccardo Paolo Bestetti
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated: