DCL

1. DCL 概述

数据控制语言（Data Control Language，DCL）用于管理数据库访问权限，核心语句为 GRANT 和 REVOKE。

1.1 权限管理模型

用户(User) ──授予──→ 角色(Role) ──拥有──→ 权限(Privilege) ──作用于──→ 对象(Object)

1.2 权限分类

2. GRANT 授予权限

2.1 授予对象权限

-- 授予表权限
GRANT SELECT ON employees TO user_read;
GRANT SELECT, INSERT, UPDATE ON employees TO user_write;
GRANT ALL PRIVILEGES ON employees TO admin_user;

-- 授予列级权限
GRANT SELECT (name, dept_id) ON employees TO user_limited;
GRANT UPDATE (salary) ON employees TO hr_manager;

-- 授予视图权限
GRANT SELECT ON employee_view TO reporting_user;

-- 授予函数执行权限
GRANT EXECUTE ON FUNCTION calculate_bonus(INTEGER) TO hr_user;

2.2 授予模式权限

-- 授予模式使用权限
GRANT USAGE ON SCHEMA hr TO user_read;

-- 授予模式下创建对象的权限
GRANT CREATE ON SCHEMA hr TO developer;

2.3 授予系统权限

-- PostgreSQL
GRANT CREATEDB TO user_admin;
GRANT CREATEROLE TO user_admin;

-- 授予所有表的权限（PostgreSQL）
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_role;

-- 授予未来创建对象的默认权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO readonly_role;

2.4 WITH GRANT OPTION

-- 允许被授权者将权限授予他人
GRANT SELECT ON employees TO manager WITH GRANT OPTION;

-- manager 可以进一步授权
-- manager 执行：
GRANT SELECT ON employees TO staff;

2.5 授予角色

-- 创建角色
CREATE ROLE readonly;
CREATE ROLE readwrite;
CREATE ROLE admin;

-- 为角色授权
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO readwrite;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO admin;

-- 将角色授予用户
GRANT readonly TO user_read;
GRANT readwrite TO user_write;
GRANT admin TO user_admin;

-- 角色继承
GRANT readonly TO readwrite;  -- readwrite 继承 readonly 的权限

3. REVOKE 撤销权限

3.1 撤销对象权限

-- 撤销特定权限
REVOKE SELECT ON employees FROM user_read;

-- 撤销所有权限
REVOKE ALL PRIVILEGES ON employees FROM user_write;

-- 撤销列级权限
REVOKE UPDATE (salary) ON employees FROM hr_manager;

3.2 CASCADE 与 RESTRICT

-- CASCADE：级联撤销（同时撤销被该用户授予他人的权限）
REVOKE SELECT ON employees FROM manager CASCADE;

-- RESTRICT：如果有依赖权限则报错（默认行为）
REVOKE SELECT ON employees FROM manager RESTRICT;

3.3 撤销角色

REVOKE readonly FROM user_read;
REVOKE admin FROM user_admin;

4. 权限层级

4.1 权限继承链

数据库实例
└── 数据库
    └── 模式(Schema)
        └── 表(Table)
            └── 列(Column)

-- 上层权限不自动继承到下层
-- 需要显式授予每层权限
GRANT USAGE ON DATABASE mydb TO user1;
GRANT USAGE ON SCHEMA public TO user1;
GRANT SELECT ON TABLE employees TO user1;

4.2 权限检查顺序

1. 检查用户是否为超级用户（超级用户跳过所有检查）
2. 检查用户是否直接拥有权限
3. 检查用户所属角色是否拥有权限
4. 检查 PUBLIC 角色是否拥有权限
5. 以上都不满足则拒绝

5. 查看权限

5.1 信息模式查询

-- 查看表的权限
SELECT grantee, privilege_type
FROM information_schema.table_privileges
WHERE table_name = 'employees';

-- 查看列级权限
SELECT grantee, column_name, privilege_type
FROM information_schema.column_privileges
WHERE table_name = 'employees';

5.2 PostgreSQL 专用查询

-- 查看角色成员
SELECT r.rolname, m.rolname AS member_of
FROM pg_roles r
JOIN pg_auth_members am ON r.oid = am.roleid
JOIN pg_roles m ON am.member = m.oid;

-- 查看表权限
SELECT relname, acl
FROM pg_class
WHERE relname = 'employees';

6. 安全最佳实践

6.1 最小权限原则

-- 只授予必要的权限
-- 不推荐
GRANT ALL PRIVILEGES ON DATABASE mydb TO app_user;

-- 推荐
GRANT USAGE ON SCHEMA app TO app_user;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA app TO app_user;

6.2 使用角色管理

-- 创建角色体系
CREATE ROLE app_readonly;
CREATE ROLE app_readwrite;
CREATE ROLE app_admin;

-- 授予角色权限
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_readonly;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_readwrite;
GRANT ALL PRIVILEGES ON SCHEMA app TO app_admin;

-- 将角色分配给用户
GRANT app_readonly TO reporting_service;
GRANT app_readwrite TO application_service;
GRANT app_admin TO dba_user;

6.3 避免使用 PUBLIC 权限

-- 撤销 PUBLIC 的默认权限
REVOKE ALL ON SCHEMA public FROM PUBLIC;

-- 显式授予需要的权限
GRANT USAGE ON SCHEMA public TO authenticated_role;