视图授权

一、准备工作​​

1. ​​登录MySQL服务器​​

   mysql -u root -p # 使用管理员账户登录

2. ​​确认视图存在​​

   SELECT table_name 
   FROM information_schema.views 
   WHERE table_schema = 'ccsk' AND table_name LIKE 'scyf_%';

---

​​二、创建用户并授权（分场景）​​

​​场景1：为单个IP创建用户并授权视图​​

-- 1. 创建用户（IP限制为10.77.49.11）
CREATE USER IF NOT EXISTS 'fxyp'@'10.77.49.11' IDENTIFIED BY 'StrongPass123!';

-- 2. 授权单个视图
GRANT SELECT ON `ccsk`.`scyf_address_info` TO 'fxyp'@'10.77.49.11';

-- 3. 或授权所有scyf_开头的视图（需存储过程，见下文）
FLUSH PRIVILEGES;

​​场景2：为多个IP批量授权（如11/12/13）​​

 

-- 1. 创建多个用户（相同用户名，不同IP）
CREATE USER IF NOT EXISTS 'fxyp'@'10.77.49.11' IDENTIFIED BY 'Pass111';
CREATE USER IF NOT EXISTS 'fxyp'@'10.77.49.12' IDENTIFIED BY 'Pass222';
CREATE USER IF NOT EXISTS 'fxyp'@'10.77.49.13' IDENTIFIED BY 'Pass333';

-- 2. 使用存储过程批量授权所有scyf_视图
DELIMITER //
CREATE PROCEDURE grant_scyf_views_to_ips()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE view_name VARCHAR(64);
  
  DECLARE cur CURSOR FOR 
    SELECT table_name 
    FROM information_schema.views 
    WHERE table_schema = 'ccsk' AND table_name LIKE 'scyf_%';
  
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  
  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO view_name;
    IF done THEN LEAVE read_loop; END IF;
    
    -- 对三个IP分别授权
    SET @sql1 = CONCAT('GRANT SELECT ON `ccsk`.`', view_name, '` TO ''fxyp''@''10.77.49.11''');
    SET @sql2 = CONCAT('GRANT SELECT ON `ccsk`.`', view_name, '` TO ''fxyp''@''10.77.49.12''');
    SET @sql3 = CONCAT('GRANT SELECT ON `ccsk`.`', view_name, '` TO ''fxyp''@''10.77.49.13''');
    
    PREPARE stmt FROM @sql1; EXECUTE stmt; DEALLOCATE PREPARE stmt;
    PREPARE stmt FROM @sql2; EXECUTE stmt; DEALLOCATE PREPARE stmt;
    PREPARE stmt FROM @sql3; EXECUTE stmt; DEALLOCATE PREPARE stmt;
  END LOOP;
  CLOSE cur;
END //
DELIMITER ;

-- 3. 执行存储过程
CALL grant_scyf_views_to_ips();
FLUSH PRIVILEGES;

​​场景3：允许所有IP访问（测试环境用）​​

CREATE USER 'fxyp'@'%' IDENTIFIED BY 'Password123';
GRANT SELECT ON `ccsk`.`scyf_%` TO 'fxyp'@'%';  -- 通配符需逐视图授权（MySQL限制）
FLUSH PRIVILEGES;

---

​​三、验证授权结果​​

1. ​​检查用户权限​​

   SHOW GRANTS FOR 'fxyp'@'10.77.49.11';

   ​​正确输出示例​​：

   GRANT USAGE ON *.* TO 'fxyp'@'10.77.49.11'
   GRANT SELECT ON `ccsk`.`scyf_address_info` TO 'fxyp'@'10.77.49.11'

2. ​​实际连接测试​​

   mysql -u fxyp -p -h 10.77.49.11  # 从客户端登录

    

   USE ccsk;
   SELECT * FROM scyf_address_info LIMIT 1;  -- 应成功
   SELECT * FROM other_table;               -- 应报错

---

​​四、权限维护与撤销​​

​​1. 新增授权​​

-- 为已有用户新增视图权限
GRANT SELECT ON `ccsk`.`scyf_new_view` TO 'fxyp'@'10.77.49.11';
FLUSH PRIVILEGES;

​​2. 撤销权限​​

-- 撤销单个视图权限
REVOKE SELECT ON `ccsk`.`scyf_address_info` FROM 'fxyp'@'10.77.49.11';

-- 删除用户
DROP USER IF EXISTS 'fxyp'@'10.77.49.11';
FLUSH PRIVILEGES;

---

​​五、故障排查表​​

问题现象	可能原因	解决方案
ERROR 1142	无权限	检查 SHOW GRANTS 并补授权
ERROR 1356	视图不存在	确认视图名称拼写
ERROR 1410	用户不存在	先执行 CREATE USER
连接超时	防火墙/网络问题	检查 telnet 10.77.49.11 3306

---

​​六、安全建议​​

1. ​​密码策略​​

   ALTER USER 'fxyp'@'10.77.49.11' IDENTIFIED BY '新复杂密码';

2. ​​权限监控​​

   -- 定期检查用户权限
   SELECT * FROM mysql.user WHERE User = 'fxyp';

3. ​​日志记录​​

   # 在my.cnf中启用审计
   [mysqld]
   plugin-load = audit_log.so
   audit_log_format = JSON

    

    

---

通过以上流程，您可完成从创建用户到视图授权的全生命周期管理，兼顾安全性与灵活性。