Bug #29842 access denied on view definer deletion

• Submitted: 17 Jul 2007 9:54
• Modified: 17 Jul 2007 11:36
• Reporter: Maikel Punie
• Status: Not a Bug
• Category: MySQL Server: Views
• Severity: S3 (Non-critical)
• Version: 5.0.38
• OS: Linux (5.0.38-Debian_2-log)
• CPU Architecture: Any
• Tags: access denied, users, Views

[17 Jul 2007 9:54] Maikel Punie

Description:
When a view is defined by a specific user, and that user is deleted afterwards the view isn’t accessible by any user anymore. When a user tries to access a such a view, it gets an access denied error message.

How to repeat:
- create a view defined by user1
- access the view with user 2, everything works

- delete user 1
- try to access the view with user 2
- results in an access denied

Suggested fix:
maybe a better error message could be given when a view is accessed witch doesn't has a definer,

or when a user is deleted a warning could be shown that the views (a list of the views) created by this user won't work anymore.

[17 Jul 2007 11:36] Sveta Smirnova

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

>maybe a better error message could be given when a view is accessed witch
> doesn't has a definer,

> or when a user is deleted a warning could be shown that the views (a list of
> the views) created by this user won't work anymore.

root user gets such information:

$mysql50 test -uu2
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2993
Server version: 5.0.48-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select * from v1;
ERROR 1045 (28000): Access denied for user 'u2'@'localhost' (using password: NO)
mysql> \q
Bye

$mysql50 test -uroot
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2995
Server version: 5.0.48-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select * from v1;
ERROR 1449 (HY000): There is no 'u1'@'localhost' registered