问题描述
使用 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 | [email protected] |
| CdpPolicyVO | [email protected] |
| CephOsdGroupHistoricalUsageVO | [email protected] |
| ClusterVO | [email protected] |
| DiskOfferingVO | [email protected] |
| HostVO | [email protected] |
| ImageVO | [email protected] |
| InstanceOfferingVO | [email protected] |
| IpRangeVO | [email protected] |
| L2NetworkVO | [email protected] |
| L3NetworkVO | [email protected] |
| LocalStorageHostHistoricalUsageVO | [email protected] |
| PrimaryStorageHistoricalUsageVO | [email protected] |
| PrimaryStorageVO | [email protected] |
| ScsiLunVO | [email protected] |
| VmInstanceVO | [email protected] |
| VolumeSnapshotTreeVO | [email protected] |
| VolumeSnapshotVO | [email protected] |
| VolumeVO | [email protected] |
| ZoneVO | [email protected] |
| 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
没搞懂的问题
- 明明 zstack@localhost 是有所有访问权限的。仅仅因为 mysql.user 表中 zstack@% 被删除 搞得无论是root 还是 zstack 都无法访问 view3 是我真的不能理解的。
- mysql 真的好难用,view 不能直接修改 definer。存储过程中重新创建 view 也不能指定definer
- 然后 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
标签:root,MySql5.6,52.170,视图,172.26,权限,zstack,localhost,view
From: https://www.cnblogs.com/linma/p/18196429