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

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.

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.

@Sergei Golubchik the problem here is that the error message is a little bit misleading. If I create a view as user 'x', then dump that database and import it to another mariadb installation, no user is able to use that view, that is FAI and ok. If I try to use that view as root user I'll get a correct error message that user 'x' does not exist and the view could not be used. But if I want to access this view as user 'y', even if user 'y' has all privileges, I just get the error message 'you don't have the permissions to access this view'. This is really missleading and I don't know a reason for not showing the same error message as for the root user.

The general answer is — to not disclose the information to the user 'y' that this user has no right to see. This is why, for example, during login both incorrect password and incorrect user name say "access denied" and not "the password is incorrect" and "no such user".

In your particular case it is a question of privileges, still. There is no check in the code anywhere to do something differently for the 'root' user, the name 'root' is not special. The server looks at the SUPER privilege, because it's the privilege that allows to specify a definer, so it's the privilege that allows to see it too.

serg how will hiding the reason for the denial improve security? In case of a login it makes sense but not when the creator of a view does not exist.

My case has nothing to do with privileges. The user who created the view does not exist on the target system and the view could be used by every user if the creator would exist. So the error message that a user who wants to access this view does not have enough privileges, is not correct. There is no good reason to hide the correct error message from a user who has enough privileges to use this view.

every user has privileges to use the view, but not every user has privileges to specify the definer. Only users with the SUPER privilege can do that. That's why the detailed definer-related information is only shown to users with the SUPER privilege, not to every user with a SELECT privilege.

'every user has privileges to use the view, but not every user has privileges to specify the definer.' But if the creator does not exist anymore the error message says that each user who access the view, does not have enough privileges to access the view. This is wrong and should be changed.

I think a simple error message like "The view is not accessible" or "The view is in an inconsistent state, please inform an administrator" would have more useful information for an user.

Even better would be a warning for administrators while they import the data. if the creator of a view is not existent and the usage of the view requires the creator, the import script could show a warning.