MySQL数据库学习-基础DCL

2025-08-02

DCL，Data Control Language(数据控制语言)，用于管理数据库用户、控制数据库的访问权限。

基础

管理用户
查询用户：
USE mysql;
SELECT * FROM user；
创建用户：CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
修改用户密码：ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码'；
删除用户：DROP USER '用户名'@'主机名';
权限控制
常见权限：
ALL,ALL PRIVILEGES：所有权限
SELECT：查询数据
INSERT：插入数据
UPDATE：修改数据
DELETE：删除数据
ALTER：修改表
DROP：删除数据库/表/试图
CREATE：创建数据库/表
查询权限：SHOW GRANTS FOR ''@'';
授予权限：GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
撤销权限：REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

任务

任务零：场景准备 (DML/DQL)

为了让权限分配更有意义，我们先增加一个“数据分析师”的角色，并让他初步尝点甜头。

角色调整：我们认为产品经理（比如 'hongcha'）也需要具备数据分析的能力，请将 'hongcha' 的 role 从 'Product Manager' 修改为 'Product/Data Analyst'。注意：你之前创建 users 表时，role 字段是 ENUM 类型，可能不支持这个新值。如果报错，你需要先用 ALTER TABLE 修改 role 字段的 ENUM 定义，增加一个 'Product/Data Analyst' 的选项。

任务一：创建新用户

公司来了新人，一个初级开发和一个实习测试。你需要为他们在数据库中创建专门的账号。

创建用户：
创建一个名为 junior_dev 的用户，他只能从本机 (localhost) 访问，密码设置为 dev_password_123。
创建一个名为 intern_tester 的用户，同样只能从本机 (localhost) 访问，密码设置为 test_password_456。

任务二：精细化的权限授予 (GRANT)

这是本次任务的核心。你要为上面创建的两个新用户以及一个设想中的“只读分析”角色分配精确的权限。

给实习测试员授权：intern_tester 的工作是验证任务状态。
授予他对 industrial_project_management 数据库中所有表的查询权限 (SELECT)。他需要能看到所有项目、用户和任务的详细信息。
另外，他还需要能够更新 (UPDATE) tasks 表的 status 和 completion_date 这两个字段，以便他完成测试后更新任务状态。但他不应该能修改任务名称、描述或负责人。
他绝对不能有删除 (DELETE) 或插入 (INSERT) 的权限。
给初级开发者授权：junior_dev 的主要工作是完成被分配的任务。
授予他对 industrial_project_management 数据库中所有表的查询权限 (SELECT)。
授予他对 tasks 表的插入权限 (INSERT)，因为他可能需要分解和创建子任务。
授予他对 tasks 表的更新权限 (UPDATE)，但他不能修改 project_id。
为了数据安全，他不能删除 (DELETE) 任何任务，也不能对 projects 和 users 表做任何修改。
创建只读账号：假设公司有一个数据分析平台，需要一个账号专门用来连接数据库，进行报表展示。这个账号风险极高，绝对不能让它有任何写入或修改数据的能力。
创建一个名为 readonly_analyst 的用户，允许他从任何主机 (%) 访问，密码是 analysis_pwd_789。
授予该用户对 industrial_project_management 数据库所有表的只读权限 (SELECT)。除此之外，不授予任何其他权限。

任务三：权限的检查与回收 (SHOW GRANTS & REVOKE)

管理权限是一个持续的过程，需要随时能检查和调整。

检查权限：
分别查询 junior_dev@localhost 和 readonly_analyst@% 这两个用户当前拥有的权限，确保和你刚才授予的一致。
权限变更：
junior_dev 表现出色，现转为正式员工，可以被信任并赋予删除自己创建的任务的权限。现在决定授予他对 tasks 表的 DELETE 权限。
后来发现，允许 junior_dev 修改任务的 due_date 导致了项目延期风险。现在需要撤销他 UPDATE tasks 表中 due_date 字段的权限。（提示：撤销列级权限的语法和授予类似，但 MySQL 对此的支持细节可能比较复杂，这是一个很好的探索点）。如果直接撤销列级 UPDATE 权限遇到困难，可以先撤销整个 UPDATE 权限，再重新授予他需要的列权限。
用户离职：
实习生 intern_tester 结束了实习。你需要撤销他所有的权限。

任务四：清理用户 (DROP)

intern_tester 已离职，他的账号也就不需要了。

删除用户：从数据库中彻底删除 intern_tester@localhost 这个用户。

代码

/*

*/
use industrial_project_management;
-- USE仅仅针对DML AND DDL,DCL使用时必须写全名称。
alter table users modify role 
	enum('Developer','Tester','Product Manager','Product/Data Analyst') 
	not null default 'Developer';

update users set role='Product/Data Analyst' where username='hongcha';

CREATE USER 'junior_dev'@'localhost' IDENTIFIED BY 'dev_password_123';
CREATE USER 'intern_tester'@'localhost' IDENTIFIED BY 'test_password_456';

-- 测试权限
GRANT SELECT ON industrial_project_management.* TO 'intern_tester'@'localhost';
GRANT UPDATE(status,completion_date) ON industrial_project_management.tasks TO 'intern_tester'@'localhost';

-- 开发权限
GRANT SELECT ON industrial_project_management.* TO 'junior_dev'@'localhost';
GRANT INSERT ON industrial_project_management.tasks TO 'junior_dev'@'localhost';
GRANT UPDATE(task_id,task_name,task_details,assigned_to_user_id,status,due_date,completion_date)
ON industrial_project_management.tasks TO 'junior_dev'@'localhost';

-- 数据分析平台
CREATE USER 'readonly_analyst'@'%' IDENTIFIED BY 'analysis_pwd_789';
GRANT SELECT ON industrial_project_management.* TO 'readonly_analyst'@'%';

SHOW GRANTS FOR 'junior_dev'@'localhost';
SHOW GRANTS FOR 'intern_tester'@'localhost';
SHOW GRANTS FOR 'readonly_analyst'@'%';

GRANT delete ON industrial_project_management.tasks TO 'junior_dev'@'localhost';
revoke update on industrial_project_management.tasks from 'junior_dev'@'localhost';
GRANT update(task_id,task_name,task_details,assigned_to_user_id,status,completion_date) ON industrial_project_management.tasks TO 'junior_dev'@'localhost';

revoke all ON industrial_project_management.* FROM 'intern_tester'@'localhost';
DROP USER 'intern_tester'@'localhost';

← 返回