Bug #14875 Bad view DEFINER makes SHOW CREATE VIEW fail
Submitted: 11 Nov 2005 18:34 Modified: 26 May 2006 16:36
Reporter: Paul Dubois Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Views Severity:S3 (Non-critical)
Version:5.0.16/5.0.17 BK OS:Linux (Linux)
Assigned to: Georgi Kodinov

[11 Nov 2005 18:34] Paul Dubois
Description:
If you have the SUPER privilege so that you can specify any DEFINER
user for CREATE VIEW, but specify a non-existent account, that
makes SHOW CREATE VIEW fail:

mysql> create definer = 'no-such-user'@localhost view v as select 1;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show warnings;
+-------+------+------------------------------------------------+
| Level | Code | Message                                        |
+-------+------+------------------------------------------------+
| Note  | 1449 | There is not no-such-user@localhost registered |
+-------+------+------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create view v;
ERROR 1449 (HY000): There is not no-such-user@localhost registered

This behavior might be intentional.  But it makes mysqldump output
omit the view.  It also causes SHOW TABLES to omit the view.

I asked Sanja about this, who said:

>all above looks like good candidates for bugreports.
>The only thing which should be decided should we show real user name to
>user or it will be treated as showing view underplaying.

The "all above" refers to other issues that I had raised concerning the
format of the error message:

The error message might be slightly better if it were phrased
like one of these:

   There is no user@host account registered
   No user@host account is registered

Whatever phrasing you use for the error message, please quote the
user and host parts.  That will be more consistent with other error
essages, and quoting also eliminates an ugly problem if the user
part is empty.  Consider this example:

mysql> create definer = ''@localhost view v as select 1;
Query OK, 0 rows affected, 1 warning (0.16 sec)

mysql> show create view v;
ERROR 1449 (HY000): There is not @localhost registered

This error message would be clearer if the account was displayed
as ''@'localhost' because then it's more explicit that the username
is empty.

Also, here is another error message that can be changed:

Using a pre-5.0.13 view in 5.0.16 and up, we get a warning because the
definer is unknown:

mysql> select * from v;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set, 1 warning (0.05 sec)

mysql> show warnings;
+---------+------+--------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                            |
+---------+------+--------------------------------------------------------------------------------------------------------------------+
| Warning | 1447 | View test.v has not definer information (old table format). Current user is used as definer. Please recreate view! |
+---------+------+--------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Please change "has not definer" to "has no definer"

How to repeat:
See above.
[23 May 2006 10:20] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/6761
[26 May 2006 8:47] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/6901
[26 May 2006 12:48] Georgi Kodinov
pushed in 5.0.23-BK
[26 May 2006 16:36] Paul Dubois
Noted in 5.0.23 changelog.

A view with a non-existent account in the DEFINER clause caused SHOW
CREATE VIEW to fail. Now SHOW CREATE VIEW issues a warning instead.