MySQL高级函数之：`CURRENT_USER()` 和 `SESSION_USER()`：其在获取用户身份时的应用。

好的，我们开始今天的讲座，主题是MySQL高级函数 CURRENT_USER() 和 SESSION_USER()，以及它们在获取用户身份时的应用。 这两个函数虽然看起来相似，但实际上在某些情况下会产生不同的结果。 理解它们的差异对于编写安全可靠的MySQL应用至关重要。

一、CURRENT_USER() 和 SESSION_USER() 的基本概念

• CURRENT_USER(): 这个函数返回的是 当前连接 的 认证用户 和 主机名。 也就是说，它返回的是在建立连接时成功通过身份验证的用户，而不是当前会话中执行语句的用户。 重要的是，它反映的是连接建立时确定的身份。

• SESSION_USER(): 这个函数返回的是 当前会话 的 认证用户 和 主机名。 在大多数情况下，它与 CURRENT_USER() 返回相同的值。 然而，当使用 DEFINER 子句创建存储过程或视图时，它们可能会有所不同。

两个函数都返回一个字符串，格式为 'user_name'@'host_name'。 如果用户名或主机名包含特殊字符，它们将被转义。

二、CURRENT_USER() 和 SESSION_USER() 的应用场景

1. 审计和日志记录: 这两个函数可以用于在触发器或存储过程中记录执行操作的用户。 这对于跟踪数据的修改者和识别潜在的安全问题非常有用。

2. 权限控制: 虽然不建议直接在SQL语句中使用这两个函数进行权限控制（应该使用角色和权限系统），但它们可以用于审计权限检查的有效性。

3. 存储过程和视图的定义者: 这是 CURRENT_USER() 和 SESSION_USER() 产生差异的关键场景。 使用 DEFINER 子句可以指定存储过程或视图以特定用户的身份执行。

三、CURRENT_USER() 和 SESSION_USER() 的差异与实例

关键差异在于 DEFINER 子句对 SESSION_USER() 的影响很小，但对 CURRENT_USER() 的影响很大。 让我们通过一些具体的例子来说明：

场景一：普通连接

假设我们以用户 'test_user'@'localhost' 连接到MySQL服务器：

-- 以 test_user@localhost 连接
SELECT CURRENT_USER(), SESSION_USER();

结果：

CURRENT_USER()	SESSION_USER()
‘test_user’@’localhost’	‘test_user’@’localhost’

在这个简单的例子中，CURRENT_USER() 和 SESSION_USER() 返回相同的值。

场景二：使用 DEFINER 的存储过程

现在，我们创建一个存储过程，并使用 DEFINER 子句指定它以 'other_user'@'%' 的身份执行：

-- 创建用户 other_user
CREATE USER 'other_user'@'%' IDENTIFIED BY 'password';

-- 授权 other_user 可以执行所有存储过程
GRANT EXECUTE ON *.* TO 'other_user'@'%';

-- 创建用户 test_user
CREATE USER 'test_user'@'localhost' IDENTIFIED BY 'password';

-- 授权 test_user 创建存储过程
GRANT CREATE ROUTINE ON *.* TO 'test_user'@'localhost';
GRANT EXECUTE ON *.* TO 'test_user'@'localhost';

DELIMITER //

CREATE PROCEDURE `test_procedure`()
DEFINER = 'other_user'@'%'
BEGIN
    SELECT CURRENT_USER(), SESSION_USER();
END //

DELIMITER ;

-- 授权 test_user 执行该存储过程
GRANT EXECUTE ON PROCEDURE `test`.`test_procedure` TO 'test_user'@'localhost';

现在，我们以 'test_user'@'localhost' 的身份连接，并执行该存储过程：

-- 以 test_user@localhost 连接
CALL `test_procedure`();

结果：

CURRENT_USER()	SESSION_USER()
‘other_user’@’%’	‘test_user’@’localhost’

在这个例子中，CURRENT_USER() 返回了 'other_user'@'%'，这是存储过程的定义者。 SESSION_USER() 仍然返回 'test_user'@'localhost'，即当前会话的认证用户。 这就是关键的区别。 当存储过程以 DEFINER 身份运行时，CURRENT_USER() 会反映 DEFINER 的身份，而 SESSION_USER() 保持不变。

场景三：使用 DEFINER 的视图

视图的行为与存储过程类似。 创建一个使用 DEFINER 的视图：

-- 创建视图
CREATE VIEW `test_view` AS
SELECT CURRENT_USER(), SESSION_USER();

ALTER VIEW `test_view` DEFINER = 'other_user'@'%';

-- 授权 test_user 可以访问 test_view
GRANT SELECT ON `test`.`test_view` TO 'test_user'@'localhost';

