TiDB 用户管理

用户创建

1. 基本用户创建

sql

-- 创建基本用户
CREATE USER 'username'@'host' IDENTIFIED BY 'password';

-- 示例：创建允许从任何主机访问的用户
CREATE USER 'test'@'%' IDENTIFIED BY 'TestPass123!';

-- 示例：创建只允许从特定 IP 访问的用户
CREATE USER 'test'@'192.168.1.100' IDENTIFIED BY 'TestPass123!';

-- 示例：创建只允许从特定域名访问的用户
CREATE USER 'test'@'example.com' IDENTIFIED BY 'TestPass123!';

2. 创建用户时设置密码过期时间

sql

-- 创建密码 30 天后过期的用户
CREATE USER 'test'@'%' IDENTIFIED BY 'TestPass123!' PASSWORD EXPIRE INTERVAL 30 DAY;

-- 创建密码永不过期的用户
CREATE USER 'test'@'%' IDENTIFIED BY 'TestPass123!' PASSWORD EXPIRE NEVER;

-- 使用全局密码过期设置
CREATE USER 'test'@'%' IDENTIFIED BY 'TestPass123!' PASSWORD EXPIRE DEFAULT;

3. 创建用户时设置资源限制

sql

-- 创建用户并设置资源限制
CREATE USER 'test'@'%' IDENTIFIED BY 'TestPass123!' 
    WITH MAX_QUERIES_PER_HOUR 1000
         MAX_UPDATES_PER_HOUR 500
         MAX_CONNECTIONS_PER_HOUR 100
         MAX_USER_CONNECTIONS 10;

用户权限管理

1. 授予权限

sql

-- 授予所有数据库的所有权限
GRANT ALL PRIVILEGES ON *.* TO 'test'@'%';

-- 授予特定数据库的所有权限
GRANT ALL PRIVILEGES ON database_name.* TO 'test'@'%';

-- 授予特定表的特定权限
GRANT SELECT, INSERT, UPDATE ON database_name.table_name TO 'test'@'%';

-- 授予带 WITH GRANT OPTION 的权限
GRANT SELECT ON database_name.* TO 'test'@'%' WITH GRANT OPTION;

-- 刷新权限
FLUSH PRIVILEGES;

2. 查看权限

sql

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

-- 查看特定用户权限
SHOW GRANTS FOR 'test'@'%';

-- 查看用户的详细权限信息
SELECT * FROM mysql.user WHERE User = 'test' AND Host = '%';
SELECT * FROM mysql.db WHERE User = 'test' AND Host = '%';
SELECT * FROM mysql.tables_priv WHERE User = 'test' AND Host = '%';

3. 撤销权限

sql

-- 撤销特定权限
REVOKE INSERT, UPDATE ON database_name.table_name FROM 'test'@'%';

-- 撤销所有数据库的所有权限
REVOKE ALL PRIVILEGES ON *.* FROM 'test'@'%';

-- 撤销 WITH GRANT OPTION 权限
REVOKE GRANT OPTION ON database_name.* FROM 'test'@'%';

-- 刷新权限
FLUSH PRIVILEGES;

用户信息修改

1. 修改用户密码

sql

-- 修改当前用户密码
ALTER USER USER() IDENTIFIED BY 'NewPassword123!';

-- 修改特定用户密码
ALTER USER 'test'@'%' IDENTIFIED BY 'NewPassword123!';

-- 使用 SET PASSWORD 语句修改密码（不推荐）
SET PASSWORD FOR 'test'@'%' = PASSWORD('NewPassword123!');

2. 修改用户密码过期时间

sql

-- 修改密码过期时间为 90 天
ALTER USER 'test'@'%' PASSWORD EXPIRE INTERVAL 90 DAY;

-- 设置密码永不过期
ALTER USER 'test'@'%' PASSWORD EXPIRE NEVER;

-- 使用全局密码过期设置
ALTER USER 'test'@'%' PASSWORD EXPIRE DEFAULT;

3. 修改用户资源限制

sql

-- 修改用户资源限制
ALTER USER 'test'@'%' 
    WITH MAX_QUERIES_PER_HOUR 2000
         MAX_UPDATES_PER_HOUR 1000
         MAX_CONNECTIONS_PER_HOUR 200
         MAX_USER_CONNECTIONS 20;

4. 修改用户名或主机

sql

-- 重命名用户
RENAME USER 'old_user'@'host' TO 'new_user'@'host';

-- 示例：重命名用户
RENAME USER 'test'@'%' TO 'new_test'@'%';

-- 示例：修改用户主机
RENAME USER 'test'@'192.168.1.100' TO 'test'@'%';

5. 锁定和解锁用户

sql

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

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

