#SQL详解之DCL - 数据控制语言完整指南

-- 1. 创建用户（Host很重要！防远程瞎连）
CREATE USER 'webapp'@'localhost' IDENTIFIED BY 'P@ssw0rd!202x'; -- 只能本地连
CREATE USER 'analyst'@'192.168.1.%' IDENTIFIED BY 'S3cur3Rep0rt'; -- 仅指定IP段

-- 2. 查看现有用户
SELECT User, Host FROM mysql.user;

-- 3. 改密码（8.0推荐用ALTER，兼容旧版本）
ALTER USER 'webapp'@'localhost' IDENTIFIED BY 'N3wP@ss!202x';

-- 4. 删用户
DROP USER 'analyst'@'192.168.1.%';

-- 1. 创建能登录的用户
CREATE ROLE webapp LOGIN PASSWORD 'P@ssw0rd!202x';
-- 或者等价的简化版（psql支持CREATE USER）
CREATE USER analyst PASSWORD 'S3cur3Rep0rt';

-- 2. 创建不能登录的组（用于批量授权）
CREATE ROLE app_developer NOLOGIN;
CREATE ROLE report_reader NOLOGIN;

-- 3. 查看现有角色（psql快捷命令\du，通用SQL也行）
\du
SELECT rolname, rolsuper, rolcreatedb FROM pg_roles;

-- 4. 删角色（必须先删依赖的权限）
DROP ROLE report_reader;

-- 1. 给Web应用表级权限（最常用！别给库级ALL）
GRANT SELECT, INSERT, UPDATE ON company_db.employees TO 'webapp'@'localhost';
GRANT SELECT ON company_db.departments TO 'webapp'@'localhost';

-- 2. 给管理员临时库级权限
GRANT ALL PRIVILEGES ON company_db.* TO 'dba_temp'@'localhost';

-- 3. 刷新权限（ALTER/GRANT后通常自动刷新，CREATE/DROP可能需要）
FLUSH PRIVILEGES;

-- 4. 查看权限
SHOW GRANTS FOR 'webapp'@'localhost';

-- 1. 收单个权限
REVOKE UPDATE ON company_db.employees FROM 'webapp'@'localhost';

-- 2. 收所有权限
REVOKE ALL PRIVILEGES ON company_db.* FROM 'dba_temp'@'localhost';

-- 3. 必须刷新！
FLUSH PRIVILEGES;

-- 0. 必做！先给public模式的使用权限
GRANT USAGE ON SCHEMA public TO webapp;

-- 1. 给Web应用表级权限
GRANT SELECT, INSERT, UPDATE ON TABLE employees TO webapp;
GRANT SELECT ON TABLE departments TO webapp;

-- 2. 批量授权现有表（避免一个个写）
GRANT SELECT ON ALL TABLES IN SCHEMA public TO report_reader;

-- 3. 批量授权未来的表（这功能MySQL 8.0才有基础版！）
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT, INSERT ON TABLES TO webapp;

-- 4. 查看权限（psql快捷命令\dp，通用SQL也行）
\dp employees

-- 1. 收单个表权限
REVOKE UPDATE ON TABLE employees FROM webapp;

-- 2. 收未来的表权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
REVOKE SELECT, INSERT ON TABLES FROM webapp;

CREATE USER 'ecommerce'@'localhost' IDENTIFIED BY 'Ec0mm3rc3!202x';
GRANT SELECT, INSERT, UPDATE ON ecommerce_db.orders TO 'ecommerce'@'localhost';
GRANT SELECT, INSERT ON ecommerce_db.order_items TO 'ecommerce'@'localhost';
GRANT SELECT ON ecommerce_db.products TO 'ecommerce'@'localhost';
FLUSH PRIVILEGES;

-- 1. 建组
CREATE ROLE app_developer NOLOGIN;
CREATE ROLE sales_reader NOLOGIN;
CREATE ROLE finance_writer NOLOGIN;

-- 2. 给组批量授权
GRANT USAGE ON SCHEMA public TO app_developer, sales_reader, finance_writer;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO app_developer;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO sales_reader;
GRANT SELECT, UPDATE ON finance_salaries TO finance_writer;

-- 3. 给未来的表授权（自动继承到组）
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT ALL PRIVILEGES ON TABLES TO app_developer;

-- 4. 加人到组
CREATE USER lily LOGIN IN ROLE app_developer PASSWORD 'L1lyDev!202x';
CREATE USER tom LOGIN IN ROLE sales_reader PASSWORD 'T0mS3ll!202x';