面试题：MySQL如何通过存储过程来动态管理用户权限并增强安全性

Question

请设计一个MySQL存储过程，实现根据传入的用户名、数据库名和权限类型（如SELECT、INSERT等），动态地为指定用户在指定数据库上授予相应权限。同时要考虑到安全性，例如防止SQL注入等问题，详细描述实现思路并给出关键代码。

Answer 1

实现思路

1. 输入验证：对传入的用户名、数据库名和权限类型进行有效性检查，确保它们符合预期格式，避免非法字符导致潜在的SQL注入风险。
2. 构建安全SQL语句：使用预编译语句（prepared statements）来构建授予权限的SQL语句。这样可以将变量与SQL语句本身分离，数据库会对变量进行转义和验证，从而有效防止SQL注入。
3. 执行SQL语句：执行构建好的预编译SQL语句，完成权限授予操作。

关键代码

DELIMITER //

CREATE PROCEDURE GrantUserPermissions(
    IN p_username VARCHAR(100),
    IN p_database_name VARCHAR(100),
    IN p_permission_type VARCHAR(20)
)
BEGIN
    DECLARE stmt VARCHAR(200);
    -- 检查用户名、数据库名和权限类型是否为空
    IF p_username IS NULL OR p_database_name IS NULL OR p_permission_type IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '用户名、数据库名和权限类型均不能为空';
    END IF;
    -- 构建预编译SQL语句
    SET stmt = CONCAT('GRANT ', p_permission_type,'ON ', p_database_name,'.* TO \'', p_username,'\'@\'%\'');
    -- 准备并执行SQL语句
    PREPARE grant_stmt FROM stmt;
    EXECUTE grant_stmt;
    DEALLOCATE PREPARE grant_stmt;
END //

DELIMITER ;

上述代码定义了一个名为GrantUserPermissions的存储过程，接受用户名、数据库名和权限类型作为参数。存储过程首先进行输入验证，然后构建授予权限的SQL语句，使用预编译语句来防止SQL注入，并最终执行该语句。@'%'表示该用户可以从任何主机连接到数据库。如果需要限制主机访问，可以替换为具体的主机名或IP地址。