-- 查看用户锁定状态
SELECT User, Host, account_locked FROM mysql.user WHERE User = 'test';

用户删除

1. 删除单个用户

sql

-- 删除用户
DROP USER 'test'@'%';

-- 示例：删除特定主机的用户
DROP USER 'test'@'192.168.1.100';

2. 删除多个用户

sql

-- 同时删除多个用户
DROP USER 'user1'@'%', 'user2'@'%', 'user3'@'localhost';

3. 删除用户前的检查

sql

-- 检查用户是否存在
SELECT User, Host FROM mysql.user WHERE User = 'test';

-- 检查用户拥有的权限
SHOW GRANTS FOR 'test'@'%';

-- 检查用户的连接情况
SHOW PROCESSLIST WHERE USER = 'test';

角色管理

1. 创建角色

sql

-- 创建角色
CREATE ROLE 'role_name';

-- 示例：创建只读角色
CREATE ROLE 'read_only';

-- 示例：创建读写角色
CREATE ROLE 'read_write';

2. 授予角色权限

sql

-- 授予角色权限
GRANT SELECT ON *.* TO 'read_only';
GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'read_write';

3. 将角色授予用户

sql

-- 将角色授予用户
GRANT 'read_only' TO 'test'@'%';

-- 将多个角色授予用户
GRANT 'read_only', 'read_write' TO 'test'@'%';

-- 设置用户的默认角色
SET DEFAULT ROLE 'read_write' FOR 'test'@'%';

4. 激活角色

sql

-- 激活当前会话的所有角色
SET ROLE ALL;

-- 激活特定角色
SET ROLE 'read_write';

-- 激活默认角色
SET ROLE DEFAULT;

-- 查看当前激活的角色
SELECT CURRENT_ROLE();

5. 撤销角色

sql

-- 从用户撤销角色
REVOKE 'read_only' FROM 'test'@'%';

-- 删除角色
DROP ROLE 'read_only';

用户管理最佳实践

1. 命名规范

使用有意义的用户名，反映用户的角色或用途
避免使用默认用户名（如 root、admin 等）
用户名应区分大小写，建议使用小写字母

2. 密码策略

使用强密码，包含大小写字母、数字和特殊字符
定期更换密码
避免在多个系统中使用相同密码
启用密码复杂度检查

3. 权限管理

遵循最小权限原则，只授予必要的权限
定期审查用户权限，撤销不必要的权限
使用角色管理权限，便于批量管理
避免使用 WITH GRANT OPTION，除非必要

4. 安全管理

限制用户的访问主机
启用 SSL 连接
定期审计用户活动
启用登录失败锁定

5. 监控和日志

监控用户登录活动
记录用户操作日志
设置异常登录告警
定期检查用户连接情况

常见问题（FAQ）

Q1: 如何创建一个只读用户？

A1: 可以通过以下步骤创建只读用户：

sql

-- 创建用户
CREATE USER 'readonly'@'%' IDENTIFIED BY 'ReadOnlyPass123!';

-- 授予只读权限
GRANT SELECT ON *.* TO 'readonly'@'%';

-- 刷新权限
FLUSH PRIVILEGES;

Q2: 如何查看当前用户是谁？

A2: 使用以下命令查看当前用户：

sql

SELECT USER();
-- 或
SELECT CURRENT_USER();

Q3: 如何修改用户的最大连接数？

A3: 可以使用以下命令修改用户的最大连接数：

sql

-- 修改用户的最大连接数
ALTER USER 'test'@'%' WITH MAX_USER_CONNECTIONS 20;

-- 查看用户的资源限制
SELECT User, Host, max_user_connections FROM mysql.user WHERE User = 'test';

Q4: 如何重置 root 用户密码？

A4: 如果忘记了 root 用户密码，可以通过以下步骤重置：

停止 TiDB 服务
使用 --skip-grant-tables 选项启动 TiDB
登录 TiDB 并修改 root 密码
重启 TiDB 服务

Q5: 如何查看所有用户？

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

sql

SELECT User, Host FROM mysql.user;

Q6: 如何查看用户的连接数？

A6: 使用以下命令查看用户的连接数：

sql

-- 查看所有用户的连接数
SHOW PROCESSLIST;

-- 查看特定用户的连接数
SHOW PROCESSLIST WHERE USER = 'test';

-- 统计特定用户的连接数
SELECT COUNT(*) FROM information_schema.processlist WHERE USER = 'test';

Q7: 如何禁用用户？

A7: 可以通过锁定用户来禁用用户：

sql

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

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

-- 查看用户锁定状态
SELECT User, Host, account_locked FROM mysql.user WHERE User = 'test';

Q8: 如何限制用户只能访问特定数据库？

