Mysql 访问视图却报ERROR 1356 (HY000)错误
1.适用范围
MYSQL 8.0
2.问题概述
普通业务用户，在自己用户下创建了表和视图，表可以正常访问，而访问视图却报ERROR 1356 (HY000)错误，测试如下：
mysql> desc asher_test ;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| name | varchar(50) | YES | | NULL | |
| purchased | date | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> select * from asher_test;
+-------------+------------+
| name | purchased |
+-------------+------------+
| fullbackup | 2022-11-27 |
+-------------+------------+
1 row in set (0.00 sec)
mysql> select * from ray ;
ERROR 1356 (HY000): View 'drm.ray' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
3.问题原因
3.1.通过root 用户查询问题仍然存在,且所有视图问题都是相同的。
mysql> select * from ray ;
ERROR 1356 (HY000): View 'drm.ray' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
3.2.root 自己的创建的视图可以正常访问
CREATE VIEW asher_v2
(v_name,v_purchased )
AS SELECT name, purchased
FROM asher_test;
mysql> select * from asher_v2 ;
+-------------+-------------+
| v_name | v_purchased |
+-------------+-------------+
| fullbackup | 2022-11-27 |
+-------------+-------------+
1 row in set (0.00 sec)
drop VIEW asher_v2 ;
3.3.查看用户权限
mysql> show grants for `drm_admin`@`%` ;
+------------------------------------------------------------------------------------+
| Grants for drm_admin@% |
+------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `drm_admin`@`%` |
| GRANT SELECT ON `performance_schema`.`user_variables_by_thread` TO `drm_admin`@`%` |
| GRANT `drm_admin_role`@`%` TO `drm_admin`@`%` |
+------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
mysql> show grants for drm_admin_role ;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for drm_admin_role@% |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT PROCESS, CREATE USER ON *.* TO `drm_admin_role`@`%` |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, EXECUTE,
CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, TRIGGER ON `drm`.* TO `drm_admin_role`@`%` WITH GRANT OPTION |
| |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
3.4.以为是权限问题，重新授权问题仍然存在
grant all privileges on drm.* to drm_admin_role ;
CREATE VIEW asher_v3
(v_name,v_purchased )
AS SELECT name, purchased
FROM asher_test;
mysql> select * from asher_v3 ;
ERROR 1356 (HY000): View 'drm.asher_v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
3.5.创建普通用户直接授权,可以正常查询
create user test@'%' identified by 'xxxxxx';
Query OK, 0 rows affected (0.00 sec)
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, TRIGGER ON `drm`.* TO test@'%' WITH GRANT OPTION ;
CREATE VIEW asher_v4
AS SELECT name, purchased
FROM asher_test;
select * from asher_v4 ;
mysql> select * from asher_v4 ;
+-------------+------------+
| name | purchased |
+-------------+------------+
| fullbackup | 2022-11-27 |
+-------------+------------+
1 row in set (0.00 sec)
3.6.通过角色授权,问题存在
mysql> show grants for test@'%' ;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for test@% |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `test`@`%` |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, TRIGGER ON `drm`.* TO `test`@`%` WITH GRANT OPTION |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
revoke SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, TRIGGER ON `drm`.* from `test`@`%` ;
mysql> show grants for test@'%' ;
+--------------------------------------------------------+
| Grants for test@% |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO `test`@`%` |
| GRANT USAGE ON `drm`.* TO `test`@`%` WITH GRANT OPTION |
+--------------------------------------------------------+
2 rows in set (0.01 sec)
GRANT `drm_admin_role`@`%` TO test@'%' ;
show grants for test@'%' ;
+--------------------------------------------------------+
| Grants for test@% |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO `test`@`%` |
| GRANT USAGE ON `drm`.* TO `test`@`%` WITH GRANT OPTION |
| GRANT `drm_admin_role`@`%` TO `test`@`%` |
+--------------------------------------------------------+
3 rows in set (0.00 sec)
drop VIEW asher_v5 ;
CREATE VIEW asher_v5
AS SELECT name, purchased
FROM asher_test;
mysql> select * from asher_v5 ;
ERROR 1356 (HY000): View 'drm.asher_v5' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
同样的权限给角色 不能解决问题
3.7.重建角色问题仍然存在
revoke `drm_admin_role`@`%` from `test`@`%` ;
create role test_role ;
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, TRIGGER ON `drm`.* TO test_role WITH GRANT OPTION ;
mysql> show grants for test_role ;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for test_role@% |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `test_role`@`%` |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, TRIGGER ON `drm`.* TO `test_role`@`%` WITH GRANT OPTION |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> show grants for drm_admin_role ;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for drm_admin_role@% |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT PROCESS, CREATE USER ON *.* TO `drm_admin_role`@`%` |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, TRIGGER ON `drm`.* TO `drm_admin_role`@`%` WITH GRANT OPTION |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql>
grant test_role to `test`@`%` ;
show grants for `test`@`%` ;
+--------------------------------------------------------+
| Grants for test@% |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO `test`@`%` |
| GRANT USAGE ON `drm`.* TO `test`@`%` WITH GRANT OPTION |
| GRANT `test_role`@`%` TO `test`@`%` |
+--------------------------------------------------------+
3 rows in set (0.00 sec)
drop VIEW asher_v6 ;
CREATE VIEW asher_v6
AS SELECT name, purchased
FROM asher_test;
3.8.将视图 definer改成invoker的
# 把视图 definer改成invoker的 试试
mysql> show create view asher_v6;
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View | character_set_client | collation_connection |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| asher_v6 | CREATE ALGORITHM=UNDEFINED DEFINER=`test`@`%` SQL SECURITY DEFINER VIEW `asher_v6` AS select `asher_test`.`name` AS `name`,`asher_test`.`purchased` AS `purchased` from `asher_test` | utf8mb4 | utf8mb4_0900_ai_ci |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)
CREATE ALGORITHM=UNDEFINED DEFINER=`test`@`%` SQL SECURITY INVOKER VIEW `asher_v7` AS select `asher_test`.`name` AS `name`,`asher_test`.`purchased` AS `purchased` from `asher_test`
mysql> select * from asher_v7 ;
+-------------+------------+
| name | purchased |
+-------------+------------+
| fullbackup | 2022-11-27 |
+-------------+------------+
1 row in set (0.00 sec)
# 更改现有视图，将视图 definer改成invoker
ALTER
ALGORITHM=UNDEFINED
DEFINER=`test`@`%`
SQL SECURITY INVOKER VIEW `asher_v6` AS select `asher_test`.`name` AS `name`,`asher_test`.`purchased` AS `purchased` from `asher_test` ;
select * from asher_v6 ;
+-------------+------------+
| name | purchased |
+-------------+------------+
| fullbackup | 2022-11-27 |
+-------------+------------+
1 row in set (0.00 sec)
4.解决方案
通过以肯方案测试，得知在创建视图或者是存储过程的时候，是需要定义安全验证方式的(也就是安全性SQL SECURITY)，其值可以为definer或invoker，表示在执行过程中，使用谁的权限来执行。
definer:由definer(定义者)指定的用户的权限来执行
invoker:由调用这个视图(存储过程)的用户的权限来执行
但是我们这里是用了角色，所以第如果是用的默认definer，就不能用角色，而是直接授权。如果要用角色，就需要将definer改为角色的名字，而不是用户的名字。
4.1 方案一，更definer改成invoker
这里我们可以直接将视图 definer改成invoker ，更改方案如下：
4.1.1直接更改现有视图，将视图 definer改成invoker
ALTER
ALGORITHM=UNDEFINED
DEFINER=`test`@`%`
SQL SECURITY INVOKER VIEW `asher_v6` AS select `asher_test`.`name` AS `name`,`asher_test`.`purchased` AS `purchased` from `asher_test` ;
4.1.2 创建视图的时候，将视图 definer改成invoker
CREATE ALGORITHM=UNDEFINED DEFINER=`test`@`%` SQL SECURITY INVOKER VIEW `asher_v7` AS select `asher_test`.`name` AS `name`,`asher_test`.`purchased` AS `purchased` from `asher_test`
4.2 方案二,更改DEFINER为角色名
视图默认为definer情况下，指定definer改为角色的名字
直接授权在上面的2.5测试中，是可以执行的，所以这里我们将definer改为角色的名字
mysql> select * from asher_v5 ;
ERROR 1356 (HY000): View 'drm.asher_v5' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysql> show create view asher_v5 ;
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View | character_set_client | collation_connection |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| asher_v5 | CREATE ALGORITHM=UNDEFINED DEFINER=`test`@`%` SQL SECURITY DEFINER VIEW `asher_v5` AS select `asher_test`.`name` AS `name`,`asher_test`.`purchased` AS `purchased` from `asher_test` | utf8mb4 | utf8mb4_0900_ai_ci |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)
ALTER
ALGORITHM=UNDEFINED
DEFINER=`test_role`@`%`
SQL SECURITY DEFINER VIEW `asher_v5` AS select `asher_test`.`name` AS `name`,`asher_test`.`purchased` AS `purchased` from `asher_test` ;
mysql> show create view asher_v5 ;
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View | character_set_client | collation_connection |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| asher_v5 | CREATE ALGORITHM=UNDEFINED DEFINER=`test_role`@`%` SQL SECURITY DEFINER VIEW `asher_v5` AS select `asher_test`.`name` AS `name`,`asher_test`.`purchased` AS `purchased` from `asher_test` | utf8mb4 | utf8mb4_0900_ai_ci |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)
mysql> select * from asher_v5 ;
+-------------+------------+
| name | purchased |
+-------------+------------+
| fullbackup | 2022-11-27 |
+-------------+------------+
1 row in set (0.00 sec)
5.参考文档
https://dev.mysql.com/doc/refman/8.0/en/create-view.html
https://dev.mysql.com/doc/refman/8.0/en/stored-objects-security.html
