【数据库原理】存取控制 SQL 语句案例

存取控制是通过 SQL 实现对数据库对象（如表、视图、列、行等）权限的细粒度管理，确保用户只能访问他们被授权的资源。以下从权限管理模板和具体应用分类出发，详细讲解如何实现存取控制。

 

一、存取控制的核心分类

1. 自主存取控制（DAC）

    •    数据所有者决定其他用户的权限。

    •    适合大多数通用数据库权限管理。

 

2. 强制存取控制（MAC）

    •    系统通过安全级别自动控制用户访问。

    •    应用于高安全性环境（如政府或军用系统）。

 

3. 基于角色的存取控制（RBAC）

    •    用户通过角色间接获得权限，方便集中管理。

    •    常用于企业级权限管理。

 

4. 基于属性的存取控制（ABAC）

    •    通过用户属性或环境属性动态控制访问权限。

    •    适合复杂应用场景，如时间、地点限制。

 

二、存取控制 SQL 模板和应用

（一）基本权限管理：DAC

    1.    授予权限

    •    模板：

GRANT {权限类型} ON {对象} TO {用户或角色};

    •    示例：

-- 授予UserA对表Employees的SELECT权限

GRANT SELECT ON Employees TO UserA;

-- 授予UserB对表Orders的INSERT和DELETE权限

GRANT INSERT, DELETE ON Orders TO UserB;

 

    2.    收回权限

    •    模板：

REVOKE {权限类型} ON {对象} FROM {用户或角色};

    •    示例：

-- 收回UserA对表Employees的SELECT权限

REVOKE SELECT ON Employees FROM UserA;

-- 收回UserB对表Orders的INSERT权限

REVOKE INSERT ON Orders FROM UserB;

 

    3.    列级别权限

    •    模板：

GRANT {权限类型} ({列名}) ON {对象} TO {用户或角色};

    •    示例：

-- 允许UserA只查看表Employees中的Name列

GRANT SELECT (Name) ON Employees TO UserA;

 

    4.    行级别权限

    •    使用行级安全策略。

    •    模板：

CREATE POLICY {策略名}

ON {表名}

FOR {操作类型}

USING ({条件});

    •    示例：

-- UserA只能查看部门为'Sales'的记录

CREATE POLICY Sales_Only

ON Employees

FOR SELECT

USING (Department = 'Sales');

 

（二）角色管理：RBAC

    1.    创建角色

    •    模板：

CREATE ROLE {角色名};

    •    示例：

-- 创建Manager角色

CREATE ROLE Manager;

 

    2.    角色权限分配

    •    模板：

GRANT {权限类型} ON {对象} TO {角色};

    •    示例：

-- 为Manager角色分配SELECT和UPDATE权限

GRANT SELECT, UPDATE ON Employees TO Manager;

 

    3.    用户角色绑定

    •    模板：

GRANT {角色} TO {用户};

    •    示例：

-- 将Manager角色赋予UserA

GRANT Manager TO UserA;

 

    4.    移除角色

    •    模板：

REVOKE {角色} FROM {用户};

    •    示例：

-- 从UserA移除Manager角色

REVOKE Manager FROM UserA;

 

（三）动态权限管理：ABAC

    1.    基于时间的访问控制

    •    模板：

DO $$

BEGIN

  IF CURRENT_TIME BETWEEN {开始时间} AND {结束时间} THEN

    GRANT {权限类型} ON {对象} TO {用户};

  ELSE

    REVOKE {权限类型} ON {对象} FROM {用户};

  END IF;

END $$;

    •    示例：

-- 限制UserA只能在工作时间访问表Orders

DO $$

BEGIN

  IF CURRENT_TIME BETWEEN '09:00:00' AND '18:00:00' THEN

    GRANT SELECT ON Orders TO UserA;

  ELSE

    REVOKE SELECT ON Orders FROM UserA;

  END IF;

END $$;

 

    2.    基于环境属性的访问控制

    •    示例：

-- 定义策略：UserB只能从特定IP访问数据库

CREATE POLICY IP_Policy

ON Orders

FOR SELECT

USING (inet_client_addr() = '192.168.1.100');

 

（四）权限审计与优化

    1.    查看用户权限

    •    模板：

SELECT * 

FROM information_schema.role_table_grants 

WHERE grantee = '{用户名}';

    •    示例：

-- 查看UserA的权限

SELECT * 

FROM information_schema.role_table_grants 

WHERE grantee = 'UserA';

 

    2.    优化权限

    •    最小化权限分配，使用角色集中管理权限。

    •    模板：

GRANT {角色} TO {用户};

    •    示例：

-- 使用Reader角色管理只读权限

CREATE ROLE Reader;

GRANT SELECT ON Employees TO Reader;

GRANT Reader TO UserA, UserB;

 

三、存取控制的实践案例

案例 1：电商系统的权限管理

    1.    定义角色：

    •    Admin：全面权限。

    •    Customer：只读订单权限。

    •    Seller：管理商品和订单权限。

CREATE ROLE Admin;

CREATE ROLE Customer;

CREATE ROLE Seller;

 

    2.    分配权限：

-- Admin角色权限

GRANT ALL PRIVILEGES ON Orders, Products TO Admin;

-- Customer角色权限

GRANT SELECT ON Orders TO Cus

-- Seller角色权限

GRANT INSERT, UPDATE, DELETE ON Products TO Seller;

GRANT SELECT, UPDATE ON Orders TO Seller;

 

    3.    分配角色：

GRANT Admin TO User1; -- 将Admin权限分配给管理员

GRANT Customer TO User2; -- 将Customer权限分配给普通用户

GRANT Seller TO User3; -- 将Seller权限分配给商家