Jeskson-微服务分布式

DCL数据控制语言

2024/1/1

# DCL数据控制语言

DCL (Data Control Language) 数据控制语言，主要用于控制数据库的访问权限和安全性

# 概述

DCL主要包含两个核心语句：

  • GRANT：授予权限
  • REVOKE：撤销权限

这些语句用于管理数据库用户的访问权限，确保数据安全和访问控制。

# 权限类型

# 1. 系统权限

系统权限控制用户在数据库系统级别的操作能力：

-- 常见系统权限
CREATE SESSION      -- 连接数据库
CREATE TABLE        -- 创建表
CREATE VIEW         -- 创建视图
CREATE PROCEDURE    -- 创建存储过程
CREATE USER         -- 创建用户
DROP USER           -- 删除用户
ALTER USER          -- 修改用户
CREATE ROLE         -- 创建角色
DROP ANY TABLE      -- 删除任意表
SELECT ANY TABLE    -- 查询任意表

# 2. 对象权限

对象权限控制用户对特定数据库对象的操作：

-- 表权限
SELECT    -- 查询权限
INSERT    -- 插入权限
UPDATE    -- 更新权限
DELETE    -- 删除权限
ALTER     -- 修改表结构权限
INDEX     -- 创建索引权限
REFERENCES -- 引用权限（外键）

-- 视图权限
SELECT    -- 查询视图

-- 存储过程权限
EXECUTE   -- 执行权限

# GRANT语句

# 1. 基本语法

GRANT 权限列表 
ON 对象名 
TO 用户名/角色名 
[WITH GRANT OPTION];

# 2. 授予系统权限

-- 授予连接权限
GRANT CREATE SESSION TO user1;

-- 授予创建表权限
GRANT CREATE TABLE TO user1;

-- 授予多个系统权限
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW TO user1;

-- 授予DBA权限（所有权限）
GRANT DBA TO user1;

# 3. 授予对象权限

# 表权限

-- 授予单个表的查询权限
GRANT SELECT ON employees TO user1;

-- 授予多个权限
GRANT SELECT, INSERT, UPDATE ON employees TO user1;

-- 授予所有权限
GRANT ALL PRIVILEGES ON employees TO user1;

-- 授予特定列的权限
GRANT UPDATE(salary, department_id) ON employees TO user1;

# 视图权限

-- 授予视图查询权限
GRANT SELECT ON employee_view TO user1;

# 存储过程权限

-- 授予存储过程执行权限
GRANT EXECUTE ON calculate_salary TO user1;

# 4. 授予权限给角色

-- 创建角色
CREATE ROLE hr_role;
CREATE ROLE finance_role;

-- 给角色授权
GRANT SELECT, INSERT, UPDATE ON employees TO hr_role;
GRANT SELECT ON salary_view TO finance_role;

-- 将角色授予用户
GRANT hr_role TO user1;
GRANT finance_role TO user2;

# 5. WITH GRANT OPTION

-- 授予权限并允许用户将权限转授给其他用户
GRANT SELECT ON employees TO user1 WITH GRANT OPTION;

-- user1现在可以将SELECT权限授予其他用户
-- 以user1身份执行：
GRANT SELECT ON employees TO user2;

# 6. 授予权限给所有用户

-- 授予权限给所有用户
GRANT SELECT ON public_data TO PUBLIC;

# REVOKE语句

# 1. 基本语法

REVOKE 权限列表 
ON 对象名 
FROM 用户名/角色名 
[CASCADE];

# 2. 撤销系统权限

-- 撤销单个系统权限
REVOKE CREATE TABLE FROM user1;

-- 撤销多个系统权限
REVOKE CREATE SESSION, CREATE VIEW FROM user1;

-- 撤销DBA权限
REVOKE DBA FROM user1;

# 3. 撤销对象权限

-- 撤销表权限
REVOKE SELECT ON employees FROM user1;

-- 撤销多个权限
REVOKE SELECT, INSERT, UPDATE ON employees FROM user1;

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

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

# 4. 撤销角色权限

-- 撤销角色
REVOKE hr_role FROM user1;

-- 撤销角色的对象权限
REVOKE SELECT ON employees FROM hr_role;

# 5. CASCADE选项

-- 级联撤销（撤销用户及其转授的权限）
REVOKE SELECT ON employees FROM user1 CASCADE;

# 用户管理

# 1. 创建用户

-- MySQL
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'username'@'%' IDENTIFIED BY 'password';  -- 允许任意主机连接

-- PostgreSQL
CREATE USER username WITH PASSWORD 'password';

-- SQL Server
CREATE LOGIN username WITH PASSWORD = 'password';
CREATE USER username FOR LOGIN username;

-- Oracle
CREATE USER username IDENTIFIED BY password;

# 2. 修改用户

-- 修改密码
-- MySQL
ALTER USER 'username'@'localhost' IDENTIFIED BY 'new_password';

-- PostgreSQL
ALTER USER username WITH PASSWORD 'new_password';

-- SQL Server
ALTER LOGIN username WITH PASSWORD = 'new_password';

-- Oracle
ALTER USER username IDENTIFIED BY new_password;

