MySql5.6 关于视图访问权限问题记录

问题描述

使用 zstack或root账号访问视图 view3 出现

[root@172-26-52-170 mariadb]# mysql -uzstack -pzstack.password zstack -e "select * from view3"
ERROR 1045 (28000) at line 1: Access denied for user 'zstack'@'localhost' (using password: YES)
[root@172-26-52-170 mariadb]# mysql -uroot -pzstack.mysql.password zstack -e "select * from view3"
ERROR 1449 (HY000) at line 1: The user specified as a definer ('zstack'@'%') does not exist

我限定了zstack用户 只能使用172.26.52.170，localhost，127.0.0.1 访问。

MariaDB [zstack]> select user, host from mysql.user order by user;
+-------------+---------------+
| user        | host          |
+-------------+---------------+
| root        | localhost     |
| root        | 127.0.0.1     |
| root        | ::1           |
| root        | %             |
| root        | 172.26.52.170 |
| zops        | localhost     |
| zops        | %             |
| zstack      | 172.26.52.170 |
| zstack      | localhost     |
| zstack      | 127.0.0.1     |
| zstack_rest | %             |
| zstack_ui   | localhost     |
| zstack_ui   | 172.26.52.170 |
| zstack_ui   | 127.0.0.1     |
+-------------+---------------+

同时我在修改限定前创建了一个视图 view3，然后 definer 是 zstack@%

MariaDB [zstack]> select table_name, definer from information_schema.VIEWS;
+-----------------------------------+--------------------+
| table_name                        | definer            |
+-----------------------------------+--------------------+
| BackupStorageVO                   | root@172.26.52.170 |
| CdpPolicyVO                       | root@172.26.52.170 |
| CephOsdGroupHistoricalUsageVO     | root@172.26.52.170 |
| ClusterVO                         | root@172.26.52.170 |
| DiskOfferingVO                    | root@172.26.52.170 |
| HostVO                            | root@172.26.52.170 |
| ImageVO                           | root@172.26.52.170 |
| InstanceOfferingVO                | root@172.26.52.170 |
| IpRangeVO                         | root@172.26.52.170 |
| L2NetworkVO                       | root@172.26.52.170 |
| L3NetworkVO                       | root@172.26.52.170 |
| LocalStorageHostHistoricalUsageVO | root@172.26.52.170 |
| PrimaryStorageHistoricalUsageVO   | root@172.26.52.170 |
| PrimaryStorageVO                  | root@172.26.52.170 |
| ScsiLunVO                         | root@172.26.52.170 |
| VmInstanceVO                      | root@172.26.52.170 |
| VolumeSnapshotTreeVO              | root@172.26.52.170 |
| VolumeSnapshotVO                  | root@172.26.52.170 |
| VolumeVO                          | root@172.26.52.170 |
| ZoneVO                            | root@172.26.52.170 |
| view3                             | zstack@%           |
+-----------------------------------+--------------------+
21 rows in set (0.015 sec)

解决方案

重新创建视图并且指定 definer 。
手段很多比如
https://stackoverflow.com/questions/10169960/mysql-error-1449-the-user-specified-as-a-definer-does-not-exist
https://forums.mysql.com/read.php?10,429197,429197#msg-429197
https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html
差不多都是一个意思，然后我遇到的情况有点特殊因为要批量修改以前所有view 的 definer
我写了个存储过程来重新创建 view

CREATE PROCEDURE zstack.alterViewDefiner()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE view_name VARCHAR(256);
    DECLARE view_def LONGTEXT;

    DECLARE cur CURSOR FOR SELECT TABLE_NAME, VIEW_DEFINITION FROM information_schema.VIEWS WHERE DEFINER = 'zstack@%';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO view_name, view_def;

        IF done THEN
            LEAVE read_loop;
        END IF;
      
        SET @sql = CONCAT('CREATE OR REPLACE VIEW ', view_name, ' AS ', view_def, ';');
        -- 我开始是准备这么写的直接指定definer 但是这样语句执行不了，遂放弃。不指定因为是 root 账号执行 definer 会是 root@% 没搞明白怎么才是 root@ip 的形式
        -- SET @sql = CONCAT('CREATE OR REPLACE DEFINER = 'root'@'localhost' VIEW ', view_name, ' AS ', view_def, ';');
        -- SELECT @sql AS Generated_SQL;

        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

    END LOOP;

    CLOSE cur;
END

没搞懂的问题

1. 明明 zstack@localhost 是有所有访问权限的。仅仅因为 mysql.user 表中 zstack@% 被删除 搞得无论是root 还是 zstack 都无法访问 view3 是我真的不能理解的。
2. mysql 真的好难用，view 不能直接修改 definer。存储过程中重新创建 view 也不能指定definer
3. 然后 view3 的 definer 设置为 root@%，root 和 zstack 账号就都能访问了。也没找到说法

MariaDB [zstack]> SHOW GRANTS FOR 'zstack'@'localhost'\G;
*************************** 1. row ***************************
Grants for zstack@localhost: GRANT USAGE ON *.* TO `zstack`@`localhost` IDENTIFIED BY PASSWORD '*0EE5DC1F2BF5512284EDB05976CDD8BE4CFBD348'
*************************** 2. row ***************************
Grants for zstack@localhost: GRANT ALL PRIVILEGES ON `zstack`.* TO `zstack`@`localhost`
*************************** 3. row ***************************
Grants for zstack@localhost: GRANT ALL PRIVILEGES ON `zstack_rest`.* TO `zstack`@`localhost`
3 rows in set (0.000 sec)

ERROR: No query specified