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.
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.