A8: 可以通过授予用户特定数据库的权限来实现：

sql

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

-- 刷新权限
FLUSH PRIVILEGES;

Q9: 如何审计用户活动？

A9: 可以通过以下方式审计用户活动：

启用慢查询日志和通用查询日志
使用 TiDB Audit Log 功能
定期检查用户的连接和操作记录
使用第三方审计工具

Q10: 如何批量创建用户？

A10: 可以使用存储过程或脚本来批量创建用户：

sql

-- 示例：使用存储过程批量创建用户
DELIMITER //
CREATE PROCEDURE create_users()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE user_name VARCHAR(50);
    DECLARE password VARCHAR(50);
    
    WHILE i <= 10 DO
        SET user_name = CONCAT('user_', i);
        SET password = CONCAT('Pass_', i, '_123!');
        SET @sql = CONCAT('CREATE USER ''', user_name, '''@''%'' IDENTIFIED BY ''', password, '''');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;

-- 执行存储过程
CALL create_users();

Q11: 如何删除用户及其所有权限？

A11: 使用 DROP USER 命令可以删除用户及其所有权限：

sql

-- 删除用户
DROP USER 'test'@'%';

Q12: 如何查看用户的密码过期时间？

A12: 使用以下命令查看用户的密码过期时间：

sql

SELECT User, Host, password_expired, password_last_changed, password_lifetime 
FROM mysql.user WHERE User = 'test';

Q13: 如何修改用户的密码策略？

A13: 可以通过修改系统变量来调整密码策略：

sql

-- 设置密码最小长度
SET GLOBAL validate_password_length = 12;

-- 设置密码至少包含的大小写字母数量
SET GLOBAL validate_password_mixed_case_count = 1;

-- 设置密码至少包含的数字数量
SET GLOBAL validate_password_number_count = 1;

-- 设置密码至少包含的特殊字符数量
SET GLOBAL validate_password_special_char_count = 1;

-- 设置密码策略级别
SET GLOBAL validate_password_policy = 'STRONG';

Q14: 如何使用 SSL 连接？

A14: 可以通过以下步骤配置 SSL 连接：

生成 SSL 证书和密钥
配置 TiDB 使用 SSL
创建需要 SSL 连接的用户

sql

-- 创建需要 SSL 连接的用户
CREATE USER 'ssl_user'@'%' IDENTIFIED BY 'SslPass123!' REQUIRE SSL;

Q15: 如何迁移用户到新的 TiDB 集群？

A15: 可以通过以下方式迁移用户：

使用 mysqldump 导出用户数据
在新集群中导入用户数据
刷新权限

bash

-- 导出用户数据
mysqldump -u root -p --no-data --routines --events mysql > mysql_users.sql

-- 导入用户数据
mysql -u root -p mysql < mysql_users.sql

-- 刷新权限
mysql -u root -p -e "FLUSH PRIVILEGES;"

TiDB 用户管理 ​

用户创建 ​

1. 基本用户创建 ​

2. 创建用户时设置密码过期时间 ​

3. 创建用户时设置资源限制 ​

用户权限管理 ​

1. 授予权限 ​

2. 查看权限 ​

3. 撤销权限 ​

用户信息修改 ​

1. 修改用户密码 ​

2. 修改用户密码过期时间 ​

3. 修改用户资源限制 ​

4. 修改用户名或主机 ​

5. 锁定和解锁用户 ​

用户删除 ​

1. 删除单个用户 ​

2. 删除多个用户 ​

3. 删除用户前的检查 ​

角色管理 ​

1. 创建角色 ​

2. 授予角色权限 ​

3. 将角色授予用户 ​

4. 激活角色 ​

5. 撤销角色 ​

用户管理最佳实践 ​

1. 命名规范 ​

2. 密码策略 ​

3. 权限管理 ​

4. 安全管理 ​

5. 监控和日志 ​

常见问题（FAQ） ​

Q1: 如何创建一个只读用户？ ​

Q2: 如何查看当前用户是谁？ ​

Q3: 如何修改用户的最大连接数？ ​

Q4: 如何重置 root 用户密码？ ​

Q5: 如何查看所有用户？ ​

Q6: 如何查看用户的连接数？ ​

Q7: 如何禁用用户？ ​

Q8: 如何限制用户只能访问特定数据库？ ​

Q9: 如何审计用户活动？ ​

Q10: 如何批量创建用户？ ​

Q11: 如何删除用户及其所有权限？ ​

Q12: 如何查看用户的密码过期时间？ ​

Q13: 如何修改用户的密码策略？ ​

Q14: 如何使用 SSL 连接？ ​

Q15: 如何迁移用户到新的 TiDB 集群？ ​