tidb教程FG139-TiDB用户和权限管理

fgedu.net.cn

一、基础概念

1.1 用户管理

用户管理是指创建、修改和删除用户账户的过程。在TiDB中，用户账户由用户名和主机名组成，格式为`’username’@’hostname’`。

1.2 权限管理

权限管理是指授予或撤销用户对数据库对象的访问权限的过程。TiDB支持细粒度的权限控制，可以控制用户对数据库、表、列等对象的访问权限。

1.3 权限类型

全局权限：适用于所有数据库的权限
数据库权限：适用于特定数据库的权限
表权限：适用于特定表的权限
列权限：适用于特定列的权限
存储过程权限：适用于存储过程的权限

1.4 角色管理

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

二、规划建议

2.1 用户规划

管理员用户：拥有所有权限，用于管理数据库
应用用户：拥有应用所需的最小权限
只读用户：只拥有查询权限
备份用户：拥有备份权限

2.2 权限规划

最小权限原则：只授予用户所需的最小权限
权限分离：将不同职责的权限分离
定期审计：定期审计用户权限
密码策略：设置强密码策略

2.3 角色规划

管理员角色：拥有所有权限
应用角色：拥有应用所需的权限
只读角色：只拥有查询权限
备份角色：拥有备份权限

三、实施方案

3.1 用户管理

创建用户

-- 创建用户
CREATE USER 'admin'@'%' IDENTIFIED BY 'admin123';
CREATE USER 'app'@'192.168.1.%' IDENTIFIED BY 'app123';
CREATE USER 'readonly'@'localhost' IDENTIFIED BY 'readonly123';

-- 查看用户
SELECT user, host FROM mysql.user;

+----------+-----------+
| user     | host      |
+----------+-----------+
| admin    | %         |
| app      | 192.168.1.% |
| readonly | localhost |
| root     | %         |
+----------+-----------+

修改用户

-- 修改用户密码
ALTER USER 'admin'@'%' IDENTIFIED BY 'newpassword123';

-- 修改用户主机
RENAME USER 'admin'@'%' TO 'admin'@'192.168.1.%';

-- 查看用户
SELECT user, host FROM mysql.user;

+----------+-----------+风哥提示：
| user     | host      |
+----------+-----------+
| admin    | 192.168.1.% |
| app      | 192.168.1.% |
| readonly | localhost |
| root     | %         |
+----------+-----------+

删除用户

-- 删除用户
DROP USER 'readonly'@'localhost';

-- 查看用户
SELECT user, host FROM mysql.user;

+----------+-----------+
| user     | host      |
+----------+-----------+
| admin    | 192.168.1.% |
| app      | 192.168.1.% |
| root     | %         |
+----------+-----------+

3.2 权限管理

授予权限

-- 授予全局权限
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'192.168.1.%' WITH GRANT OPTION;

-- 授予数据库权限
GRANT SELECT, INSERT, UPDATE, DELETE ON fgedudb.* TO 'app'@'192.168.1.%';

-- 授予表权限
GRANT SELECT ON fgedudb.fgedu_users TO 'readonly'@'localhost';

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

-- 查看权限
SHOW GRANTS FOR 'admin'@'192.168.1.%';
SHOW GRANTS FOR 'app'@'192.168.1.%';

+----------------------------------------------------------------------------------------------------+
| Grants for admin@192.168.1.%                                                                       |
+----------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `admin`@`192.168.1.%` WITH GRANT OPTION                              |
+----------------------------------------------------------------------------------------------------+

+----------------------------------------------------------------------------------------------------+
| Grants for app@192.168.1.%                                                                         |
+----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `app`@`192.168.1.%`                                                           |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `fgedudb`.* TO `app`@`192.168.1.%`                          |
+----------------------------------------------------------------------------------------------------+

撤销权限

-- 撤销权限
REVOKE DELETE ON fgedudb.* FROM 'app'@'192.168.1.%';

-- 查看权限
SHOW GRANTS FOR 'app'@'192.168.1.%';

