主页 / dba / 问题 / 198001
Accepted
LiamB
Asked: 2018-02-16 04:12:55 +0800 CST

SQL Server 逐行访问

  • 772

我有一个结构如此的表（简化）

Name, EMail, LastLoggedInAt

我在 SQL Server (RemoteUser) 中有一个用户应该只能看到 LastLoggdInAt 字段不为空的数据（通过选择查询）。

看起来我可以做到这一点？可能吗？

sql-server row-level-security

1 个回答

  1. Best Answer

    SQL Server 安全模型允许您授予对视图的访问权限而不授予对基础表的访问权限。

    由于示例代码是展示概念的好方法，请考虑以下内容，以及LoginDetails表格和相应的视图：

    CREATE TABLE dbo.LoginDetails
(
    Username nvarchar(100) NOT NULL
    , EmailAddress nvarchar(256) NOT NULL
    , LastLoggedInAt datetime NULL
);
GO

CREATE VIEW dbo.LoginDetailsView
AS
SELECT ld.Username
    , ld.EmailAddress
    , ld.LastLoggedInAt
FROM dbo.LoginDetails ld
WHERE ld.LastLoggedInAt IS NOT NULL;
GO

    我们将创建一个登录名和一个用户，然后为该用户分配从视图中选择行的权限，但没有任何查看表本身的权限。

    CREATE LOGIN RemoteUser 
WITH PASSWORD = '2q1345lkjsadfgsa0(*';

CREATE USER RemoteUser
FOR LOGIN RemoteUser
WITH DEFAULT_SCHEMA = dbo;

GRANT SELECT ON dbo.LoginDetailsView TO RemoteUser;

    现在，我们将插入两个测试行：

    INSERT INTO dbo.LoginDetails(Username, EmailAddress, LastLoggedInAt)
VALUES ('user x', '[email protected]', NULL)
    , ('user y', '[email protected]', GETDATE());

    这将测试安全模型。第一条SELECT语句成功，因为它是从视图中进行选择，而第二SELECT条语句失败，因为用户没有直接访问表的权限。

    EXECUTE AS LOGIN = 'RemoteUser';

SELECT *
FROM dbo.LoginDetailsView;

    ╔══════════╦══════════════╦═══════════════════════ ══╗
║ 用户名 ║ 电子邮件地址 ║ LastLoggedInAt ║
╠══════════╬══════════════╬═══════════════════════ ══╣
║ 用户 y ║ [email protected] ║ 2018-02-15 07:36:54.490 ║
╚══════════╩══════════════╩═══════════════════════ ══╝

    SELECT *
FROM dbo.LoginDetails;

REVERT

    请注意，根据您的问题的要求，视图的结果排除了LastLoggedInAt值为的行。NULL

    针对基础表的第二条SELECT语句返回错误：

    消息 229，级别 14，状态 5，第 28 行
    对对象“LoginDetails”、数据库“tempdb”、架构“dbo”的 SELECT 权限被拒绝。

    清理：

    DROP USER RemoteUser;
DROP LOGIN RemoteUser;
DROP VIEW dbo.LoginDetailsView;
DROP TABLE dbo.LoginDetails;

    或者，如果您有 SQL Server 2016 或更新版本，则可以使用行级安全谓词来防止某些用户看到具有 NULLLastLoggedInAt值的行。行级别安全性的 Microsoft 文档在此处。

    首先，我们创建表、登录名、该登录名的用户，然后授予对表的访问权限：

    CREATE TABLE dbo.LoginDetails
(
    Username nvarchar(100) NOT NULL
    , EmailAddress nvarchar(256) NOT NULL
    , LastLoggedInAt datetime NULL
);
GO

CREATE LOGIN RemoteUser 
WITH PASSWORD = '2q1345lkjsadfgsa0(*';

CREATE USER RemoteUser
FOR LOGIN RemoteUser
WITH DEFAULT_SCHEMA = dbo;

GRANT SELECT ON dbo.LoginDetails TO RemoteUser;

    接下来，我们插入几个示例行。一行具有 null LastLoggedInAt，另一行具有该列的非空值。

    INSERT INTO dbo.LoginDetails(Username, EmailAddress, LastLoggedInAt)
VALUES ('user x', '[email protected]', NULL)
    , ('user y', '[email protected]', GETDATE());

    在这里，我们创建了一个模式绑定的表值函数，该函数根据传入函数的@LastLoggedInAt和变量的值返回一行 0 或 1。@username过滤器谓词将使用此函数来消除我们想要对某些用户隐藏的行。

    CREATE FUNCTION dbo.fn_LoginDetailsRemoteUserPredicate
(
    @LastLoggedInAt datetime
    , @username sysname
)  
RETURNS TABLE  
WITH SCHEMABINDING  
AS  
    RETURN SELECT 1 AS fn_securitypredicate_result   
    WHERE (@username = N'RemoteUser' AND @LastLoggedInAt IS NOT NULL)
        OR @username <> N'RemoteUser';  
GO

    SELECT这是从针对表运行的语句中删除行的安全过滤器dbo.LoginDetails：

    CREATE SECURITY POLICY LoginDetailsRemoteUserPolicy
ADD FILTER PREDICATE dbo.fn_LoginDetailsRemoteUserPredicate(LastLoggedInAt, USER_NAME())
ON dbo.LoginDetails
WITH (STATE=ON);

    上面的过滤器通过传入当前用户的名称以及表中列的dbo.fn_LoginDetailsRemoteUserPredicate每一行的值来使用该函数。LastLoggedInAtdbo.LoginDetails

    如果我们以普通用户身份查询表：

    SELECT *
FROM dbo.LoginDetails

    我们看到所有行：

    ╔══════════╦══════════════╦═══════════════════════ ══╗
║ 用户名 ║ 电子邮件地址 ║ LastLoggedInAt ║
╠══════════╬══════════════╬═══════════════════════ ══╣
║ 用户 x ║ [email protected] ║ NULL ║
║ 用户 y ║ [email protected] ║ 2018-02-15 13:53:42.577 ║
╚══════════╩══════════════╩═══════════════════════ ══╝

    但是，如果我们测试为RemoteUser：

    EXECUTE AS LOGIN = 'RemoteUser';

SELECT *
FROM dbo.LoginDetails

REVERT

    我们只看到“有效”行：

    ╔══════════╦══════════════╦═══════════════════════ ══╗
║ 用户名 ║ 电子邮件地址 ║ LastLoggedInAt ║
╠══════════╬══════════════╬═══════════════════════ ══╣
║ 用户 y ║ [email protected] ║ 2018-02-15 13:42:02.023 ║
╚══════════╩══════════════╩═══════════════════════ ══╝

    而且，我们清理：

    DROP SECURITY POLICY LoginDetailsRemoteUserPolicy;
DROP FUNCTION dbo.fn_LoginDetailsRemoteUserPredicate;
DROP USER RemoteUser;
DROP LOGIN RemoteUser;
DROP TABLE dbo.LoginDetails;

    请注意，以这种方式将函数模式绑定到表确实使得在不先删除过滤谓词和dbo.fn_LoginDetailsRemoteUserPredicate函数的情况下无法修改表的定义。

    • 33

相关问题