# 3. 删除用户

-- MySQL
DROP USER 'username'@'localhost';

-- PostgreSQL
DROP USER username;

-- SQL Server
DROP USER username;
DROP LOGIN username;

-- Oracle
DROP USER username CASCADE;  -- CASCADE删除用户拥有的对象

# 角色管理

# 1. 创建角色

-- 创建角色
CREATE ROLE role_name;

-- 创建带密码的角色（PostgreSQL）
CREATE ROLE role_name WITH LOGIN PASSWORD 'password';

# 2. 角色权限管理

-- 给角色授权
GRANT SELECT, INSERT ON table_name TO role_name;

-- 将角色授予用户
GRANT role_name TO username;

-- 撤销角色
REVOKE role_name FROM username;

# 3. 删除角色

DROP ROLE role_name;

# 权限查询

# 1. 查看用户权限

-- MySQL
SHOW GRANTS FOR 'username'@'localhost';
SELECT * FROM mysql.user WHERE User = 'username';

-- PostgreSQL
\du  -- psql命令
SELECT * FROM information_schema.role_table_grants WHERE grantee = 'username';

-- SQL Server
SELECT 
    dp.name AS principal_name,
    dp.type_desc AS principal_type,
    o.name AS object_name,
    p.permission_name,
    p.state_desc AS permission_state
FROM sys.database_permissions p
LEFT JOIN sys.objects o ON p.major_id = o.object_id
LEFT JOIN sys.database_principals dp ON p.grantee_principal_id = dp.principal_id;

-- Oracle
SELECT * FROM user_tab_privs;  -- 表权限
SELECT * FROM user_sys_privs;  -- 系统权限
SELECT * FROM user_role_privs; -- 角色权限

# 2. 查看表权限

-- MySQL
SELECT * FROM information_schema.table_privileges 
WHERE table_name = 'table_name';

-- PostgreSQL
SELECT * FROM information_schema.table_privileges 
WHERE table_name = 'table_name';

-- Oracle
SELECT * FROM all_tab_privs WHERE table_name = 'TABLE_NAME';

# 实战案例

# 案例1：电商系统权限设计

-- 1. 创建角色
CREATE ROLE admin_role;
CREATE ROLE manager_role;
CREATE ROLE employee_role;
CREATE ROLE customer_role;

-- 2. 管理员角色权限（所有权限）
GRANT ALL PRIVILEGES ON ecommerce.* TO admin_role;

-- 3. 经理角色权限
GRANT SELECT, INSERT, UPDATE ON ecommerce.products TO manager_role;
GRANT SELECT, INSERT, UPDATE ON ecommerce.categories TO manager_role;
GRANT SELECT ON ecommerce.orders TO manager_role;
GRANT SELECT ON ecommerce.customers TO manager_role;

-- 4. 员工角色权限
GRANT SELECT ON ecommerce.products TO employee_role;
GRANT SELECT ON ecommerce.categories TO employee_role;
GRANT SELECT, INSERT, UPDATE ON ecommerce.orders TO employee_role;
GRANT SELECT ON ecommerce.customers TO employee_role;

-- 5. 客户角色权限（只读产品信息）
GRANT SELECT ON ecommerce.products TO customer_role;
GRANT SELECT ON ecommerce.categories TO customer_role;

-- 6. 创建用户并分配角色
CREATE USER 'admin_user'@'%' IDENTIFIED BY 'admin_pass';
CREATE USER 'manager_user'@'%' IDENTIFIED BY 'manager_pass';
CREATE USER 'employee_user'@'%' IDENTIFIED BY 'employee_pass';
CREATE USER 'customer_user'@'%' IDENTIFIED BY 'customer_pass';

GRANT admin_role TO admin_user;
GRANT manager_role TO manager_user;
GRANT employee_role TO employee_user;
GRANT customer_role TO customer_user;

# 案例2：数据分析团队权限管理

-- 1. 创建分析师角色
CREATE ROLE data_analyst;
CREATE ROLE senior_analyst;
CREATE ROLE data_scientist;

-- 2. 基础分析师权限（只读）
GRANT SELECT ON sales.orders TO data_analyst;
GRANT SELECT ON sales.customers TO data_analyst;
GRANT SELECT ON sales.products TO data_analyst;

-- 3. 高级分析师权限（可创建临时表）
GRANT data_analyst TO senior_analyst;
GRANT CREATE TEMPORARY TABLES ON sales.* TO senior_analyst;
GRANT INSERT, UPDATE, DELETE ON sales.temp_* TO senior_analyst;

-- 4. 数据科学家权限（可创建视图和存储过程）
GRANT senior_analyst TO data_scientist;
GRANT CREATE VIEW ON sales.* TO data_scientist;
GRANT CREATE ROUTINE ON sales.* TO data_scientist;

-- 5. 分配用户
CREATE USER 'analyst1'@'%' IDENTIFIED BY 'pass1';
CREATE USER 'senior_analyst1'@'%' IDENTIFIED BY 'pass2';
CREATE USER 'data_scientist1'@'%' IDENTIFIED BY 'pass3';

