『数据库权限设计』在Sqlserver关系数据库实现行列级别的权限设计

-- 创建数据库和架构
CREATE DATABASE SalesDB;
GO

USE SalesDB;
GO

CREATE SCHEMA Security;
GO


-- 创建用户
CREATE USER Manager WITHOUT LOGIN;
CREATE USER Employee1 WITHOUT LOGIN;
CREATE USER Employee2 WITHOUT LOGIN;
CREATE USER Employee3 WITHOUT LOGIN;
GO


-- 创建目标表
CREATE TABLE dbo.Orders (
    OrderID INT PRIMARY KEY,
    SalesRep NVARCHAR(50),
    OrderAmount DECIMAL(18, 2)
);
GO

-- 插入示例数据
INSERT INTO dbo.Orders (OrderID, SalesRep, OrderAmount)
VALUES 
    (1, '1', 100.00),
    (2, '2', 200.00),
    (3, '3', 300.00),
    (4, '4', 400.00);
GO



-- 创建匹配表
CREATE TABLE Security.UserAccess (
    UserID NVARCHAR(50),
    CanViewUserID NVARCHAR(50)
);
GO

-- 插入示例数据
INSERT INTO Security.UserAccess (UserID, CanViewUserID)
VALUES 
    ('Manager', '1'),
    ('Manager', '2'),
    ('Manager', '3'),
    ('Employee1', '1'),
    ('Employee2', '2'),
    ('Employee3', '3');
GO


-- 创建行级别安全策略函数
CREATE FUNCTION Security.fn_securitypredicate(@SalesRep AS NVARCHAR(50))
RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS fn_securitypredicate_result
    FROM Security.UserAccess
    WHERE CanViewUserID = @SalesRep
          AND UserID = USER_NAME();
GO



-- 删除已存在的安全策略
DROP SECURITY POLICY IF EXISTS SalesFilter;
GO

-- 创建新的安全策略并应用于目标表
CREATE SECURITY POLICY SalesFilter
ADD FILTER PREDICATE Security.fn_securitypredicate(SalesRep)
ON dbo.Orders
WITH (STATE = ON);
GO


-- 授予用户对 Orders 表的 SELECT 权限
GRANT SELECT ON dbo.Orders TO Manager;
GRANT SELECT ON dbo.Orders TO Employee1;
GRANT SELECT ON dbo.Orders TO Employee2;
GRANT SELECT ON dbo.Orders TO Employee3;
GO


-- 测试 Manager 用户
EXECUTE AS USER = 'Manager';
SELECT * FROM dbo.Orders;
REVERT;
GO


-- 测试 Employee1 用户
EXECUTE AS USER = 'Employee1';
SELECT * FROM dbo.Orders;
REVERT;
GO

-- 测试 Employee1 用户
EXECUTE AS USER = 'Employee2';
SELECT * FROM dbo.Orders;
REVERT;
GO

-- 测试 Employee1 用户
EXECUTE AS USER = 'Employee3';
SELECT * FROM dbo.Orders;
REVERT;
GO

SELECT pr.name AS principal_name, pr.type_desc AS principal_type, 
       pe.permission_name, pe.state_desc AS permission_state
FROM sys.database_permissions AS pe
JOIN sys.database_principals AS pr
    ON pe.grantee_principal_id = pr.principal_id
WHERE pe.permission_name = 'IMPERSONATE';