内容简介：本文主要介绍MySQL相关知识，包括安装、配置、优化等内容。风哥教程参考MySQL官方文档MySQL SQL Syntax、MySQL Server Administration。 01 更多视频教程www.fgedu.net.cn 02 学习交流加群风哥微信: itpux-com

Part01-基础概念与理论知识

DCL（Data Control Language，数据控制语言）是SQL语言的一个重要组成部分，用于管理数据库的权限和用户。在MySQL中，DCL语句主要包括GRANT（授权）和REVOKE（撤销权限）语句，用于控制用户对数据库对象的访问权限。 03 学习交流加群风哥QQ113257174

1.1 DCL语句的特点

• DCL语句用于管理用户权限和安全
• DCL语句执行后会自动提交，不需要手动COMMIT
• DCL语句会影响用户对数据库对象的访问权限

1.2 常用DCL语句分类

语句	作用
GRANT	授予用户权限
REVOKE	撤销用户权限
CREATE USER	创建用户（严格来说属于DDL，但与权限管理相关）
DROP USER	删除用户（严格来说属于DDL，但与权限管理相关）
ALTER USER	修改用户属性（严格来说属于DDL，但与权限管理相关）

Part02-生产环境规划与建议

2.1 创建用户

-- 创建用户
CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password123';

-- 创建用户并指定密码过期时间
CREATE USER 'user2'@'%' IDENTIFIED BY 'password456' PASSWORD EXPIRE INTERVAL 90 DAY;

-- 创建用户并限制连接数
CREATE USER 'user3'@'192.168.1.%' IDENTIFIED BY 'password789' WITH MAX_USER_CONNECTIONS 10;

2.2 修改用户

-- 修改用户密码
ALTER USER 'user1'@'localhost' IDENTIFIED BY 'newpassword123';

-- 修改用户密码过期时间
ALTER USER 'user2'@'%' PASSWORD EXPIRE INTERVAL 60 DAY;

-- 禁用用户
ALTER USER 'user3'@'192.168.1.%' ACCOUNT LOCK;

-- 启用用户
ALTER USER 'user3'@'192.168.1.%' ACCOUNT UNLOCK;

2.3 删除用户

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

-- 安全删除用户（如果存在）
DROP USER IF EXISTS 'user2'@'%';

Part03-生产环境项目实施方案

3.1 基本GRANT语句

-- 授予用户对特定数据库的所有权限
GRANT ALL PRIVILEGES ON db_name.* TO 'user1'@'localhost';

-- 授予用户对特定表的特定权限
GRANT SELECT, INSERT, UPDATE ON db_name.table_name TO 'user2'@'%';

-- 授予用户权限并允许其授予给其他用户
GRANT ALL PRIVILEGES ON db_name.* TO 'user3'@'192.168.1.%' WITH GRANT OPTION;

3.2 常用权限类型

权限类型	作用
ALL PRIVILEGES	所有权限（除了GRANT OPTION）
SELECT	查询数据的权限
INSERT	插入数据的权限
UPDATE	更新数据的权限
DELETE	删除数据的权限
CREATE	创建数据库或表的权限
DROP	删除数据库或表的权限
ALTER	修改表结构的权限
INDEX	创建或删除索引的权限
GRANT OPTION	授予其他用户权限的权限

3.3 权限作用域

-- 全局权限（所有数据库）
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost';

-- 数据库级权限（特定数据库）
GRANT ALL PRIVILEGES ON db_name.* TO 'db_user'@'%';

-- 表级权限（特定表）
GRANT SELECT, INSERT ON db_name.table_name TO 'table_user'@'192.168.1.%';

-- 列级权限（特定列）
GRANT SELECT (id, name), UPDATE (salary) ON db_name.employees TO 'column_user'@'localhost';

3.4 角色权限管理

-- 创建角色
CREATE ROLE 'read_only', 'read_write';

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

-- 将角色授予用户
GRANT 'read_only' TO 'user1'@'localhost';
GRANT 'read_write' TO 'user2'@'%';

-- 激活角色
SET DEFAULT ROLE 'read_only' TO 'user1'@'localhost';
SET DEFAULT ROLE 'read_write' TO 'user2'@'%';

Part04-生产案例与实战讲解

4.1 基本REVOKE语句

-- 撤销用户的所有权限
REVOKE ALL PRIVILEGES ON db_name.* FROM 'user1'@'localhost';

-- 撤销用户的特定权限
REVOKE INSERT, UPDATE ON db_name.table_name FROM 'user2'@'%';

-- 撤销用户的GRANT OPTION权限
REVOKE GRANT OPTION ON db_name.* FROM 'user3'@'192.168.1.%';

4.2 撤销角色权限

-- 从用户撤销角色
REVOKE 'read_only' FROM 'user1'@'localhost';

-- 撤销角色的权限
REVOKE SELECT ON *.* FROM 'read_only';

Part05-风哥经验总结与分享

5.1 最小权限原则

• 只授予用户完成任务所需的最小权限
• 避免使用ALL PRIVILEGES，除非绝对必要
• 根据用户的实际需求分配权限

5.2 用户管理最佳实践

• 为不同的用户创建不同的账号
• 使用强密码策略
• 定期更新用户密码
• 及时删除不需要的用户账号
• 限制用户的连接来源（使用主机名或IP地址）

5.3 权限管理最佳实践

• 使用角色来管理权限，简化权限管理
• 定期审计用户权限
• 使用SSL加密连接
• 启用密码过期策略
• 限制用户的连接数和资源使用

6. 权限查看

6.1 查看用户权限

-- 查看用户的权限
SHOW GRANTS FOR 'user1'@'localhost';

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

6.2 查看角色权限