GRANT data_analyst TO analyst1;
GRANT senior_analyst TO senior_analyst1;
GRANT data_scientist TO data_scientist1;

# 案例3：多租户系统权限隔离

-- 1. 为每个租户创建独立的数据库
CREATE DATABASE tenant_001;
CREATE DATABASE tenant_002;
CREATE DATABASE tenant_003;

-- 2. 创建租户管理员角色
CREATE ROLE tenant_admin;
GRANT ALL PRIVILEGES ON tenant_001.* TO tenant_admin;

-- 3. 创建租户用户角色
CREATE ROLE tenant_user;
GRANT SELECT, INSERT, UPDATE ON tenant_001.orders TO tenant_user;
GRANT SELECT, INSERT, UPDATE ON tenant_001.customers TO tenant_user;

-- 4. 为每个租户创建专用用户
CREATE USER 'tenant_001_admin'@'%' IDENTIFIED BY 'admin_pass_001';
CREATE USER 'tenant_001_user1'@'%' IDENTIFIED BY 'user_pass_001';

-- 5. 分配权限（确保租户间隔离）
GRANT ALL PRIVILEGES ON tenant_001.* TO 'tenant_001_admin'@'%';
GRANT SELECT, INSERT, UPDATE ON tenant_001.* TO 'tenant_001_user1'@'%';

-- 6. 禁止跨租户访问
REVOKE ALL PRIVILEGES ON tenant_002.* FROM 'tenant_001_admin'@'%';
REVOKE ALL PRIVILEGES ON tenant_003.* FROM 'tenant_001_admin'@'%';

# 安全最佳实践

# 1. 最小权限原则

-- 只授予必要的权限
-- 不好的做法
GRANT ALL PRIVILEGES ON *.* TO 'app_user'@'%';

-- 好的做法
GRANT SELECT, INSERT, UPDATE ON app_db.users TO 'app_user'@'%';
GRANT SELECT ON app_db.products TO 'app_user'@'%';

# 2. 使用角色管理权限

-- 创建角色而不是直接给用户授权
CREATE ROLE app_read_role;
CREATE ROLE app_write_role;

GRANT SELECT ON app_db.* TO app_read_role;
GRANT INSERT, UPDATE ON app_db.users TO app_write_role;

-- 将角色授予用户
GRANT app_read_role TO 'readonly_user'@'%';
GRANT app_read_role, app_write_role TO 'readwrite_user'@'%';

# 3. 定期审计权限

-- 定期检查用户权限
SELECT 
    User,
    Host,
    Select_priv,
    Insert_priv,
    Update_priv,
    Delete_priv,
    Create_priv,
    Drop_priv
FROM mysql.user
WHERE User NOT IN ('root', 'mysql.sys', 'mysql.session');

-- 检查过期或不活跃的用户
SELECT 
    User,
    Host,
    password_last_changed,
    account_locked
FROM mysql.user
WHERE password_last_changed < DATE_SUB(NOW(), INTERVAL 90 DAY);

# 4. 密码策略

-- 设置密码策略（MySQL 8.0+）
SET GLOBAL validate_password.policy = 'MEDIUM';
SET GLOBAL validate_password.length = 8;
SET GLOBAL validate_password.mixed_case_count = 1;
SET GLOBAL validate_password.number_count = 1;
SET GLOBAL validate_password.special_char_count = 1;

-- 强制密码过期
ALTER USER 'username'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

# 5. 连接限制

-- 限制用户连接数
CREATE USER 'limited_user'@'%' 
IDENTIFIED BY 'password'
WITH MAX_CONNECTIONS_PER_HOUR 100
     MAX_QUERIES_PER_HOUR 1000
     MAX_UPDATES_PER_HOUR 500;

# 常见错误与解决方案

# 1. 权限不足错误

-- 错误：Access denied for user 'username'@'host'
-- 解决：检查并授予必要权限
GRANT SELECT ON database.table TO 'username'@'host';
FLUSH PRIVILEGES;

# 2. 角色权限问题

-- 确保角色被正确激活（MySQL 8.0+）
SET DEFAULT ROLE ALL TO 'username'@'host';

-- 或在连接时激活角色
SET ROLE ALL;

# 3. 权限传播问题

-- 撤销WITH GRANT OPTION权限时使用CASCADE
REVOKE SELECT ON table_name FROM 'username'@'host' CASCADE;

# 不同数据库的DCL差异

# MySQL

  • 使用FLUSH PRIVILEGES刷新权限缓存
  • 支持基于主机的权限控制
  • MySQL 8.0+引入了角色概念

# PostgreSQL

  • 权限系统更加细粒度
  • 支持列级权限
  • 内置角色系统

# SQL Server

  • 区分登录(Login)和用户(User)
  • 支持Windows认证
  • 复杂的权限继承体系

# Oracle

  • 强大的角色和权限系统
  • 支持细粒度审计
  • VPD(Virtual Private Database)功能

# 总结

DCL是数据库安全的核心，通过合理的权限设计和管理，可以确保数据的安全性和访问控制。在实际应用中，应该遵循最小权限原则，使用角色管理权限，定期审计和更新权限设置，确保数据库系统的安全性。

SELECT查询基础