如何基于数据库名称为PSQL用户批量程序化授予权限

主流数据库（如PostgreSQL、MySQL）本身不支持直接用x_*这类通配符批量授权数据库权限，但可以通过程序化脚本或自动触发器实现需求：

1. PostgreSQL实现方式 #

一次性批量授权 #

编写PL/pgSQL脚本，自动筛选x_前缀的数据库并执行授权：

DO $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN SELECT datname FROM pg_database WHERE datname LIKE 'x_%' LOOP
        EXECUTE 'GRANT ALL PRIVILEGES ON DATABASE ' || quote_ident(rec.datname) || ' TO user_x;';
    END LOOP;
END $$;

新增数据库自动授权 #

创建数据库事件触发器，实现新增x_前缀数据库时自动授权：

1. 先创建触发器函数：

CREATE OR REPLACE FUNCTION grant_x_db_privileges()
RETURNS event_trigger AS $$
DECLARE
    obj RECORD;
BEGIN
    FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands() WHERE command_tag = 'CREATE DATABASE' AND object_identity LIKE 'x_%' LOOP
        EXECUTE 'GRANT ALL PRIVILEGES ON DATABASE ' || quote_ident(obj.object_identity) || ' TO user_x;';
    END LOOP;
END $$ LANGUAGE plpgsql;

2. 创建事件触发器：

CREATE EVENT TRIGGER grant_x_db_trigger
ON ddl_command_end
WHEN TAG IN ('CREATE DATABASE')
EXECUTE FUNCTION grant_x_db_privileges();

2. MySQL实现方式 #

一次性批量授权 #

先查询生成授权语句，再执行：

SELECT CONCAT('GRANT ALL PRIVILEGES ON `', SCHEMA_NAME, '`.* TO ''user_x''@''localhost'';')
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME LIKE 'x_%';

将查询结果的语句复制执行即可完成批量授权。

新增数据库自动授权 #

结合事件调度器定时检查并授权：

1. 开启事件调度器：

SET GLOBAL event_scheduler = ON;

2. 创建存储过程：

DELIMITER //
CREATE PROCEDURE grant_x_db_privileges()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE db_name VARCHAR(255);
    DECLARE cur CURSOR FOR SELECT SCHEMA_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME LIKE 'x_%';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO db_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET @sql = CONCAT('GRANT ALL PRIVILEGES ON `', db_name, '`.* TO ''user_x''@''localhost'';');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;
    CLOSE cur;
END //
DELIMITER ;

3. 创建定时事件（示例为每小时执行一次）：

CREATE EVENT grant_x_db_event
ON SCHEDULE EVERY 1 HOUR
DO CALL grant_x_db_privileges();

注意事项 #

• 执行以上操作需拥有GRANT、CREATE EVENT等管理员级权限
• 脚本需根据实际数据库环境调整（如MySQL的用户主机地址）
• 自动触发的功能需确保服务重启后依然生效（如MySQL需在配置文件中设置event_scheduler=ON）

内容的提问来源于stack exchange，提问作者Martí Coll Risco