在SQL Server xml列中存储ASP.NET样式的安全性

本文关键字：NET ASP 样式 安全性 存储 SQL Server xml | 更新日期: 2023-09-27 18:26:20

我想将记录的安全信息存储在SQL Server数据库中。出于一致性的目的，安全信息最好与配置文件中的格式相同：

<authorization>
     <allow roles="Admins"/>
     <allow users="SomeGuy,SomeOtherGuy"/>
     <deny users="*"/>
</authorization>

然后，我希望能够在数据库中查询特定用户被允许访问的所有内容，给定他们的用户名和角色列表。

有人对如何最好地做到这一点有什么建议吗？还是我走错了路

一个简单的暴力解决方案是只读取数据库中的每一行，并将每个安全规则XML拉到某个类中，该类将为我进行评估——但显然这会很慢，而且在大表上是不合理的。

想到的另一件事是制作某种类型的子表，该子表包括某种优先级，以指示每个允许或拒绝节点的应用顺序。然而，我有相当多的表需要这个功能，如果我能避免创建大量的子表，那将是理想的。

尽管我在SQL Server中使用XML列的经验有限，但我可能可以构建一个XML查询来确定是否允许用户使用——也许是从(/authorization/allow/@users)[1]开始。然而，节点的顺序很重要，所以虽然我可能会找到一个与给定名称或角色匹配的节点，但我不知道如何执行任何基于集合的操作来检查用户是被拒绝还是被允许，基于哪一个先出现。

那么，给定用户名和逗号分隔的角色列表，检查该用户对数据库中特定行的访问权限的最佳方法是什么

在SQL Server xml列中存储ASP.NET样式的安全性

好吧，我已经想出了一个解决方案，但并不理想。对于10000条记录，返回与安全配置文件匹配的所有行需要5秒钟。这不是一场彻底的灾难，它确实有效，但我以后必须回到这个问题上来改进它

以下是我解决问题的方法。请记住，我只做了几个小时。

在我真正做任何事情之前，我知道我需要一个函数来比较两个逗号分隔的列表。我需要在一个列表中列出一个用户的角色，并查看这些角色中是否有任何一个出现在存储在xml列中的授权设置中，如原始文章中所述。为此，我做了两个函数。

第一个函数是一个常见的使用xml:进行字符串拆分的函数

IF EXISTS (
    SELECT * FROM sysobjects WHERE id = object_id(N'ufnSplitStrings') 
    AND xtype IN (N'FN', N'IF', N'TF')
)
    DROP FUNCTION ufnSplitStrings
GO
CREATE FUNCTION dbo.ufnSplitStrings
(
   @List       NVARCHAR(MAX),
   @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
   RETURN 
   (  
      SELECT Item = y.i.value('(./text())[1]', 'nvarchar(4000)')
      FROM 
      ( 
        SELECT x = CONVERT(XML, '<i>' 
          + REPLACE(@List, @Delimiter, '</i><i>') 
          + '</i>').query('.')
      ) AS a CROSS APPLY x.nodes('i') AS y(i)
   );

建立了这个函数后，我可以创建另一个函数，然后进行我想要的比较：

IF EXISTS (
    SELECT * FROM sysobjects WHERE id = object_id(N'ufnContainsAny') 
    AND xtype IN (N'FN', N'IF', N'TF')
)
    DROP FUNCTION ufnContainsAny
GO
CREATE FUNCTION dbo.ufnContainsAny(@List1 NVARCHAR(MAX), @List2 NVARCHAR(MAX))
RETURNS int 
AS 
BEGIN
    DECLARE @Ret AS INT = 0
    SELECT @Ret = COUNT(*) FROM dbo.ufnSplitStrings(@List1, ',') x
    JOIN dbo.ufnSplitStrings(@List2, ',') y ON x.Item = y.Item
    RETURN @Ret
END;
GO

最后，我可以使用该函数来组装我的主UserIsAuthorized函数。

IF EXISTS (
    SELECT * FROM sysobjects WHERE id = object_id(N'ufnUserIsAuthorized') 
    AND xtype IN (N'FN', N'IF', N'TF')
)
    DROP FUNCTION ufnUserIsAuthorized
GO
CREATE FUNCTION dbo.ufnUserIsAuthorized(@SecurityRules XML, @UserName NVARCHAR(64), @UserRoles NVARCHAR(MAX))
RETURNS int 
AS 
BEGIN
    DECLARE @ret int = 0;
    DECLARE @AuthType NVARCHAR(32);

    DECLARE @authRules Table (a nvarchar(32), u nvarchar(max), r nvarchar(max), o int)
    INSERT INTO @authRules
    SELECT
        a = value.value('local-name(.[1])', 'varchar(32)'),
        u = ',' + value.value('@users', 'varchar(max)') + ',',
        r = ',' + value.value('@roles', 'varchar(max)') + ',',
        o = value.value('for $i in . return count(../*[. << $i]) + 1', 'int')
    FROM @SecurityRules.nodes('//allow,//deny') AS T(value)
    SELECT TOP 1 @AuthType = a FROM @authRules
    WHERE CHARINDEX(',' + @UserName + ',', u) > 0 OR CHARINDEX(',*,', u) > 0 OR dbo.ufnContainsAny(r, @UserRoles) > 0 OR CHARINDEX(',*,', r) > 0
    GROUP BY a
    ORDER BY MIN(o)
    IF (@AuthType IS NOT NULL AND @AuthType = 'allow')
        SET @ret = 1;
    RETURN @ret;
END;

该函数将xml允许和拒绝节点拆分为一个表，该表包含授权类型（允许或拒绝）、用户列表、角色列表，最后是特定节点在文档中的显示顺序。最后，我可以获取查找用户或用户角色之一的第一个节点。如果该节点为"允许"，那么我返回一个1。

是的，这有点可怕，因为我们在每次通话中都要声明一个表。我尝试了各种小测试，只查找用户名（以避免对ufContainerAny进行任何调用），但性能没有改变。我还尝试将"o"列更改为一个简单的标识列，因为我选择了所有节点——这将允许它跳过我认为可能耗时的获取节点顺序的计算。但这也没有影响演出。

因此，毫不奇怪，这种方法需要工作。如果有人有什么建议的话，我洗耳恭听。

我最初使用这个功能的行数很少，所以我可以在此期间使用它，直到我找到更好的解决方案（或者完全放弃这个方法）。

编辑：

只需跳过DECLARE表/INSERT就可以显著提高性能。相反，我们可以这样做：

SELECT TOP 1 @AuthType = a FROM 
(
SELECT
    a = value.value('local-name(.[1])', 'varchar(32)'),
    u = ',' + value.value('@users', 'varchar(max)') + ',',
    r = ',' + value.value('@roles', 'varchar(max)') + ',',
    o = value.value('for $i in . return count(../*[. << $i]) + 1', 'int')
FROM @SecurityRules.nodes('//allow,//deny') AS T(value)
) AS sec
WHERE CHARINDEX(',' + @UserName + ',', u) > 0 OR CHARINDEX(',*,', u) > 0 OR dbo.ufnContainsAny(r, @UserRoles) > 0 OR CHARINDEX(',*,', r) > 0
GROUP BY a
ORDER BY MIN(o)
相关文章：