Bug #78896 Inconsistent error on using view with missing definer
Submitted: 21 Oct 2015 10:32 Modified: 21 Oct 2015 12:55
Reporter: Przemyslaw Malkowski Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Errors Severity:S3 (Non-critical)
Version:5.7.8, 5.6.27, 5.6.28 OS:Any
Assigned to: CPU Architecture:Any

[21 Oct 2015 10:32] Przemyslaw Malkowski
Description:
For some reason only a user that has all privileges gets the proper error message when trying to read from a view for which definer user is missing.
When reading as non privileged user, a confusing 'Access denied' error is returned instead.
Not sure which exactly privilege is needed to get the correct error, maybe only the 'ALL' works here.

How to repeat:
--create view as user1
mysql [localhost] {user1} (db1) > create table t1 (id int);
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {user1} (db1) > create view v1 as select id from t1;
Query OK, 0 rows affected (0.02 sec)

--drop user1
mysql [localhost] {root} ((none)) > drop user user1@localhost;
Query OK, 0 rows affected (0.00 sec)

--select from the view as user2
mysql [localhost] {user2} ((none)) > show grants;
+-----------------------------------------------------------------------------+
| Grants for user2@localhost                                                  |
+-----------------------------------------------------------------------------+
| GRANT SELECT, EXECUTE, CREATE VIEW, SHOW VIEW ON *.* TO 'user2'@'localhost' |
+-----------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql [localhost] {user2} (db1) > select * from v1;
ERROR 1045 (28000): Access denied for user 'user2'@'localhost' (using password: NO)

--grant ALL to the user2
mysql [localhost] {root} ((none)) > grant ALL ON *.* TO 'user2'@'localhost';
Query OK, 0 rows affected (0.00 sec)

--select from the view as user2 again
mysql [localhost] {user2} (db1) > show grants;
+----------------------------------------------------+
| Grants for user2@localhost                         |
+----------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'user2'@'localhost' |
+----------------------------------------------------+
1 row in set (0.00 sec)

mysql [localhost] {user2} (db1) > select * from v1;
ERROR 1449 (HY000): The user specified as a definer ('user1'@'localhost') does not exist

mysql [localhost] {user2} (db1) > show create view v1\G
*************************** 1. row ***************************
                View: v1
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`user1`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`id` AS `id` from `t1`
character_set_client: latin1
collation_connection: latin1_swedish_ci
1 row in set, 1 warning (0.00 sec)

Suggested fix:
IMHO the 1449 error should be displayed for the user having at least select rights to a table.
[21 Oct 2015 12:55] Umesh Shastry
Hello Przemyslaw,

Thank you for the report.
Verified as described with 5.6.28 build.

Thanks,
Umesh