+----------------------------------------------------------------------------------------------------+
| Grants for app@192.168.1.%                                                                         |
+----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `app`@`192.168.1.%`                                                           |
| GRANT SELECT, INSERT, UPDATE ON `fgedudb`.* TO `app`@`192.168.1.%`                                  |
+----------------------------------------------------------------------------------------------------+

3.3 角色管理

创建角色

-- 创建角色
CREATE ROLE 'admin_role', 'app_role', 'readonly_role';

-- 查看角色
SELECT * FROM mysql.role_edges;

授予角色权限

-- 授予角色权限
GRANT ALL PRIVILEGES ON *.* TO 'admin_role';
GRANT SELECT, INSERT, UPDATE, DELETE ON fgedudb.* TO 'app_role';
GRANT SELECT ON fgedudb.* TO 'readonly_role';

-- 查看角色权限
SHOW GRANTS FOR 'admin_role';
SHOW GRANTS FOR 'app_role';
SHOW GRANTS FOR 'readonly_role';

+----------------------------------------------------------------------------------------------------+
| Grants for admin_role@%                                                                            |
+----------------------------------------------------------------------------------------------------+学习交流加群风哥QQ113257174
| GRANT ALL PRIVILEGES ON *.* TO `admin_role`@`%`                                                    |
+----------------------------------------------------------------------------------------------------+

+----------------------------------------------------------------------------------------------------+
| Grants for app_role@%                                                                              |
+----------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE ON `fgedudb`.* TO `app_role`@`%`                               |
+----------------------------------------------------------------------------------------------------+

+----------------------------------------------------------------------------------------------------+
| Grants for readonly_role@%                                                                         |
+----------------------------------------------------------------------------------------------------+
| GRANT SELECT ON `fgedudb`.* TO `readonly_role`@`%`                                                  |
+----------------------------------------------------------------------------------------------------+

将角色授予用户

-- 将角色授予用户
GRANT 'admin_role' TO 'admin'@'192.168.1.%';
GRANT 'app_role' TO 'app'@'192.168.1.%';

-- 查看角色分配
SELECT * FROM mysql.role_edges;

+-----------+-------------------+-----------+---------+
| FROM_HOST | FROM_USER         | TO_HOST   | TO_USER |
+-----------+-------------------+-----------+---------+
| %         | admin_role        | 192.168.1.% | admin   |
| %         | app_role          | 192.168.1.% | app     |
+-----------+-------------------+-----------+---------+

激活角色

-- 激活角色
SET DEFAULT ROLE ALL TO 'admin'@'192.168.1.%';
SET DEFAULT ROLE ALL TO 'app'@'192.168.1.%';

-- 查看默认角色
SELECT * FROM mysql.default_roles;

+-----------+-------------------+-------------------+-------------------+
| HOST      | USER              | DEFAULT_ROLE_HOST | DEFAULT_ROLE_USER |
+-----------+-------------------+-------------------+-------------------+
| 192.168.1.% | admin             | %                 | admin_role        |
| 192.168.1.% | app               | %                 | app_role          |
+-----------+-------------------+-------------------+-------------------+

删除角色

-- 撤销用户的角色
REVOKE 'admin_role' FROM 'admin'@'192.168.1.%';

-- 删除角色
DROP ROLE 'admin_role', 'app_role', 'readonly_role';

-- 查看角色
SELECT * FROM mysql.role_edges;

3.4 权限审计

查看用户权限

-- 查看所有用户的权限
SELECT user, host, plugin FROM mysql.user;

-- 查看用户的详细权限
SHOW GRANTS FOR 'root'@'%';
SHOW GRANTS FOR 'admin'@'192.168.1.%';
SHOW GRANTS FOR 'app'@'192.168.1.%';

使用TiDB Dashboard

# 访问TiDB Dashboard
# 地址：http://tidb-server:10080/dashboard

# 查看用户和权限
# 在TiDB Dashboard > 用户和权限

四、实战案例

4.1 生产环境用户和权限管理

场景：企业需要在生产环境中管理TiDB用户和权限，确保系统安全。

步骤1：创建管理员用户

