1.适用范围

MYSQL 8.0

2.问题概述

普通业务用户，在自己用户下创建了表和视图，表可以正常访问，而访问视图却报ERROR 1356 (HY000)错误，测试如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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 用户查询问题仍然存在,且所有视图问题都是相同的。

1
2
3
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 自己的创建的视图可以正常访问

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

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.查看用户权限

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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.以为是权限问题，重新授权问题仍然存在

1
2
3
4
5
6
7
8
9
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.创建普通用户直接授权,可以正常查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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.通过角色授权,问题存在

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

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.重建角色问题仍然存在

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
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的

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
# 把视图 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

1
2
3
4
5
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

1
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改为角色的名字

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
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