以 'test_user'@'localhost' 的身份连接，并查询该视图：

-- 以 test_user@localhost 连接
SELECT * FROM `test_view`;

结果：

CURRENT_USER()	SESSION_USER()
‘other_user’@’%’	‘test_user’@’localhost’

与存储过程一样，CURRENT_USER() 反映了视图的定义者 'other_user'@'%'，而 SESSION_USER() 保持为 'test_user'@'localhost'。

场景四：不使用 DEFINER 的存储过程

如果我们创建一个不使用 DEFINER 子句的存储过程，那么 CURRENT_USER() 和 SESSION_USER() 将始终返回相同的值：

DELIMITER //

CREATE PROCEDURE `test_procedure2`()
BEGIN
    SELECT CURRENT_USER(), SESSION_USER();
END //

DELIMITER ;

-- 授权 test_user 执行该存储过程
GRANT EXECUTE ON PROCEDURE `test`.`test_procedure2` TO 'test_user'@'localhost';

以 'test_user'@'localhost' 的身份连接，并执行该存储过程：

-- 以 test_user@localhost 连接
CALL `test_procedure2`();

结果：

CURRENT_USER()	SESSION_USER()
‘test_user’@’localhost’	‘test_user’@’localhost’

在这种情况下，CURRENT_USER() 和 SESSION_USER() 都返回 'test_user'@'localhost'。

四、安全考虑

理解 CURRENT_USER() 和 SESSION_USER() 的差异对于编写安全代码至关重要。 以下是一些需要考虑的安全问题：

1. 避免在存储过程或视图中使用 SQL SECURITY DEFINER 进行权限控制: 虽然可以使用 SQL SECURITY DEFINER 和 CURRENT_USER() 在存储过程中进行一些权限检查，但这通常不是最佳实践。 更好的方法是使用角色和权限系统来控制用户对数据库对象的访问。

2. 注意 DEFINER 的权限: 如果一个存储过程或视图的 DEFINER 拥有过多的权限，那么任何能够调用该存储过程或查询该视图的用户都可能间接获得这些权限。 因此，应该谨慎地选择 DEFINER，并确保它只拥有执行所需操作的最小权限集。

3. 审计 DEFINER 的使用: 定期审计数据库中存储过程和视图的 DEFINER 设置，以确保它们仍然符合安全策略。

4. 防御SQL注入: 无论使用哪个函数，都要始终注意SQL注入的风险。 不要直接将用户输入拼接到SQL语句中，而是使用参数化查询或预处理语句。

五、一些实际例子和代码

1. 审计日志:

CREATE TABLE audit_log (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    event_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    user_name VARCHAR(255),
    session_user VARCHAR(255),
    table_name VARCHAR(255),
    operation VARCHAR(255),
    details TEXT
);

DELIMITER //

CREATE TRIGGER after_update_products
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (user_name, session_user, table_name, operation, details)
    VALUES (CURRENT_USER(), SESSION_USER(), 'products', 'UPDATE',
           CONCAT('Old values: ', OLD.product_name, ', New values: ', NEW.product_name));
END //

DELIMITER ;

这个触发器会在每次更新 products 表时，将 CURRENT_USER() 和 SESSION_USER() 以及其他相关信息记录到 audit_log 表中。

2. 动态SQL中的权限检查（不推荐，仅作演示）:

DELIMITER //
CREATE PROCEDURE check_user_access(IN table_name VARCHAR(255))
BEGIN
    SET @sql = CONCAT('SELECT * FROM ', table_name, ' WHERE created_by = "', CURRENT_USER(), '"');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

警告: 这种方法非常危险，容易受到SQL注入攻击。 绝对不要在生产环境中使用它。 更好的方法是使用角色和权限系统。

六、总结： 理解用户身份获取方式，保障数据库安全

CURRENT_USER() 和 SESSION_USER() 函数提供了获取用户身份的机制，但它们之间存在微妙的差异，尤其是在使用 DEFINER 子句时。 理解这些差异对于编写安全可靠的MySQL应用程序至关重要。 始终注意安全问题，并使用适当的权限控制机制来保护数据库。

七、存储过程和视图的DEFINER如何影响用户身份

DEFINER子句影响CURRENT_USER()，使其返回定义存储过程或视图的用户的身份，而SESSION_USER()反映当前会话的用户。

八、安全使用CURRENT_USER()和SESSION_USER()的建议

不要依赖这些函数进行权限控制，而是使用角色和权限系统；小心DEFINER的权限，定期审查其配置；防御SQL注入。

九、实际案例中如何应用这两个函数进行审计

通过触发器，记录CURRENT_USER()和SESSION_USER()到审计日志表中，可以跟踪数据变更的用户身份，有助于问题排查和安全分析。