-- 创建管理员用户
CREATE USER 'tidb_admin'@'192.168.1.%' IDENTIFIED BY 'Admin123!';

-- 授予管理员权限
GRANT ALL PRIVILEGES ON *.* TO 'tidb_admin'@'192.168.1.%' WITH GRANT OPTION;

-- 查看权限
SHOW GRANTS FOR 'tidb_admin'@'192.168.1.%';

+----------------------------------------------------------------------------------------------------+
| Grants for tidb_admin@192.168.1.%                                                                 |
+----------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `tidb_admin`@`192.168.1.%` WITH GRANT OPTION                         |
+----------------------------------------------------------------------------------------------------+

步骤2：创建应用用户

-- 创建应用用户
CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'App123!';

-- 授予应用权限
GRANT SELECT, INSERT, UPDATE, DELETE ON fgedudb.* TO 'app_user'@'192.168.1.%';

-- 查看权限
SHOW GRANTS FOR 'app_user'@'192.168.1.%';

+----------------------------------------------------------------------------------------------------+
| Grants for app_user@192.168.1.%                                                                    |
+----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `app_user`@`192.168.1.%`                                                     |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `fgedudb`.* TO `app_user`@`192.168.1.%`                      |
+----------------------------------------------------------------------------------------------------+

步骤3：创建只读用户

-- 创建只读用户
CREATE USER 'read_user'@'192.168.1.%' IDENTIFIED BY 'Read123!';

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

-- 查看权限
SHOW GRANTS FOR 'read_user'@'192.168.1.%';

+----------------------------------------------------------------------------------------------------+
| Grants for read_user@192.168.1.%                                                                   |
+----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `read_user`@`192.168.1.%`                                                    |
| GRANT SELECT ON `fgedudb`.* TO `read_user`@`192.168.1.%`                                             |
+----------------------------------------------------------------------------------------------------+

步骤4：创建备份用户

-- 创建备份用户
CREATE USER 'backup_user'@'192.168.1.%' IDENTIFIED BY 'Backup123!';

-- 授予备份权限
GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'backup_user'@'192.168.1.%';
GRANT SELECT ON *.* TO 'backup_user'@'192.168.1.%';

-- 查看权限
SHOW GRANTS FOR 'backup_user'@'192.168.1.%';

+----------------------------------------------------------------------------------------------------+
| Grants for backup_user@192.168.1.%                                                                 |
+----------------------------------------------------------------------------------------------------+
| GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO `backup_user`@`192.168.1.%`                |
| GRANT SELECT ON *.* TO `backup_user`@`192.168.1.%`                                                  |
+----------------------------------------------------------------------------------------------------+

步骤5：测试用户权限

-- 以app_user身份登录
mysql -h 192.168.1.13 -P 4000 -u app_user -p

-- 测试权限
USE fgedudb;
SELECT * FROM fgedu_users;
INSERT INTO fgedu_users VALUES (6, 'user6', 'user6@example.com', 50, NOW());
UPDATE fgedu_users SET age = 51 WHERE id = 6;
DELETE FROM fgedu_users WHERE id = 6;

-- 尝试创建数据库（应该失败）
CREATE DATABASE testdb;

-- 成功执行的操作
+------+----------+------------------+-----+---------------------+
| id   | username | email            | age | created_at          |
+------+----------+------------------+-----+---------------------+
|    1 | admin    | admin@example.com |  34 | 2024-04-09 10:00:00 |
|    2 | user1    | user1@example.com |  25 | 2024-04-09 10:00:00 |
|    3 | user3    | user3@example.com |  35 | 2024-04-09 11:00:00 |
+------+----------+------------------+-----+

-- 失败的操作
ERROR 1044 (42000): Access denied for user 'app_user'@'192.168.1.10' to database 'testdb'

4.2 角色管理实践

场景：企业需要使用角色管理来简化权限管理。

步骤1：创建角色

-- 创建角色
CREATE ROLE 'db_admin', 'db_app', 'db_readonly';

