Python从入门到精通day38

SQL详解之DCL（数据控制语言）

上一节课我们讲解了DQL的数据查询操作，本节课将继续基于「学校选课系统数据库（school）」，详细讲解DCL（Data Control Language，数据控制语言）的使用。DCL的核心作用是管理数据库用户权限，确保数据库数据安全，是数据库管理员（DBA）最常用的SQL指令，其核心语句为GRANT（授予权限） 和 REVOKE（回收权限），搭配CREATE USER、FLUSH PRIVILEGES等语句可实现完整的权限管理。

温馨提示：执行所有DCL操作前，请先确认已登录数据库服务器（如使用root账号），确保拥有最高权限，避免操作失败。若已切换到school数据库，可直接执行以下DCL语句；若未切换，无需额外切换，DCL操作不依赖具体数据库。

一、DCL基础：创建数据库用户

管理权限的前提是拥有数据库用户，使用CREATE USER语句可创建新的数据库用户，并为其指定访问口令，同时可限制用户的访问主机（如任意主机、指定网段）。

1. 1. 基础创建（允许任意主机访问）

语法：CREATE USER '用户名'@'访问主机' IDENTIFIED BY '访问口令'；其中@'%'表示允许用户从任意主机访问数据库服务器，适合测试或全局访问场景。

-- 创建用户wangdachui，允许从任意主机访问，口令为Wang.618（口令建议包含大小写、数字、特殊字符）CREATEUSER'wangdachui'@'%' IDENTIFIED BY'Wang.618';

说明：用户名和访问主机需用''包裹，访问主机支持通配符，%表示任意字符，后续会讲解限制访问网段的写法。

1. 2. 限制访问网段（推荐生产环境使用）

实际生产环境中，为保障安全，需限制用户仅能从指定网段或主机访问，此时需先删除原有同名用户（避免冲突），再重新创建并指定访问网段。

-- 先删除已存在的同名用户（避免创建失败，IF EXISTS表示存在则删除，不存在不报错）DROPUSER IF EXISTS'wangdachui'@'%';-- 创建用户，仅允许192.168.0.x网段的主机访问（%匹配网段内任意主机）CREATEUSER'wangdachui'@'192.168.0.%' IDENTIFIED BY'Wang.618';

注意：新创建的用户默认无任何操作权限，即使能成功登录数据库服务器，也无法查询、修改任何数据，需手动授予权限后才能操作。

二、DCL核心：授予用户权限（GRANT）

使用GRANT语句为已创建的用户授予指定权限，核心原则是权限最小化（仅授予用户完成工作所需的最少权限），避免权限滥用导致数据库安全风险。GRANT语法：GRANT 权限列表 ON 数据库.表 TO '用户名'@'访问主机'；

1. 1. 授予单表查询权限

适用于仅需查询单个表数据的场景（如普通操作员仅需查看学生信息表），指定具体数据库和表名，授予SELECT权限。

-- 为wangdachui授予查询school数据库中tb_college表（学院表）的权限GRANTSELECTON `school`.`tb_college` TO'wangdachui'@'192.168.0.%';

说明：school.tb_college中``用于避免数据库名、表名与SQL关键字冲突，推荐常规使用。

1. 2. 授予数据库级查询权限

适用于需查询某个数据库下所有表数据的场景（如数据分析师需查看school数据库所有表），用*表示数据库下所有对象（表、视图等）。

-- 为wangdachui授予查询school数据库所有对象的权限GRANTSELECTON `school`.*TO'wangdachui'@'192.168.0.%';

1. 3. 授予增删改权限（DML权限）

适用于需对数据进行插入、删除、修改操作的场景（如数据录入员），可同时授予INSERT、DELETE、UPDATE三种权限，也可单独授予某一种。

-- 为wangdachui授予school数据库所有对象的增删改权限（搭配之前的查询权限，满足完整操作）GRANTINSERT, DELETE, UPDATEON `school`.*TO'wangdachui'@'192.168.0.%';

1. 4. 授予DDL操作权限

适用于需创建、删除、修改表结构的场景（如初级DBA），授予CREATE（创建表）、DROP（删除表）、ALTER（修改表结构）权限。

-- 为wangdachui授予school数据库所有对象的DDL操作权限GRANTCREATE, DROP, ALTERON `school`.*TO'wangdachui'@'192.168.0.%';

1. 5. 授予全部权限（不推荐）

可授予用户对所有数据库、所有对象的全部操作权限，等价于root账号权限，仅适合数据库管理员，普通用户绝不推荐授予。

-- 为wangdachui授予所有数据库所有对象的全部权限（谨慎使用，存在严重安全风险）GRANTALL PRIVILEGES ON*.*TO'wangdachui'@'192.168.0.%';

易错提示：普通用户授予ALL PRIVILEGES权限后，可修改、删除所有数据库数据和表结构，极易导致数据丢失，违背“权限最小化”原则。

三、DCL核心：回收用户权限（REVOKE）

当用户不再需要某类权限（如员工离职、岗位调整）时，需使用REVOKE语句回收已授予的权限，语法与GRANT对应，核心：REVOKE 权限列表 ON 数据库.表 FROM '用户名'@'访问主机'；

1. 1. 回收指定权限

适用于仅回收用户部分权限的场景（如取消数据录入员的删除权限），指定需回收的权限列表和对应数据库、表。

-- 回收wangdachui对school数据库所有对象的增删改权限，保留查询和DDL权限REVOKEINSERT, DELETE, UPDATEON `school`.*FROM'wangdachui'@'192.168.0.%';

1. 2. 回收全部权限

适用于用户完全不需要访问数据库的场景（如员工离职），回收用户的所有权限，使其无法再操作任何数据库对象。

-- 回收wangdachui的所有权限（针对所有数据库、所有对象）REVOKEALL PRIVILEGES ON*.*FROM'wangdachui'@'192.168.0.%';

1. 3. 使权限即时生效（FLUSH PRIVILEGES）

数据库会缓存用户的权限信息，授予或回收权限后，若权限未即时生效，需执行FLUSH PRIVILEGES语句刷新权限缓存，确保新权限立即生效。

-- 刷新权限缓存，使授予/回收的权限即时生效FLUSH PRIVILEGES;

说明：部分数据库版本（如MySQL 8.0+）授予/回收权限后会自动刷新缓存，但为确保兼容性，建议每次操作后手动执行该语句。

四、补充知识点：DCL核心注意事项与权限原则

结合本节课DCL示例，补充核心注意事项和权限管理原则，帮助大家规范使用DCL，保障数据库安全。

1. 1. 核心注意事项

• • 创建用户时，访问主机需精准控制，生产环境优先限制指定网段（如192.168.0.%），避免使用%（任意主机）；
• • 授予权限时，严格遵循“权限最小化”，仅授予用户完成工作所需的最少权限，不冗余授权；
• • 回收权限后，若用户已登录数据库，需重新登录才能生效（或执行FLUSH PRIVILEGES强制刷新）；
• • 删除用户需使用DROP USER语句，仅回收权限不会删除用户，用户仍可登录（无任何权限）。

1. 2. 权限管理核心原则

数据库权限管理的核心是“最小权限原则”，其核心逻辑的如下：

1. 1. 按需授权：根据用户岗位、工作职责，授予对应权限（如数据录入员仅授予INSERT、UPDATE权限）；
2. 2. 精准授权：权限范围精准到具体数据库、具体表，不授予超出范围的权限；
3. 3. 动态调整：用户岗位变动、离职时，及时回收对应权限，避免权限泄露；
4. 4. 定期审计：定期检查用户权限，清理冗余权限、过期用户，确保权限管理规范。