-- 查看角色的权限
SHOW GRANTS FOR 'read_only';

-- 查看用户的角色
SHOW GRANTS FOR 'user1'@'localhost' USING 'read_only';

6.3 查看权限表

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

-- 查看权限表
SELECT * FROM mysql.db WHERE user = 'user1';
SELECT * FROM mysql.tables_priv WHERE user = 'user1';
SELECT * FROM mysql.columns_priv WHERE user = 'user1';

7. 常见错误和解决方案

7.1 权限错误

错误信息	原因	解决方案
ERROR 1045 (28000): Access denied for user ‘user1’@’localhost’ (using password: YES)	密码错误或用户没有权限	检查密码是否正确，或授予用户相应权限
ERROR 1142 (42000): GRANT command denied to user ‘user1’@’localhost’ for table ‘table_name’	用户没有GRANT权限	使用具有GRANT权限的用户执行授权操作
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘IDENTIFIED BY ‘password” at line 1	语法错误，MySQL 8.0+中CREATE USER和GRANT语句分离	先创建用户，再授予权限

7.2 用户管理错误

错误信息	原因	解决方案
ERROR 1396 (HY000): Operation CREATE USER failed for ‘user1’@’localhost’	用户已存在	使用CREATE USER IF NOT EXISTS或先删除现有用户
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements	密码不符合安全策略	使用更复杂的密码，或修改密码策略

8. 示例演示

8.1 完整的权限管理示例

-- 创建测试数据库
CREATE DATABASE test_db;

-- 创建用户
CREATE USER 'readonly_user'@'localhost' IDENTIFIED BY 'readonly123';
CREATE USER 'readwrite_user'@'%' IDENTIFIED BY 'readwrite123';
CREATE USER 'admin_user'@'192.168.1.%' IDENTIFIED BY 'admin123';

-- 授予权限
GRANT SELECT ON test_db.* TO 'readonly_user'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON test_db.* TO 'readwrite_user'@'%';
GRANT ALL PRIVILEGES ON test_db.* TO 'admin_user'@'192.168.1.%' WITH GRANT OPTION;

-- 创建表
USE test_db;
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    age INT,
    salary DECIMAL(10,2)
);

-- 插入测试数据
INSERT INTO employees (name, age, salary) VALUES
('风哥1号', 28, 10000),
('风哥2号', 32, 15000),
('王五', 25, 6000);

-- 测试权限
-- 使用readonly_user登录
-- SELECT * FROM test_db.employees; -- 允许
-- INSERT INTO test_db.employees (name, age, salary) VALUES ('赵六', 30, 12000); -- 拒绝

-- 使用readwrite_user登录
-- SELECT * FROM test_db.employees; -- 允许
-- INSERT INTO test_db.employees (name, age, salary) VALUES ('赵六', 30, 12000); -- 允许
-- DROP TABLE test_db.employees; -- 拒绝

-- 使用admin_user登录
-- SELECT * FROM test_db.employees; -- 允许
-- DROP TABLE test_db.employees; -- 允许

-- 撤销权限
REVOKE DELETE ON test_db.* FROM 'readwrite_user'@'%';

-- 删除用户
DROP USER IF EXISTS 'readonly_user'@'localhost';
DROP USER IF EXISTS 'readwrite_user'@'%';
DROP USER IF EXISTS 'admin_user'@'192.168.1.%';

8.2 角色管理示例

-- 创建角色
CREATE ROLE 'developer', 'analyst', 'manager';

-- 给角色授予权限
GRANT SELECT, INSERT, UPDATE, DELETE ON test_db.* TO 'developer';
GRANT SELECT ON test_db.* TO 'analyst';
GRANT ALL PRIVILEGES ON test_db.* TO 'manager' WITH GRANT OPTION;

-- 创建用户并分配角色
CREATE USER 'dev1'@'localhost' IDENTIFIED BY 'dev123';
CREATE USER 'analyst1'@'%' IDENTIFIED BY 'analyst123';
CREATE USER 'manager1'@'192.168.1.%' IDENTIFIED BY 'manager123';

-- 分配角色
GRANT 'developer' TO 'dev1'@'localhost';
GRANT 'analyst' TO 'analyst1'@'%';
GRANT 'manager' TO 'manager1'@'192.168.1.%';

-- 激活角色
SET DEFAULT ROLE 'developer' TO 'dev1'@'localhost';
SET DEFAULT ROLE 'analyst' TO 'analyst1'@'%';
SET DEFAULT ROLE 'manager' TO 'manager1'@'192.168.1.%';

-- 查看角色权限
SHOW GRANTS FOR 'dev1'@'localhost';
SHOW GRANTS FOR 'analyst1'@'%';
SHOW GRANTS FOR 'manager1'@'192.168.1.%';

-- 撤销角色
REVOKE 'developer' FROM 'dev1'@'localhost';

-- 删除角色
DROP ROLE 'developer', 'analyst', 'manager';

9. 总结

DCL语句是MySQL数据库安全管理的重要组成部分，用于管理用户权限和访问控制。本文详细介绍了MySQL中常用的DCL语句，包括用户管理、权限授予、权限撤销和角色管理等。 04 风哥提示：

在使用DCL语句时，需要注意以下几点： 05更多学习教程公众号风哥教程itpux_com

• 遵循最小权限原则，只授予用户完成任务所需的最小权限
• 使用角色来简化权限管理
• 定期审计用户权限，确保权限设置合理
• 使用强密码策略，定期更新密码
• 限制用户的连接来源，提高安全性
• 及时删除不需要的用户账号

通过掌握DCL语句的使用方法，可以有效地管理MySQL数据库的权限，提高数据库的安全性和可维护性。 06 from mysql视频:www.itpux.com