Bug #14256 definer in view definition is not fully qualified
Submitted: 24 Oct 2005 13:30 Modified: 9 Nov 2005 2:57
Reporter: Georg Richter Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.16 OS:Linux (Linux)
Assigned to: Bugs System

[24 Oct 2005 13:30] Georg Richter
Description:
When creating a view on a remote machine (user has access from host='%'), view definition doesn't contain fully qualified definer.

This affects also backups made with mysqldump (you can't restore views).

How to repeat:
1) Define a user account with wildcard
[15:19] root@(none)>grant all on test.* to 'georg'@'%' identified by 'foo';
Query OK, 0 rows affected (0.00 sec)

2) Connect to the remote server
mysql -ugeorg -p -hremote_server_name

CREATE VIEW V1 AS SELECT 1;
SHOW CREATE VIEW V1 \G

Output:
CREATE ALGORITHM=UNDEFINED DEFINER="georg"@"%" SQL SECURITY DEFINER VIEW "V1" AS select 1 AS "1"

3) Drop the view and recreate it with the output from previous SHOW CREATE VIEW
DROP VIEW V1;
CREATE ALGORITHM=UNDEFINED DEFINER="georg"@"%" SQL SECURITY DEFINER VIEW "V1" AS select 1 AS "1";

Error: ERROR 1446 (HY000): View definer is not fully qualified
[24 Oct 2005 14:36] Mark Leith
Effecting another customer.
[27 Oct 2005 16:56] Thomas Healy
I have this problem and I want to make certain you understand my circumstance and that it will be addressed by this fix.  I use a simple create view statement on my master server like the following:

CREATE VIEW order_detail AS SELECT orders.order_id AS order_id, order_items.line_number AS line_number,order_items.quantity AS quantity
FROM orders, order_items
WHERE orders.order_id = order_items.order_id.

Once the slave tries to run it it looks like

CREATE ALGORITHM=UNDEFINED DEFINER=`jumpadmin`@`%` SQL SECURITY DEFINER VIEW `order_detail` AS select `orders`.`order_id` AS `order_id`,`order_items`.`line_number` AS `line_number`,`order_items`.`quantity` AS `quantity` from (`orders` join `order_items`) where (`orders`.`order_id` = `order_items`.`order_id`)

It is important that you do not have to fully qualify the user name because a fully qualified user, by definition, does not reside on my slave.

Thanks.
[28 Oct 2005 10:49] 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/internals/31607
[28 Oct 2005 14:21] Per-Erik Martin
Pushed to bk 5.0.16.
[9 Nov 2005 2:57] Paul Dubois
Noted in 5.0.16 changelog.
[31 Jan 2006 13:42] Gabriele Faggioni
I have installed version 5.0.18 on RH linux 9, but I ve the same problem.
Any suggestion?
[31 Jan 2006 14:45] Oleksandr Byelkin
1) be sure that you do not use --skip-grant tables (if you are then use DEFINER clause)
2) if above is not your case, then submit please new bugreport with repeatable sequance of SQL statements begining from table/view creation (because it looks like you have a bit different case)