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

Deleting users can break unrelated databases

        Details

        • Type: Bug
        • Status: Closed
        • Priority: Major
        • Resolution: Not a Bug
        • Affects Version/s: 5.5.41
        • Fix Version/s: N/A
        • Component/s: Views
        • Labels:
          None
        • Environment:
          Linux * 3.10.0-123.20.1.el7.x86_64 #1 SMP Thu Jan 29 18:05:33 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux

          Description

          Deleting a user can break unrelated databases.

          The problem, as far as I understand it is that deleting a user, causes all views (and possible other constructs) that he created not to be queryable anymore. Even worse, recreating that user doesn't solve the whole problem, because he has to have enough access rights to actually see the views he created to allow other users to query them.

          This seems very arbitrary and unrelated and is highly surprising. I vote to either:

          • disable this 'feature' outright (the user who initially created a view / database / table) should not need to stick around (imagine him changing companies, I want to get rid of him!) and the ability of another user to query a construct should not depend on another user being able to see / query it.
          • Deprecate it, that is start by adding an option to disable it, and then switch that option to be default on in some future release

          Some example error messages that we saw:
          SQL Fehler (1449): The user specified as a definer (´$foo´@´%´) does not exist
          1356: View '$bar' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

            Gliffy Diagrams

              Attachments

                Activity

                Ascending order - Click to sort in descending order
                Hide
                Permalink
                serg Sergei Golubchik added a comment -

                The user, who created the view doesn't need to stick around. But the user whose privileges the view uses — he does.

                In this case the view was created with explicit instructions „use the access rights of the user foo@%“. It's not surprising when this user foo@% is deleted the view stops working. There is nothing we can do in this case, MariaDB is doing exactly what you told it to.

                Alternatives are:

                • create the view with SQL SECURITY INVOKER, meaning the view will use access rights of whoever uses it.
                • create the view with the explicit definer, like CREATE DEFINER=bar@% VIEW ....
                • use roles. Grant privileges to roles, not to users. e.g. create a role developer, grant needed privileges to it, grant this role to your users. Create views with DEFINER=CURRENT_ROLE.
                Show
                serg Sergei Golubchik added a comment - The user, who created the view doesn't need to stick around. But the user whose privileges the view uses — he does. In this case the view was created with explicit instructions „use the access rights of the user foo@%“. It's not surprising when this user foo@% is deleted the view stops working. There is nothing we can do in this case, MariaDB is doing exactly what you told it to. Alternatives are: create the view with SQL SECURITY INVOKER , meaning the view will use access rights of whoever uses it. create the view with the explicit definer, like CREATE DEFINER=bar@% VIEW ... . use roles. Grant privileges to roles, not to users. e.g. create a role developer , grant needed privileges to it, grant this role to your users. Create views with DEFINER=CURRENT_ROLE .
                Hide
                Permalink
                dwt Martin Häcker added a comment -

                Many thanks for your comment Sergei, does that mean that the default for view creation is that it uses the access rights of the 'DEFINER'? (Because I couldn't find any definitions of this property in our code).

                If so, then probably the real bug is that the default behavior is really unintuitive and should be 'SQL SECURITY INVOKER' instead.

                If that is the case, you guys should probably get together with Oracle and start deprecating that default.

                Show
                dwt Martin Häcker added a comment - Many thanks for your comment Sergei, does that mean that the default for view creation is that it uses the access rights of the 'DEFINER'? (Because I couldn't find any definitions of this property in our code). If so, then probably the real bug is that the default behavior is really unintuitive and should be 'SQL SECURITY INVOKER' instead. If that is the case, you guys should probably get together with Oracle and start deprecating that default.

                  People

                  • Assignee:
                    serg Sergei Golubchik
                    Reporter:
                    dwt Martin Häcker
                  • Votes:
                    1 Vote for this issue
                    Watchers:
                    3 Start watching this issue

                    Dates

                    • Created:
                      Updated:
                      Resolved: