印刻数据库

外观

MariaDB 用户管理 ​

用户管理概述 ​

用户管理是 MariaDB 安全管理的基础，包括用户的创建、修改、删除、密码管理等操作。合理的用户管理可以有效控制数据库的访问权限，防止未授权访问和数据泄露。

MariaDB 用户的组成 ​

MariaDB 用户由两部分组成：

  • 用户名：用户的名称
  • 主机名：用户允许连接的主机地址或主机名

格式：'username'@'hostname'

例如：

  • 'root'@'localhost'：允许 root 用户从本地连接
  • 'app_user'@'%'：允许 app_user 用户从任何主机连接
  • 'readonly'@'192.168.1.%'：允许 readonly 用户从 192.168.1.0/24 网段连接

用户创建 ​

基本用户创建 ​

sql
-- 创建用户，允许从本地连接
CREATE USER 'local_user'@'localhost' IDENTIFIED BY 'password123';

-- 创建用户，允许从任何主机连接
CREATE USER 'remote_user'@'%' IDENTIFIED BY 'password456';

-- 创建用户，允许从特定 IP 网段连接
CREATE USER 'network_user'@'192.168.1.%' IDENTIFIED BY 'password789';

使用不同的认证插件 ​

MariaDB 支持多种认证插件，常用的包括：

  • mysql_native_password：传统的密码认证插件
  • unix_socket：使用 Unix 套接字文件认证（仅适用于本地连接）
  • pam：使用 PAM 认证
  • ed25519：使用 Ed25519 公钥认证
sql
-- 使用 unix_socket 认证插件
CREATE USER 'root'@'localhost' IDENTIFIED VIA unix_socket;

-- 使用 ed25519 认证插件
CREATE USER 'secure_user'@'%' IDENTIFIED VIA ed25519 USING 'public_key_string';

-- 使用 mysql_native_password 认证插件（显式指定）
CREATE USER 'legacy_user'@'%' IDENTIFIED VIA mysql_native_password USING PASSWORD('password123');

设置用户属性 ​

sql
-- 创建用户并设置过期时间
CREATE USER 'temp_user'@'%' IDENTIFIED BY 'password123' PASSWORD EXPIRE INTERVAL 30 DAY;

-- 创建用户并设置默认数据库
CREATE USER 'app_user'@'%' IDENTIFIED BY 'password456' DEFAULT DATABASE app_db;

-- 创建用户并设置资源限制
CREATE USER 'limited_user'@'%' IDENTIFIED BY 'password789' 
  WITH MAX_QUERIES_PER_HOUR 100 
       MAX_UPDATES_PER_HOUR 50 
       MAX_CONNECTIONS_PER_HOUR 10 
       MAX_USER_CONNECTIONS 5;

用户修改 ​

修改用户密码 ​

sql
-- 修改用户密码（方法 1）
SET PASSWORD FOR 'user'@'%' = PASSWORD('new_password');

-- 修改用户密码（方法 2，MariaDB 10.2+）
ALTER USER 'user'@'%' IDENTIFIED BY 'new_password';

-- 修改当前用户密码
SET PASSWORD = PASSWORD('new_password');

修改用户主机 ​

sql
-- 修改用户主机（需要先删除旧用户，再创建新用户）
DROP USER 'user'@'old_host';
CREATE USER 'user'@'new_host' IDENTIFIED BY 'password123';
-- 重新授予权限
GRANT ALL PRIVILEGES ON database.* TO 'user'@'new_host';

修改用户属性 ​

sql
-- 修改用户过期时间
ALTER USER 'temp_user'@'%' PASSWORD EXPIRE INTERVAL 90 DAY;

-- 禁用密码过期
ALTER USER 'permanent_user'@'%' PASSWORD EXPIRE NEVER;

-- 立即过期密码，强制用户下次登录时修改
ALTER USER 'user'@'%' PASSWORD EXPIRE;

-- 修改用户资源限制
ALTER USER 'limited_user'@'%' 
  WITH MAX_QUERIES_PER_HOUR 200 
       MAX_UPDATES_PER_HOUR 100;

-- 解锁用户
ALTER USER 'locked_user'@'%' ACCOUNT UNLOCK;

用户删除 ​

删除单个用户 ​

sql
-- 删除指定用户
DROP USER 'user'@'host';

-- 同时删除多个用户
DROP USER 'user1'@'host1', 'user2'@'host2';

删除所有用户（谨慎操作） ​

sql
-- 查看所有用户
SELECT User, Host FROM mysql.user;

-- 删除所有非 root 用户（谨慎操作）
DROP USER 
  'user1'@'host1', 
  'user2'@'host2',
  -- 列出所有需要删除的用户
  ;

用户权限管理 ​

授予权限 ​

sql
-- 授予用户对特定数据库的所有权限
GRANT ALL PRIVILEGES ON app_db.* TO 'app_user'@'%';

-- 授予用户对特定表的查询权限
GRANT SELECT ON app_db.users TO 'read_user'@'%';

-- 授予用户对特定表的多个权限
GRANT SELECT, INSERT, UPDATE ON app_db.orders TO 'order_user'@'%';

-- 授予用户创建数据库的权限
GRANT CREATE DATABASE ON *.* TO 'admin_user'@'localhost';

-- 授予用户权限并允许其授予其他用户相同权限
GRANT SELECT ON app_db.* TO 'user'@'%' WITH GRANT OPTION;

撤销权限 ​

sql
-- 撤销用户对特定数据库的所有权限
REVOKE ALL PRIVILEGES ON app_db.* FROM 'app_user'@'%';

-- 撤销用户对特定表的查询权限
REVOKE SELECT ON app_db.users FROM 'read_user'@'%';

-- 撤销用户的 GRANT OPTION
REVOKE GRANT OPTION ON app_db.* FROM 'user'@'%';

查看用户权限 ​

sql
-- 查看用户的全局权限
SHOW GRANTS FOR 'user'@'%';

-- 查看用户的数据库级权限
SHOW GRANTS FOR 'user'@'%' USING 'app_db';

-- 查看当前用户的权限
SHOW GRANTS;

密码管理 ​

密码复杂度要求 ​

sql
-- 安装密码验证插件
INSTALL PLUGIN validate_password SONAME 'validate_password.so';

-- 配置密码复杂度要求
SET GLOBAL validate_password_policy = 'STRONG';
SET GLOBAL validate_password_length = 12;
SET GLOBAL validate_password_number_count = 2;
SET GLOBAL validate_password_special_char_count = 1;
SET GLOBAL validate_password_mixed_case_count = 2;

密码过期策略 ​

sql
-- 设置全局密码过期策略（90 天）
SET GLOBAL default_password_lifetime = 90;

-- 为特定用户设置不同的过期策略
ALTER USER 'user'@'%' PASSWORD EXPIRE INTERVAL 30 DAY;

密码历史记录 ​

sql
-- 安装密码历史记录插件
INSTALL PLUGIN password_reuse_check SONAME 'password_reuse_check.so';

-- 配置密码历史记录（禁止重用最近 5 个密码）
SET GLOBAL password_history = 5;

用户锁定与解锁 ​

锁定用户 ​

sql
-- 锁定用户
ALTER USER 'user'@'%' ACCOUNT LOCK;

-- 锁定多个用户
ALTER USER 'user1'@'host1' ACCOUNT LOCK, 'user2'@'host2' ACCOUNT LOCK;

解锁用户 ​

sql
-- 解锁用户
ALTER USER 'user'@'%' ACCOUNT UNLOCK;

-- 解锁多个用户
ALTER USER 'user1'@'host1' ACCOUNT UNLOCK, 'user2'@'host2' ACCOUNT UNLOCK;

查看用户锁定状态 ​

sql
-- 查看用户锁定状态
SELECT User, Host, account_locked FROM mysql.user;

用户资源限制 ​

设置全局资源限制 ​

sql
-- 设置全局最大连接数
SET GLOBAL max_connections = 1000;

-- 设置每个用户的最大连接数
SET GLOBAL max_user_connections = 100;

设置用户级资源限制 ​

sql
-- 创建用户时设置资源限制
CREATE USER 'limited_user'@'%' IDENTIFIED BY 'password123' 
  WITH MAX_QUERIES_PER_HOUR 1000 
       MAX_UPDATES_PER_HOUR 500 
       MAX_CONNECTIONS_PER_HOUR 50 
       MAX_USER_CONNECTIONS 10;

-- 修改现有用户的资源限制
ALTER USER 'limited_user'@'%' 
  WITH MAX_QUERIES_PER_HOUR 2000 
       MAX_UPDATES_PER_HOUR 1000;

查看用户资源限制 ​

sql
-- 查看用户资源限制
SELECT 
  User, 
  Host, 
  max_questions, 
  max_updates, 
  max_connections, 
  max_user_connections 
FROM mysql.user;

用户管理的最佳实践 ​

1. 遵循最小权限原则 ​

  • 只授予用户完成工作所需的最小权限
  • 避免授予 ALL PRIVILEGES 权限
  • 定期审查和回收不必要的权限

2. 使用强密码策略 ​

  • 实施密码复杂度要求
  • 设置合理的密码过期时间
  • 禁止密码重用
  • 使用密码管理工具，避免密码泄露

3. 限制用户连接主机 ​

  • 尽量限制用户只能从特定主机或网段连接
  • 避免使用 'user'@'%'（允许从任何主机连接）
  • 对于敏感用户，只允许从本地连接

4. 定期审计用户和权限 ​

  • 定期查看和清理无用用户
  • 定期审查用户权限，回收不必要的权限
  • 记录用户权限变更日志

5. 使用不同的用户角色 ​

  • 为不同的应用和用户设置不同的角色
  • 例如：
    • 只读用户：用于报表和数据分析
    • 读写用户：用于应用程序
    • 管理员用户：用于数据库管理

6. 保护 root 用户 ​

  • 限制 root 用户只能从本地连接
  • 避免在应用程序中使用 root 用户
  • 定期更换 root 密码
  • 考虑使用 unix_socket 认证插件

7. 使用角色管理权限 ​

  • MariaDB 10.0+ 支持角色管理
  • 可以将权限分配给角色，然后将角色分配给用户
  • 简化权限管理，提高安全性
sql
-- 创建角色
CREATE ROLE 'read_only_role', 'read_write_role';

-- 为角色授予权限
GRANT SELECT ON *.* TO 'read_only_role';
GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'read_write_role';

-- 将角色分配给用户
GRANT 'read_only_role' TO 'report_user'@'%';
GRANT 'read_write_role' TO 'app_user'@'%';

-- 激活角色
SET DEFAULT ROLE ALL TO 'report_user'@'%', 'app_user'@'%';

8. 监控用户活动 ​

  • 启用审计日志，记录用户活动
  • 监控异常登录尝试
  • 监控用户的查询活动，特别是敏感操作

常见问题 (FAQ) ​

Q1：如何查看所有用户？ ​

A：使用以下命令查看所有用户：

sql
SELECT User, Host FROM mysql.user;

Q2：如何修改用户的认证插件？ ​

A：使用 ALTER USER 命令修改用户的认证插件：

sql
ALTER USER 'user'@'%' IDENTIFIED VIA mysql_native_password USING PASSWORD('password123');

Q3：如何禁止用户远程连接？ ​

A：删除允许远程连接的用户，只保留本地连接的用户：

sql
DROP USER 'user'@'%';
CREATE USER 'user'@'localhost' IDENTIFIED BY 'password123';

Q4：如何设置用户的默认数据库？ ​

A：在创建用户时使用 DEFAULT DATABASE 子句：

sql
CREATE USER 'user'@'localhost' IDENTIFIED BY 'password123' DEFAULT DATABASE app_db;

Q5：如何强制用户定期修改密码？ ​

A：设置密码过期时间：

sql
-- 全局设置
SET GLOBAL default_password_lifetime = 90;

-- 为特定用户设置
ALTER USER 'user'@'%' PASSWORD EXPIRE INTERVAL 30 DAY;

Q6：如何禁用匿名用户？ ​

A：删除匿名用户：

sql
DROP USER ''@'localhost';
DROP USER ''@'hostname';

Q7：如何限制用户的查询速率？ ​

A：设置用户的资源限制：

sql
ALTER USER 'user'@'%' WITH MAX_QUERIES_PER_HOUR 1000;

Q8：如何使用角色管理权限？ ​

A：创建角色，授予权限，然后将角色分配给用户：

sql
CREATE ROLE 'role_name';
GRANT privileges ON database.table TO 'role_name';
GRANT 'role_name' TO 'user'@'host';
SET DEFAULT ROLE 'role_name' TO 'user'@'host';