#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';
-- 也可以直接使用 CREATE USER（内部等价）
CREATE USER analyst PASSWORD 'S3cur3Rep0rt';

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

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

-- 4. 删除角色（必须先回收其所有权限）
DROP ROLE report_reader;

-- 1. 给应用表级权限（最安全！不要直接给库级 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. 给应用表级权限
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';