2. 首页 > 国产数据库教程 > TiDB教程 > 正文

tidb教程FG122-用户和权限管理生产实战

教程整理：风哥教程  |  更新时间：2026-04-04  |  教程分类：TiDB教程  |  文档学习：32
fgedu.net.cn

目录

一、基础概念

1.1 用户管理

用户管理是数据库安全的基础，包括创建、修改、删除用户，以及管理用户密码等操作。TiDB支持MySQL兼容的用户管理语法。

1.2 权限管理

权限管理是控制用户对数据库资源访问的机制，包括授予、回收权限等操作。TiDB支持基于角色的访问控制（RBAC）。

1.3 角色管理

角色是一组权限的集合，可以批量授予用户，简化权限管理。TiDB支持创建、修改、删除角色，以及将角色授予用户。

1.4 权限级别

  • 全局级别：适用于所有数据库
  • 数据库级别：适用于特定数据库
  • 表级别：适用于特定表
  • 列级别：适用于特定列
  • 存储过程级别：适用于存储过程

二、规划建议

2.1 用户管理策略

  • 最小权限原则：只授予用户必要的权限
  • 统一命名规范：建立统一的用户命名规范
  • 定期审查：定期审查用户权限，及时回收不必要的权限
  • 密码策略：设置强密码要求，定期更换密码
  • 用户分类：根据用户角色和职责分类管理

2.2 角色设计

  • 管理员角色：拥有所有权限
  • 开发角色：拥有开发所需的权限
  • 只读角色：只拥有查询权限
  • 应用角色：应用程序使用的角色
  • 审计角色：拥有审计相关权限

2.3 权限规划

  • 全局权限：只授予管理员
  • 数据库权限：根据用户职责授予
  • 表权限：根据具体需求授予
  • 列权限：对于敏感列，严格控制访问

三、实施方案

3.1 用户管理

创建用户

CREATE USER 'admin'@'%' IDENTIFIED BY 'Admin123!@#';

修改用户

-- 修改用户名
RENAME USER 'admin'@'%' TO 'administrator'@'%';

-- 修改密码
ALTER USER 'administrator'@'%' IDENTIFIED BY 'NewPass123!@#';

删除用户

DROP USER 'administrator'@'%';

查看用户

SELECT user, host FROM mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| root          | %         |
| admin         | %         |
| readonly      | %         |
| app           | %         |风哥提示：
+---------------+-----------+

3.2 角色管理

创建角色

CREATE ROLE 'admin_role', 'dev_role', 'read_role';

授予角色权限

-- 授予admin_role所有权限
GRANT ALL PRIVILEGES ON *.* TO 'admin_role';

-- 授予dev_role开发权限
GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT ON fgedudb.* TO 'dev_role';

-- 授予read_role只读权限
GRANT SELECT ON fgedudb.* TO 'read_role';

将角色授予用户

-- 授予admin用户admin_role角色
GRANT 'admin_role' TO 'admin'@'%';

-- 授予dev用户dev_role角色
GRANT 'dev_role' TO 'dev'@'%';

-- 授予readonly用户read_role角色
GRANT 'read_role' TO 'readonly'@'%';

激活角色

-- 为用户设置默认角色
SET DEFAULT ROLE 'admin_role' FOR 'admin'@'%';
SET DEFAULT ROLE 'dev_role' FOR 'dev'@'%';
SET DEFAULT ROLE 'read_role' FOR 'readonly'@'%';

查看角色

SELECT role_name FROM mysql.role_edges WHERE from_host = '%';
+-----------+
| role_name |
+-----------+
| admin_role |
| dev_role   |
| read_role  |
+-----------+

3.3 权限管理

授予权限

-- 授予全局权限
GRANT SUPER, RELOAD, SHUTDOWN ON *.* TO 'admin'@'%';

-- 授予数据库权限
GRANT ALL PRIVILEGES ON fgedudb.* TO 'app'@'%';

-- 授予表权限
GRANT SELECT, INSERT, UPDATE ON fgedudb.fgedu_users TO 'app'@'%';

