【此为"一森咖记"公众号——第33篇文章】
【前言】
最近遇到的一个真实案例：开发前期，Mysql库中有个'root'@'%' 账号，权限为GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'。因前期不规范，开发组前期使用该账号进行项目开发；现系统要试运行，考虑到'root'@'%'权限太高，名字也比较敏感，故创建了一个运维账号'yunwei'@'%'，并对'root'@'%'做了删除操作。
然，'applicator'@'%'查询视图时出现问题指定定义者的用户不存在。该sql中调用了视图，查了很久，最后才发现是视图权限SQL SECURITY { DEFINER | INVOKER 的问题。
本文，将针对MySQL的视图创建时权限：
SQL SECURITY DEFINER|INVOKER ，说道说道。
一、 MySQL视图创建介绍
MySQL中，创建视图的完整语法：
CREATE
[OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
[DEFINER = { user | CURRENT_USER }]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
由上，可以看出，MySQL数据库中的View创建在标准SQL的基础之上做了些扩展，语法变量信息如下：
1. ALGORITHM：指定视图的处理方式，ALGORITHM子句是可选的，它表示使用何种算法来处理视图。并不属于标准SQL的一部分，而是MySQL对标准SQL进行的功能扩展，默认值为UNDEFINED(未定义的)。
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
1) MERGE：将视图的定义和查询视图的语句合并处理；使得视图定义的某一部分取代语句的对应部分；
2) TEMPTABLE：视图的结果将被置于临时表中，而后在该临时表基础上执行查询视图的语句；TEMPTABLE在创建临时表之后、并在完成语句处理之前，能够释放基表上的锁定。与MERGE算法相比，锁定释放的速度更快，这样，使用视图的其他客户端不会被屏蔽过长时间。
3) UNDEFINED：MySQL选择使用哪种算法，一般会首选MERGE，因为MERGE更有效率，TEMPTABLE因使用了临时表，也不支持更新操作。
2. DEFINER：指定视图的创建者/属主
1) MySQL中的对象其实不注重属主，这点与ORACLE数据库极为不同，熟悉MySQL和Oracle的朋友都知道，默认当然就是执行CREATE VIEW语句的CURRENT_USER，但是创建时也可以指定不同的用户做为创建者，或者叫视图持有人。
3. SQL SECURITY：视图查询数据时的安全验证方式，有两处选项：
1) DEFINER：不是指创建者，而是指在创建视图时验证是否有权限访问视图所引用的对象；由definer(定义者)指定的用户的权限来执行命令；
2) INVOKER：指查询视图时，验证查询的用户是否拥有权限访问视图及视图所引用的对象。
划重点：
1) MERGE算法要求视图中的行和基表中的行具有一对一的关系。如果视图包含聚合函数(SUM(), MIN(), MAX(), COUNT()等)、DISTINCT、GROUP BY、HAVING、UNION或UNION ALL、没有基表的引用文字值等结构中的任何一种，将失去一对一的关系，此时必须使用临时表取而代之。
4. 最后一行[WITH [CASCADED | LOCAL] CHECK OPTION]
1) 该选项中的 CASCADED为默认值， LOCAL CHECK OPTION用于在可更新视图中防止插入或更新行。由于此选项一般不使用，因此不再赘述，具体信息请参考MySQL官方网站上的相关信息
铺垫这么多，想说明个嘛？
问题现象
删除'root'@'%'后，应用账号'applicator'@'%'查询一个view，报错The user specified as a definer ('root'@'%') does not exist。经查证是create view时指定了DEFINER和SQL SECURITY DEFINER的缘故。
问题来了：
DEFINER和SQL SECURITY DEFINER是个啥？有啥用？
DEFINER和SQL SECURITY DEFINER/INVOKER是MySQL中视图安全验证方式，核心思想还是权限 ，本质为用户是否有足够的权限去访问想访问到的数据。
这里将探讨查询视图时，对视图所引用的基表的DEFINER和SQL SECURITY DEFINER/INVOKER权限验证。
二、实验验证
1. 使用'root'@'%'创建库ts_db_02，使用如下方式
[root@ethan_mysql ~]# mysql -uroot -pmysql -h 10.10.178.112 -P 3308
mysql> create database ts_db_02;
Query OK, 1 row affected (0.03 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| ts_db_01 |
| ts_db_02 |
+--------------------+
6 rows in set (0.00 sec)
mysql> use ts_db_02;
Database changed
2. 查看'root'@'%'的权限
mysql> show grants for 'root'@'%';
+-------------------------------------------+
| Grants for root@% |
+-------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' |
+-------------------------------------------+
1 row in set (0.00 sec)
3. 因ALL PRIVILEGES ON *.*，根据前面SQL SECURITY参数的介绍，可以在ts_db_02中创建对ts_db_01的视图，引用账号'root'@'%'，在ts_db_01中按照不同的方式创建两个视图：
mysql> create sql security definer view ts02_v_d as select * from ts_db_01.employee;
Query OK, 0 rows affected (0.04 sec)
mysql> create sql security invoker view ts02_v_i as select * from ts_db_01.employee;
Query OK, 0 rows affected (0.05 sec)
mysql> show full tables;
+--------------------+------------+
| Tables_in_ts_db_02 | Table_type |
+--------------------+------------+
| ts02_v_d | VIEW |
| ts02_v_i | VIEW |
+--------------------+------------+
2 rows in set (0.00 sec)
mysql> show create view ts_db_02.ts02_v_d \G;
*************************** 1. row ***************************
View: ts02_v_d
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `ts_db_02`.`ts02_v_d` AS select `ts_db_01`.`employee`.`HRID` AS `HRID`,`ts_db_01`.`employee`.`EMPLOYEE_ID` AS `EMPLOYEE_ID`,`ts_db_01`.`employee`.`EMPLOYEE_NAME` AS `EMPLOYEE_NAME`,`ts_db_01`.`employee`.`EMPLOYEE_SHORT` AS `EMPLOYEE_SHORT`,`ts_db_01`.`employee`.`SEX` AS `SEX`,`ts_db_01`.`employee`.`DEPT_CODE` AS `DEPT_CODE` from `ts_db_01`.`employee`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)
mysql> show create view ts_db_02.ts02_v_i \G;
*************************** 1. row ***************************
View: ts02_v_i
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY INVOKER VIEW `ts_db_02`.`ts02_v_i` AS select `ts_db_01`.`employee`.`HRID` AS `HRID`,`ts_db_01`.`employee`.`EMPLOYEE_ID` AS `EMPLOYEE_ID`,`ts_db_01`.`employee`.`EMPLOYEE_NAME` AS `EMPLOYEE_NAME`,`ts_db_01`.`employee`.`EMPLOYEE_SHORT` AS `EMPLOYEE_SHORT`,`ts_db_01`.`employee`.`SEX` AS `SEX`,`ts_db_01`.`employee`.`DEPT_CODE` AS `DEPT_CODE` from `ts_db_01`.`employee`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)
3. 使用账号`root`@`localhost`新建一个用户 'app_user'@'%' ，授予查看ts_db_02库视图的权限
注意：'app_user'@'%'并没有访问表ts_db_01.employee的权限
mysql> grant select,show view on ts_db_02.* to 'app_user'@'%' identified by 'mysql';
Query OK, 0 rows affected, 1 warning (0.03 sec)
4. 查看'app_user'@'%'用户权限
mysql> show grants for 'app_user'@'%';
+-----------------------------------------------------------+
| Grants for app_user@% |
+-----------------------------------------------------------+
| GRANT USAGE ON *.* TO 'app_user'@'%' |
| GRANT SELECT, SHOW VIEW ON `ts_db_02`.* TO 'app_user'@'%' |
+-----------------------------------------------------------+
2 rows in set (0.00 sec)
5. 使用新创建的'app_user'@'%'用户登录执行操作：
[root@ethan_mysql mysqldata]# mysql -uapp_user -pmysql -h 10.10.178.112 -P 3308 -D ts_db_02
mysql> show full tables;
+--------------------+------------+
| Tables_in_ts_db_02 | Table_type |
+--------------------+------------+
| ts02_v_d | VIEW |
| ts02_v_i | VIEW |
+--------------------+------------+
2 rows in set (0.00 sec)
mysql> select * from ts02_v_d;
+--------+-------------+---------------+----------------+------+--------------------+
| HRID | EMPLOYEE_ID | EMPLOYEE_NAME | EMPLOYEE_SHORT | SEX | DEPT_CODE |
+--------+-------------+---------------+----------------+------+--------------------+
| 334578 | 666 | ethan | discipline | male | application center |
+--------+-------------+---------------+----------------+------+--------------------+
1 row in set (0.00 sec)
可以看到，执行没有问题。
简单推导下过程：
ts02_v_d创建时使用的是SQL SECURITY DEFINER，其含义只验证创建视图ts02_v_d时的创建者是否有权限访问视图所引用的数据ts_db_01.employee，因为ts02_v_d创建使用的是'root'@'%'，其权限是all privileges on *.*，所以创建视图过程成功；
又因为用户'app_user'@'%'有如下权限，所以当然可以查看视图ts_db_02.ts02_v_d。
GRANT SELECT, SHOW VIEW ON `ts_db_02`.* TO 'app_user'@'%'
6. 接下来再访问invoker权限定义的视图
mysql> select * from ts02_v_i;
ERROR 1356 (HY000): View 'ts_db_02.ts02_v_i' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
执行失败。
推导过程：
ts02_v_d创建时使用的是SQL SECURITY INVOKER，其含义是查询视图时，验证查询的用户是否拥有权限访问视图及视图所引用的对象。因为'app_user'@'%'虽然有访问视图的权限，但没有访问视图所引用对象ts_db_01.employee表的权限，所以不能执行查看视图ts_db_02.ts02_v_i。
那怎么才能执行ts_db_02.ts02_v_i？
很简单，给用户赋予访问视图所引用的对象即可。
mysql> grant select on ts_db_01.employee to 'app_user'@'%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 92
Current database: *** NONE ***
Query OK, 0 rows affected (0.02 sec)
7. 再次使用'app_user'@'%';查看视图ts_db_02.ts02_v_i
[root@ethan_mysql mysqldata]# mysql -uapp_user -pmysql -h 10.10.178.112 -P 3308 -D ts_db_02
mysql> select * from ts02_v_i;
+--------+-------------+---------------+----------------+------+--------------------+
| HRID | EMPLOYEE_ID | EMPLOYEE_NAME | EMPLOYEE_SHORT | SEX | DEPT_CODE |
+--------+-------------+---------------+----------------+------+--------------------+
| 334578 | 666 | ethan | discipline | male | application center |
+--------+-------------+---------------+----------------+------+--------------------+
1 row in set (0.00 sec)
可以看到，执行成功。
模拟文初案例，删除用户'root'@'%'后，会发生什么？
1. 删除用户'root'@'%'
[root@ethan_mysql ~]# mysql -uroot -pmysql --socket=/mysqldata/3308/tmp/mysql.sock
mysql> drop user 'root'@'%';
Query OK, 0 rows affected (0.03 sec)
mysql> select host,user from mysql.user;
+-----------+---------------+
| host | user |
+-----------+---------------+
| % | ethan_yang |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+-----------+---------------+
4 rows in set (0.00 sec)
2. 使用'app_user'@'%'用户登录执行查看视图操作
[root@ethan_mysql]#mysql -uapp_user -pmysql -h 10.10.178.112 -P 3308 -D ts_db_02
3. 查看视图ts02_v_i
mysql> select * from ts02_v_i;
+--------+-------------+---------------+----------------+------+--------------------+
| HRID | EMPLOYEE_ID | EMPLOYEE_NAME | EMPLOYEE_SHORT | SEX | DEPT_CODE |
+--------+-------------+---------------+----------------+------+--------------------+
| 334578 | 666 | ethan | discipline | male | application center |
+--------+-------------+---------------+----------------+------+--------------------+
1 row in set (0.00 sec)
mysql> show create view ts02_v_i \G
*************************** 1. row ***************************
View: ts02_v_i
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY INVOKER VIEW `ts02_v_i` AS select `ts_db_01`.`employee`.`HRID` AS `HRID`,`ts_db_01`.`employee`.`EMPLOYEE_ID` AS `EMPLOYEE_ID`,`ts_db_01`.`employee`.`EMPLOYEE_NAME` AS `EMPLOYEE_NAME`,`ts_db_01`.`employee`.`EMPLOYEE_SHORT` AS `EMPLOYEE_SHORT`,`ts_db_01`.`employee`.`SEX` AS `SEX`,`ts_db_01`.`employee`.`DEPT_CODE` AS `DEPT_CODE` from `ts_db_01`.`employee`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)
执行成功，此刻虽然没有`root`@`%`，但视图ts02_v_i已经创建，且'app_user'@'%'访问ts02_v_i所需要的视图权限和视图所引用表的select权限均有，所以命令可以执行。
3. 查看视图ts02_v_d
mysql> select * from ts02_v_d;
ERROR 1045 (28000): Access denied for user 'app_user'@'%' (using password: YES)
mysql> show create view ts02_v_d \G
*************************** 1. row ***************************
View: ts02_v_d
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `ts02_v_d` AS select `ts_db_01`.`employee`.`HRID` AS `HRID`,`ts_db_01`.`employee`.`EMPLOYEE_ID` AS `EMPLOYEE_ID`,`ts_db_01`.`employee`.`EMPLOYEE_NAME` AS `EMPLOYEE_NAME`,`ts_db_01`.`employee`.`EMPLOYEE_SHORT` AS `EMPLOYEE_SHORT`,`ts_db_01`.`employee`.`SEX` AS `SEX`,`ts_db_01`.`employee`.`DEPT_CODE` AS `DEPT_CODE` from `ts_db_01`.`employee`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set, 1 warning (0.00 sec)
执行成功，此刻虽然没有`root`@`%`，但视图ts02_v_d已经创建，访问机制使用的是SQL SECURITY DEFINER，需使用创建视图时的DEFINER（`root`@`%`）来访问视图。因此时`root`@`%`删除，所以视图ts02_v_d不能被访问。
HK
怎么破? 三种方法
方式1：添加会用户`root`@`%`，并赋原来的权限。
`root`@`%`的删除操作，就是为了防止高权限用户。故此方案被否。
方式2：把SQL SECURITY 的DEFINER改为invoker
mysql> alter ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY invoker VIEW ts_db_02.ts02_v_d AS select * from ts_db_01.employee;
Query OK, 0 rows affected, 1 warning (0.05 sec)
mysql> select * from ts02_v_d;
+--------+-------------+---------------+----------------+------+--------------------+
| HRID | EMPLOYEE_ID | EMPLOYEE_NAME | EMPLOYEE_SHORT | SEX | DEPT_CODE |
+--------+-------------+---------------+----------------+------+--------------------+
| 334578 | 666 | ethan | discipline | male | application center |
+--------+-------------+---------------+----------------+------+--------------------+
1 row in set (0.00 sec)
方式3：把DEFINER=`root`@`%` 改为DEFINER=`app_user`@`%`
mysql> alter ALGORITHM=UNDEFINED DEFINER=`app_user`@`%` SQL SECURITY DEFINER VIEW ts_db_02.ts02_v_d AS select * from ts_db_01.employee;
Query OK, 0 rows affected (0.10 sec)
mysql> select * from ts02_v_d;
+--------+-------------+---------------+----------------+------+--------------------+
| HRID | EMPLOYEE_ID | EMPLOYEE_NAME | EMPLOYEE_SHORT | SEX | DEPT_CODE |
+--------+-------------+---------------+----------------+------+--------------------+
| 334578 | 666 | ethan | discipline | male | application center |
+--------+-------------+---------------+----------------+------+--------------------+
1 row in set (0.00 sec)
【结论】
1. definer是在定义对象是判断是否有权限，只要创建的用户有权限，那么创建就可以成功，且所有有权限查询该视图的用户也能够成功执行查询语句 ，不管是否拥有该视图所引用对象的权限；
2. invoker是指在查询时验证用户是否有权限执行操作，当然创建时也会判断，如果创建的用户没有视图所引用表对象的访问权限，那创建都会失败。
3. 熟悉oracle的朋友可能联想到了，MySQL的definer/invoker涉及理念与ORACLE中的定义者权限和调用者权限过程非常类似。
最近的HK问题，针对HK游行的娃，
最近看到一张赞图挺适用，这里放下：
作者：ethan_yang
公众号：一森咖记
格言：认清大势，亦有一份小坚持
欢迎关注个人微信公众号；
长按以下二维码或公众号搜索“一森咖记”
【参考】
https://blog.csdn.net/zhangxueleishamo/article/details/83056598
【参考】
http://blog.itpub.net/7607759/viewspace-706431/
往期精彩文章
=====================================
