MySQL Bugs: #81325: Cite Unauthorized VIEW DEFINER on Authentication Errors

Bug #81325	Cite Unauthorized VIEW DEFINER on Authentication Errors
Submitted:	5 May 2016 12:52	Modified:	5 Jun 2016 14:35
Reporter:	Derek Price	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S4 (Feature request)
Version:	5.6.24	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When the user listed as the DEFINER of a VIEW no longer exists, the error reported claims, "SQLSTATE[28000]: Invalid authorization specification: 1045 Access denied for user 'loggedinuser'@'%' (using password: YES)", where "loggedinuser" is the logged in user.  This can require excessive debugging time as credentials are verified, the broken SQL statement is located, and the broken SQL statement is broken down into its constituent parts until the portion causing the error is located, and then it still requires the detective to notice that the located VIEW has a DEFINER that no longer exists and that the VIEW has SQL SECURITY DEFINER specified.

How to repeat:
Create a VIEW with SQL SECURITY DEFINER using user "todelete".  Delete user "todelete".  Select from the view with a second user, "loggedinuser".  The MySQL error message will claim an error authorizing "loggedinuser".

Suggested fix:
Update the error message output in this case to at least mention that it is the DEFINER of the VIEW that could not be authorized, not the logged in user.  Ideally, the error message could specify something like "Access denied for VIEW `viewname` DEFINER 'todelete'@'%'".  This would point the user straight at the problem instead of requiring digging.

Hi Mr. Price,

Thank you so much for your bug report. This actually is not a bug. This is a request for the improvement that might help users, more specifically DBAs, in diagnosing the errors in the setups..

However, as much as I would wish to pronounce this report as a valid feature request, I can't. You see, the more verbose you are with error messages on security related errors, the more easily it becomes to crack the system.

Error messages related to the security must be minimized, with minimal info provided. It is duty of DBA to go through a checklist of the setup in order to find the error and , yes, to do some detective work. That is a much more secure system then the one that you ask for.

I hope that you agree with my comment.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".