-- 授予列权限
GRANT SELECT (id, username) ON fgedudb.fgedu_users TO 'readonly'@'%';

回收权限

-- 回收全局权限
REVOKE SHUTDOWN ON *.* FROM 'admin'@'%';

-- 回收数据库权限
REVOKE DROP ON fgedudb.* FROM 'app'@'%';

-- 回收表权限
REVOKE UPDATE ON fgedudb.fgedu_users FROM 'readonly'@'%';

查看权限

-- 查看用户权限
SHOW GRANTS FOR 'admin'@'%';

-- 查看角色权限
SHOW GRANTS FOR 'admin_role';
+---------------------------------------------------------------------+
| Grants for admin@%                                                   |
+---------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE ROLE, DROP ROLE ON *.* TO `admin`@`%` WITH GRANT OPTION |
| GRANT `admin_role`@`%` TO `admin`@`%`                              |
+---------------------------------------------------------------------+

3.4 密码管理

学习交流加群风哥QQ113257174

设置密码策略

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

强制密码过期

-- 设置密码过期时间为90天
ALTER USER 'admin'@'%' PASSWORD EXPIRE INTERVAL 90 DAY;

锁定用户

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

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

四、实战案例

4.1 企业级用户权限管理

场景：大型企业需要建立完善的用户权限管理体系，包括管理员、开发人员、分析师和应用程序用户。

步骤1：创建角色

-- 创建管理员角色
CREATE ROLE 'admin_role';
GRANT ALL PRIVILEGES ON *.* TO 'admin_role' WITH GRANT OPTION;

-- 创建开发角色
CREATE ROLE 'dev_role';
GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, EXECUTE ON fgedudb.* TO 'dev_role';

-- 创建只读角色
CREATE ROLE 'read_role';
GRANT SELECT ON fgedudb.* TO 'read_role';

-- 创建应用角色
CREATE ROLE 'app_role';
GRANT SELECT, INSERT, UPDATE, DELETE ON fgedudb.* TO 'app_role';

步骤2：创建用户并分配角色

-- 创建管理员用户
CREATE USER 'admin'@'%' IDENTIFIED BY 'Admin123!@#';
GRANT 'admin_role' TO 'admin'@'%';
SET DEFAULT ROLE 'admin_role' FOR 'admin'@'%';

-- 创建开发用户
CREATE USER 'dev'@'%' IDENTIFIED BY 'Dev123!@#';
GRANT 'dev_role' TO 'dev'@'%';
SET DEFAULT ROLE 'dev_role' FOR 'dev'@'%';

-- 创建只读用户
CREATE USER 'analyst'@'%' IDENTIFIED BY 'Analyst123!@#';
GRANT 'read_role' TO 'analyst'@'%';
SET DEFAULT ROLE 'read_role' FOR 'analyst'@'%';

-- 创建应用用户
CREATE USER 'app'@'%' IDENTIFIED BY 'App123!@#';
GRANT 'app_role' TO 'app'@'%';
SET DEFAULT ROLE 'app_role' FOR 'app'@'%';

步骤3：测试权限

-- 以只读用户身份登录
mysql -h 192.168.1.13 -P 4000 -u analyst -p

-- 尝试执行更新操作（应该失败）
UPDATE fgedudb.fgedu_users SET username = 'test' WHERE id = 1;
ERROR 1142 (42000): UPDATE command denied to user 'analyst'@'%' for table 'fgedu_users'
-- 以开发用户身份登录
mysql -h 192.168.1.13 -P 4000 -u dev -p

-- 尝试创建表（应该成功）
CREATE TABLE fgedudb.fgedu_test (id INT PRIMARY KEY, name VARCHAR(50));
Query OK, 0 rows affected (0.12 sec)

4.2 敏感数据访问控制

场景：金融行业需要对敏感数据进行严格的访问控制，只允许特定用户访问敏感信息。

步骤1：创建敏感数据表

