MySQL中创建视图以及存储过程中或视图SQL SECURITY含义

1. 语法

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] [DEFINER = user] [SQL SECURITY { DEFINER | INVOKER }] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]

在创建view或者producer，function中都有 SQL SECURITY认证。

 

2. DEFINER 指定对象所有者

如创建视图，默认是当前谁创建视图归谁。

默认是当前用户，也可以手动指定所有者

3. SQL SECURITY 指定视图（存储过程）查询时的验证方式

有2种方式：

- definer 默认，创建视图时验证是否有权限访问视图所引用的数据；

- invoker 指查询视图时，验证查询的用户是否拥有权限访问视图及视图所引用的对象；

说明：假设当前root用户创建了一个test用户，

同时创建一个视图d_view 指向表 d_table，然后授权：

grant select ,show view on db.d_view to test

此时并没有给test授予访问d_table的权限，而只授予test访问view的权限。

- 如果视图指定的是definer，则可以访问d_view 中数据

- 如果视图本身指定invoker，那么因为test没有访问d_table表中数据，那么访问d_view就会报错：

View d_view' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

总结：

直接使用默认definer（不指定SQL SECURITY）

4. ALGORITHM：指定视图的处理方式

有三种选项：

- MERGE：将视图的定义和查询视图的语句合并处理，。

- TEMPTABLE：视图查询的结果保存到临时表，而后在该临时表基础上执行查询视图的语句；

- UNDEFINED：（默认）由MySQL选择使用哪种算法，一般会首选MERGE，因为MERGE更有效率，再说TEMPTABLE也不支持更新操作。

 

常用报错解决

视图本来有效，后面迁移后报错，优先检查view的definer是否在本地有对应的用户，注意user@ip，ip也要对应。