-- 授予角色权限
GRANT ALL PRIVILEGES ON *.* TO 'db_admin';
GRANT SELECT, INSERT, UPDATE, DELETE ON fgedudb.* TO 'db_app';
GRANT SELECT ON fgedudb.* TO 'db_readonly';

-- 查看角色权限
SHOW GRANTS FOR 'db_admin';
SHOW GRANTS FOR 'db_app';
SHOW GRANTS FOR 'db_readonly';

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

-- 创建用户
CREATE USER 'admin1'@'192.168.1.%' IDENTIFIED BY 'Admin123!';
CREATE USER 'app1'@'192.168.1.%' IDENTIFIED BY 'App123!';
CREATE USER 'read1'@'192.168.1.%' IDENTIFIED BY 'Read123!';

-- 分配角色
GRANT 'db_admin' TO 'admin1'@'192.168.1.%';
GRANT 'db_app' TO 'app1'@'192.168.1.%';
GRANT 'db_readonly' TO 'read1'@'192.168.1.%';

-- 激活角色
SET DEFAULT ROLE ALL TO 'admin1'@'192.168.1.%';
SET DEFAULT ROLE ALL TO 'app1'@'192.168.1.%';
SET DEFAULT ROLE ALL TO 'read1'@'192.168.1.%';

-- 查看角色分配
SELECT * FROM mysql.role_edges;

步骤3：测试角色权限

-- 以app1身份登录
mysql -h 192.168.1.13 -P 4000 -u app1 -p

-- 测试权限
USE fgedudb;
SELECT * FROM fgedu_users;
INSERT INTO fgedu_users VALUES (7, 'user7', 'user7@example.com', 55, NOW());

-- 尝试删除数据（应该成功）
DELETE FROM fgedu_users WHERE id = 7;

-- 尝试创建数据库（应该失败）
CREATE DATABASE testdb;

-- 成功执行的操作
+------+----------+------------------+-----+---------------------+
| id   | username | email            | age | created_at          |
+------+----------+------------------+-----+---------------------+
|    1 | admin    | admin@example.com |  34 | 2024-04-09 10:00:00 |
|    2 | user1    | user1@example.com |  25 | 2024-04-09 10:00:00 |
|    3 | user3    | user3@example.com |  35 | 2024-04-09 11:00:00 |
+------+----------+------------------+-----+

-- 失败的操作
ERROR 1044 (42000): Access denied for user 'app1'@'192.168.1.10' to database 'testdb'

五、经验总结

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

最小权限原则：只授予用户所需的最小权限
使用角色管理：通过角色管理简化权限分配
定期审计：定期审计用户权限，确保权限合理
强密码策略：设置强密码，定期更换密码
限制用户主机：限制用户的登录主机，提高安全性
分离职责：将不同职责的用户分开，避免权限滥用
备份用户权限：定期备份用户权限配置
使用SSL连接：启用SSL连接，加密数据传输

5.2 常见权限问题与解决方案

问题	原因	解决方案
访问被拒绝	用户没有相应的权限	授予用户所需的权限
权限不足	用户权限不够	增加用户权限
密码错误	密码不正确	重置用户密码
主机限制	用户只能从特定主机登录	修改用户主机限制
角色未激活	角色未设置为默认角色	激活角色

5.3 安全建议

禁用root远程登录：限制root用户只能从本地登录
使用防火墙：配置防火墙，限制数据库访问端口
启用审计日志：记录用户操作，便于安全审计
定期更新密码：定期更换用户密码
监控异常登录：监控异常登录行为，及时发现安全问题
使用加密连接：启用SSL/TLS加密连接
定期备份：定期备份数据库，防止数据丢失

5.4 用户和权限管理检查清单

检查项	配置要求	状态
用户创建	创建必要的用户，设置强密码	□
权限分配	根据最小权限原则分配权限	□
角色管理	使用角色管理简化权限分配	□
权限审计	定期审计用户权限	□
密码策略	设置强密码，定期更换	□
主机限制	限制用户登录主机	□
安全连接	启用SSL/TLS加密连接	□
备份	定期备份用户权限配置	□

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

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