SQL Server 动态授予权限的实现

1. 概述

在 SQL Server 中，授权是一个非常重要的权限，它允许用户和角色对数据库中的对象执行特定的操作。通常情况下，授权是静态的，即在数据库中为用户和角色分配固定的权限。但是，有时候需要动态地授予权限，以允许用户和角色在不同的情况下执行不同的操作。为了实现动态授权，SQL Server 提供了一些强大的功能，如 GRANT、REVOKE、EXECUTE AS 等。

2. GRANT 和 REVOKE

2.1 GRANT

GRANT 语句可以将权限授予数据库中的用户和角色。语法如下：

GRANT permission TO user_or_role;

其中，permission 是要授予的权限，user_or_role 是要授予权限的用户或角色。

例如，将 SELECT 和 INSERT 权限授予用户 john：

GRANT SELECT, INSERT ON table TO john;

可以使用 WITH GRANT OPTION 子句授予用户将权限授予其他用户或角色的权限。例如，将查询权限授予用户 jane，并允许她将该权限授予其他用户或角色：

GRANT SELECT ON table TO jane WITH GRANT OPTION;

2.2 REVOKE

REVOKE 语句用于撤销用户或角色的权限。语法如下：

REVOKE permission FROM user_or_role;

例如，撤销用户 john 的 SELECT 和 INSERT 权限：

REVOKE SELECT, INSERT ON table FROM john;

与 GRANT 语句一样，可以使用 CASCADE 子句撤销用户或角色的所有权限及其所拥有的对象的所有依赖项。例如，撤销角色 r1 的 SELECT 权限，并强制要求其所有成员也失去该权限：

REVOKE SELECT ON table FROM r1 CASCADE;

3. EXECUTE AS

EXECUTE AS 语句用于在特定的执行上下文中运行一组语句。可以使用 EXECUTE AS 对象语句设置一个特定的安全上下文，并在该上下文中执行后续语句。语法如下：

EXECUTE AS login_name;

其中，login_name 是要设置的登录名。可以使用 REVERT 语句恢复到当前用户或可执行上下文之前的安全上下文。

例如，使用 EXECUTE AS 设置登录名为 sa，并在该上下文中执行查询：

EXECUTE AS LOGIN = 'sa';
SELECT * FROM table;
REVERT;

4. 动态授权实现的例子

下面，我们将演示如何使用 GRANT、REVOKE 和 EXECUTE AS 实现动态授权。假设我们有一个存储日期的表，需要根据用户角色来限制访问权限。

首先，我们创建一个存储日期的表：

CREATE TABLE dateTable(
    id int NOT NULL PRIMARY KEY,
    date datetime NOT NULL
);

然后，我们创建两个角色，分别为管理员和普通用户：

CREATE ROLE admin;
CREATE ROLE user;

将插入和更新权限授予管理员角色：

GRANT INSERT, UPDATE ON dateTable TO admin;

将查询权限授予普通用户角色：

GRANT SELECT ON dateTable TO user;

现在，我们将创建一个存储过程，该存储过程将根据用户角色来动态授权。如果用户是管理员，则在存储过程中为其设置 EXECUTE AS admin，然后执行插入或更新操作；否则，对于普通用户，将在存储过程中设置 EXECUTE AS user，并执行查询操作。

CREATE PROCEDURE dynamicPermission(@id int, @date datetime)
AS
BEGIN
    IF (IS_MEMBER('admin') = 1)
    BEGIN
        EXECUTE AS LOGIN = 'admin';
        INSERT INTO dateTable(id, date)
        VALUES(@id, @date);
        REVERT;
    END
    ELSE
    BEGIN
        EXECUTE AS LOGIN = 'user';
        SELECT * FROM dateTable;
        REVERT;
    END
END;

现在，我们测试存储过程。首先，我们将当前用户添加到普通用户角色中：

EXEC sp_addrolemember 'user', 'CURRENT_USER';

然后，我们使用普通用户身份执行该存储过程：

EXEC dynamicPermission @id=1, @date='2022-01-01';

结果只包含查询到的日期数据。现在，我们将当前用户添加到管理员角色中：

EXEC sp_addrolemember 'admin', 'CURRENT_USER';

然后，我们再次执行该存储过程，结果包含新插入的数据。

EXEC dynamicPermission @id=2, @date='2022-01-02';

5. 结论

通过本文，我们了解了 SQL Server 中动态授权的实现方法。使用 GRANT、REVOKE 和 EXECUTE AS 语句，可以实现对不同用户和角色的动态授权，并根据执行上下文在运行时决定用户是否有权访问数据。这对于安全性要求较高的应用程序来说非常有用。