MySQL 如何拥有针对特定主机的单一用户账户

MySQL 如何拥有针对特定主机的单一用户账户? 通常，我们会创建用户名相同但主机/IP 不同的独立用户账户，如 <USER>@<HOST1>, <USER>@<HOST2> ....。<USER>@<HOSTn>。然后，给予这些用户相同的授权(权限/角色)和设置(密码、SSL 等)。

除了以上的做法，可以创建一个可以从所有主机连接的单一用户(''@'%')，然后使用 init_connect 服务器参数调用一个存储过程来实现对用户的主机限制。这将使管理用户账户设置和权限变得更容易，因为只需将其应用于一个数据库用户账户。

以下是执行过程。

客户端主机名和 IP：

db1    192.168.137.101
db2    192.168.137.102

创建一个 schema，该 schema 中一个表包含所有允许访问的主机列表。该表将与 USER() 函数的输出进行校验。

create database if not exists logins;
use logins;
create table allow_list(
  user varchar(32)  not null comment 'mysql.user.user',
  host varchar(255) not null comment 'mysql.user.host',
  remarks  varchar(100),
  primary key (user, host)
);

创建存储过程，针对 host = '%' 的值(来自 CURRENT_USER())并在 allow_list 表(基于 USER())中找到的用户实施主机限制。

DELIMITER //
CREATE PROCEDURE sp_init_connect(IN p_current_user_host VARCHAR(300))
SQL SECURITY DEFINER
BEGIN
  DECLARE v_message_text  VARCHAR(128); 
  DECLARE v_exists    BOOLEAN   DEFAULT 0;
  DECLARE v_user      VARCHAR(32) ;
  DECLARE v_host      VARCHAR(255) ;
  DECLARE c_host      VARCHAR(255) ;
  SET v_user  = SUBSTRING_INDEX(USER(), '@', 1);
  SET v_host  = SUBSTRING_INDEX(USER(), '@', -1);
  SET c_host  = SUBSTRING_INDEX(p_current_user_host, '@', -1);
  SELECT 1 INTO v_exists  FROM allow_list WHERE user = v_user AND  host = v_host LIMIT 1;
  IF c_host = '%' THEN  /* Only for users that can connect from any host */
    IF NOT v_exists THEN
      SET v_message_text=CONCAT('User not in ', database(), '.allow_list CURRENT_USER[', p_current_user_host, ']'); /* Set the error message as short as possible so it does not get truncated in the log. */
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = v_message_text, MYSQL_ERRNO = 1001;
    END IF;
  END IF;
END;
//
DELIMITER ;

CURRENT_USER() 返回服务器用于验证当前客户端的 MySQL 帐户的用户名和主机名组合(存储在系统表mysql.user中的账户)。该账户决定你的访问权限。返回值是以 utf8mb3 字符集表示的字符串。USER() 以 utf8mb3 字符集字符串形式返回当前 MySQL 用户名和主机名。该值表示你连接服务器时指定的用户名，以及连接的客户端主机。CURRENT_USER() 的值可能与 USER() 的值不同。例如，从主机11.11.11.21 远程登录数据库，执行以下查询：

mysql> select user(),current_user();
+-------------------------------+------------------------+
| user()                        | current_user()         |
+-------------------------------+------------------------+
| [email protected] | myabc_seoole_user@10.% |
+-------------------------------+------------------------+
1 row in set (0.02 sec)

mysql> 

在存储过程中或者视图中，CURRENT_USER()返回的是定义该对象的用户账户(即 definer 的值)；如果使用了 sql security invoker，返回的就是调用者， USER() 或者 SESSION_USER() 的值。

设置服务器参数 init_connect：

SET GLOBAL init_connect = "CALL logins.sp_init_connect(CURRENT_USER())";

并将该参数写入配置文件：

[mysqld]
init_connect = 'CALL logins.sp_init_connect(CURRENT_USER())'

接着创建数据库账户并授权：

CREATE USER 'use1'@'%'     IDENTIFIED BY 'abcd1234' ;
CREATE USER 'use2'@'192.%' IDENTIFIED BY 'abcd1234' ;
GRANT EXECUTE ON logins.* TO 'use1'@'%';
GRANT EXECUTE ON logins.* TO 'use2'@'192.%';

将主机映射加入hosts文件。如果有dns，可有在dns中配置

192.168.137.102  db2.example.com

尝试使用'use1'@'%'连接数据库。

因为 allow_list 表目前还是空表，因此尝试连接应该失败。

从db1(192.168.137.101)尝试连接：

[root@db1 ~]# mysql -uuse1 -pabcd1234 -h192.168.122.1 -P3306 -e'SELECT USER(), CURRENT_USER(), @@versionG'
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query

从db2(192.168.137.102)尝试连接：

[root@db2 ~]# mysql -uuse1 -pabcd1234 -h192.168.122.1 -P3306 -e'SELECT USER(), CURRENT_USER(), @@versionG'
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query

检查错误日志，这需要将log_error_verbosity 设置成 2 或者 3。

2024-06-28T08:40:56.469085Z 12 [Warning] [MY-013130] [Server] Aborted connection 12 to db: 'unconnected' user: 'use1' host: '192.168.137.101' (init_connect command failed; diagnostics area: MY-001001 - User not in logins.allow_list CURRENT_USER[use1@%])
2024-06-28T08:41:22.064094Z 13 [Warning] [MY-013130] [Server] Aborted connection 13 to db: 'unconnected' user: 'use1' host: 'db2.example.com' (init_connect command failed; diagnostics area: MY-001001 - User not in logins.allow_list CURRENT_USER[use1@%])

插入两条记录后，再次尝试连接：

INSERT INTO allow_list(user, host) VALUES('use1', '192.168.137.101');
INSERT INTO allow_list(user, host) VALUES('use1', 'db2.example.com');

从db1(192.168.137.101)尝试连接：

[root@db1 ~]# mysql -uuse1 -pabcd1234 -h192.168.122.1 -P3306 -e'SELECT USER(), CURRENT_USER(), @@versionG'
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
USER(): [email protected]
CURRENT_USER(): use1@%
@@version: 8.0.32

从db2(192.168.137.102)尝试连接：

[root@db2 ~]# mysql -uuse1 -pabcd1234 -h192.168.122.1 -P3306 -e'SELECT USER(), CURRENT_USER(), @@versionG'
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
USER(): [email protected]
CURRENT_USER(): use1@%
@@version: 8.0.32

尝试使用'use2'@'192.%'连接数据库。

从db1(192.168.137.101)尝试连接：

[root@db1 ~]# mysql -uuse2 -pabcd1234 -h192.168.122.1 -P3306 -e'SELECT USER(), CURRENT_USER(), @@versionG'
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
USER(): [email protected]
CURRENT_USER(): use2@192.%
@@version: 8.0.32

allow_list表中没有'use2@'192.168.137.101'，'use2@'192.%'也验证通过了。