MySQL 如何拥有针对特定主机的单一用户账户
2024-07-24 17:35 abce 阅读(14) 评论(0) 编辑 收藏 举报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() | +-------------------------------+------------------------+ | myabc_seoole_user@11.11.11.21 | 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(): use1@192.168.137.101 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(): use1@db2.example.com 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(): use2@192.168.137.101 CURRENT_USER(): use2@192.% @@version: 8.0.32
allow_list表中没有'use2@'192.168.137.101','use2@'192.%'也验证通过了。