MySQL视图权限：说说那点事_SQL SECURITY DEFINER | INVOKER

DN金猿

于 2024-05-12 11:54:22 发布

阅读量38

点赞数

分类专栏： mysql、oracle及其他可持久化数据库文章标签： mysql sql adb

原文链接：https://www.modb.pro/db/80341

版权

mysql、oracle及其他可持久化数据库专栏收录该内容

22 篇文章 0 订阅

订阅专栏

【前言】

最近遇到的一个真实案例：开发前期，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)

4. 使用账号`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)

5. 查看'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)

6. 使用新创建的'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'@'%'

7. 接下来再访问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)

8. 再次使用'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权限均有，所以命令可以执行。

4. 查看视图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不能被访问。

怎么破? 三种方法

方式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中的定义者权限和调用者权限过程非常类似。

DN金猿

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL视图权限：说说那点事_SQL SECURITY DEFINER | INVOKER

LOCAL CHECK OPTION用于在可更新视图中防止插入或更新行。由于此选项一般不使用，因此不再赘述，具体信息请参考MySQL官方网站上的相关信息铺垫这么多，想说明个嘛？问题现象删除'root'@'%'后，应用账号'applicator'@'%'查询一个view，报错The user specified as a definer ('root'@'%') does not exist。经查证是create view时指定了DEFINER和SQL SECURITY DEFINER的缘故。
复制链接

扫一扫