MariaDB视图动态WHERE条件实现及普通用户数据库权限配置问题

MySQL/MariaDB 原生没有直接在视图WHERE子句中判断当前操作类型的语法，你可以通过以下两种方案实现需求：

方案1：单视图动态判断（适合语句格式规范的场景） #

通过查询当前连接的执行语句类型动态调整过滤条件，视图创建语句如下：

CREATE SQL SECURITY INVOKER VIEW contents(
  id,
  title
)
AS
SELECT 
   mytable.id as id,
   mytable.title as title
FROM mytable 
WHERE 
  -- 匹配SELECT操作时无过滤，其他操作仅返回自有数据
  SUBSTRING_INDEX(USER(), '@', 1) = mytable.owner
  OR EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.PROCESSLIST 
    WHERE ID = CONNECTION_ID() 
    AND COMMAND = 'Query' 
    AND TRIM(INFO) LIKE 'SELECT%'
  )
WITH CASCADED CHECK OPTION;

注意：需要给使用视图的用户授予PROCESS权限，才可查询PROCESSLIST系统表。如果SELECT语句前缀带注释，需要调整LIKE匹配规则。

方案2：双视图拆分权限（最稳妥兼容性最高） #

拆分读写为两个独立视图，分别授予对应权限：

1. 创建读视图（全量数据，仅授予SELECT权限）

CREATE VIEW contents_read(
  id,
  title
)
AS
SELECT id, title FROM mytable;

2. 创建写视图（仅返回自有数据，授予INSERT/UPDATE/DELETE权限）

CREATE SQL SECURITY INVOKER VIEW contents_write(
  id,
  title
)
AS
SELECT id, title FROM mytable
WHERE SUBSTRING_INDEX(USER(), '@', 1) = mytable.owner
WITH CASCADED CHECK OPTION;

用户查询时使用contents_read，执行写操作时使用contents_write即可满足需求。

该需求可以实现，根据你使用的MariaDB版本有两种可选方案：

方案1：前缀匹配授权（兼容所有版本，无需额外配置） #

要求用户创建的数据库统一使用固定前缀（比如helen_），直接授予该前缀名下所有库的全部权限即可，用户新建的符合前缀规则的库会自动继承权限：

-- 授予全局建库权限
GRANT CREATE ON *.* TO 'helen'@'你的连接host';
-- 授予所有helen_开头的库的全部权限
GRANT ALL PRIVILEGES ON `helen_%`.* TO 'helen'@'你的连接host';
FLUSH PRIVILEGES;

方案2：DDL触发器自动授权（适合无库名前缀要求的场景，需要MariaDB 10.0.5+） #

在系统库创建DDL触发器，当用户创建新库时自动分配权限：

DELIMITER //
CREATE TRIGGER auto_grant_db_priv AFTER CREATE ON *.*
FOR EACH ROW
BEGIN
  IF USER() = 'helen@你的连接host' THEN
    SET @grant_sql = CONCAT('GRANT ALL PRIVILEGES ON `', NEW.SCHEMA_NAME, '`.* TO ''helen''@''你的连接host''');
    PREPARE stmt FROM @grant_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END IF;
END //
DELIMITER ;

注意：该触发器需要使用root用户创建，同时需要开启log_bin_trust_function_creators参数避免报错。

内容的提问来源于stack exchange，提问作者Helen