CREATE TABLE fgedudb.fgedu_customers (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    id_card VARCHAR(18) NOT NULL,
    phone VARCHAR(11) NOT NULL,
    bank_card VARCHAR(19) NOT NULL,
    address VARCHAR(200) NOT NULL
);

步骤2：创建角色并设置权限

-- 创建普通员工角色（只能访问非敏感列）
CREATE ROLE 'employee_role';
GRANT SELECT (id, name, address) ON fgedudb.fgedu_customers TO 'employee_role';

-- 创建管理员角色（可以访问所有列）
CREATE ROLE 'manager_role';
GRANT SELECT ON fgedudb.fgedu_customers TO 'manager_role';

步骤3：创建用户并分配角色

-- 创建普通员工用户
CREATE USER 'emp1'@'%' IDENTIFIED BY 'Emp123!@#';
GRANT 'employee_role' TO 'emp1'@'%';
SET DEFAULT ROLE 'employee_role' FOR 'emp1'@'%';

-- 创建管理员用户
CREATE USER 'mgr1'@'%' IDENTIFIED BY 'Mgr123!@#';
GRANT 'manager_role' TO 'mgr1'@'%';
SET DEFAULT ROLE 'manager_role' FOR 'mgr1'@'%';

步骤4：测试权限

-- 以普通员工身份登录
mysql -h 192.168.1.13 -P 4000 -u emp1 -p

-- 尝试查询所有列（应该只能看到授权的列）
SELECT * FROM fgedudb.fgedu_customers;
+----+------+---------+
| id | name | address |
+----+------+---------+
|  1 | 张三 | 北京市  |
|  2 | 李四 | 上海市  |
+----+------+---------+

-- 以管理员身份登录
mysql -h 192.168.1.13 -P 4000 -u mgr1 -p

-- 尝试查询所有列（应该能看到所有列）
SELECT * FROM fgedudb.fgedu_customers;
+----+------+------------------+-------------+-------------------+---------+
| id | name | id_card          | phone       | bank_card         | address |
+----+------+------------------+-------------+-------------------+---------+
|  1 | 张三 | 110101199001011234 | 13812345678 | 622202123456783456 | 北京市  |
|  2 | 李四 | 310101199001012345 | 13912345678 | 622202123456784567 | 上海市  |
+----+------+------------------+-------------+-------------------+---------+

五、经验总结

5.1 用户和权限管理最佳实践

  • 采用最小权限原则：只授予用户必要的权限
  • 使用角色管理：通过角色批量管理权限，简化权限管理
  • 定期审查权限：定期检查用户权限，及时回收不必要的权限
  • 设置强密码策略：确保用户密码安全
  • 限制用户访问范围：根据用户职责限制访问范围
  • 启用审计日志：记录用户操作，便于追踪和审计
  • 定期备份权限配置：防止权限配置丢失

5.2 权限管理技巧

  • 分层权限设计：根据用户级别设计不同的权限层次
  • 权限继承：利用角色继承简化权限管理
  • 权限分离：将管理权限和操作权限分离
  • 定期权限审计：使用工具进行权限审计
  • 自动化权限管理：使用脚本或工具自动化权限管理

5.3 常见问题与解决方案

问题 原因 解决方案
权限不足 用户没有足够的权限 检查并授予必要的权限
权限过大 用户拥有超出其职责的权限 回收不必要的权限
角色未激活 角色授予后未激活 使用SET DEFAULT ROLE激活角色
密码过期 密码策略设置了过期时间 更新密码
用户被锁定 多次密码错误或手动锁定 解锁用户

5.4 安全建议

  • 禁用root远程登录：只允许本地登录root用户
  • 使用SSL连接：加密用户认证信息
  • 限制用户IP：通过主机名限制用户登录IP
  • 定期更改密码：设置密码过期时间
  • 监控异常登录：监控并记录异常登录行为
  • 使用LDAP认证：集成企业LDAP系统进行用户认证

更多视频教程www.fgedu.net.cn

© 2024 TiDB数据库培训文档

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

相关推荐

联系我们

在线咨询：点击这里给我发消息

微信号：itpux-com

工作日：9:30-18:30，节假日休息