Acumatica：基于行级安全性的SQL视图 [英] Acumatica: Sql view based on Row Level Security

问题描述

我已经配置了行级安全性，然后我要基于此创建sql视图以支持我的自定义报告。

I have configured row level security, then I want to create sql view based on this to support for my custom report.

已更新，以下是我的视图

Updated, below is my view

SELECT
        s.CompanyID
        , DistributorID = s.BranchID
        , s.SiteID
        , s.SiteCD
        , s.Descr
        , s.Active
        , IsDefault = s.UsrIsDefault
        , u.Username
    FROM
        dbo.INSite s
        FULL JOIN dbo.Users u ON u.CompanyID = s.CompanyID
    WHERE
        SUBSTRING(s.GroupMask, 1, 4) = ''
        OR (0 = SUBSTRING(s.GroupMask, 1, 4) & (SELECT SUM(CONVERT(BIGINT, rg.GroupMask & -1)) 
                                            FROM dbo.RelationGroup rg 
                                            WHERE rg.CompanyID = s.CompanyID AND rg.Active = 1 AND rg.SpecificType = 'PX.Objects.IN.INSite')
        OR 0 <> SUBSTRING(s.GroupMask, 1, 4) & CONVERT(BIGINT, u.GroupMask))

但是，这样做不正确

推荐答案

似乎您对限制组的计算不正确：

It seems that you calculations on restriction groups are not correct:

让我与您分享这行得通。
为每个组系统分配一个字节：

Let me share with you how does it work. For each group system assign one byte:

• 组1-0x8000 = 1000 0000 0000 0000


• 组2-0x4000 = 0100 0000 0000 0000


• 组3-0x2000 = 0010 0000 0000 0000


• 组4-0x1000 = 0001 0000 0000 0000


• 组5-0x0800 = 0000 1000 0000 0000


• 组6-0x0400 = 0000 0100 0000 0000


• .....

• Group 1 - 0x8000 = 1000 0000 0000 0000
• Group 2 - 0x4000 = 0100 0000 0000 0000
• Group 3 - 0x2000 = 0010 0000 0000 0000
• Group 4 - 0x1000 = 0001 0000 0000 0000
• Group 5 - 0x0800 = 0000 1000 0000 0000
• Group 6 - 0x0400 = 0000 0100 0000 0000
• .....

将项目分配给任何组时，实际上会在组掩码中设置标志：
假设卖方属于组3和组6，则掩码将为：

When you assign an item to any group it actually sets the flags in the group masks: Lets assume vendor belongs to group 3 and group 6, than mask will be:

0010 0100 0000 0000 = 0x2400

0010 0100 0000 0000 = 0x2400

您可以在每个受支持的表的组掩码中看到确切的内容。

You can see exactly this in group masks on each supported table.

用户也属于同一规则的组。比系统搜索拦截，用户和实体属于哪些组，并计算评估权限。
同样不要忘记，有不同类型的组可以用不同的方式计算（组A，B，A逆，B逆）

Users are also belongs to groups by the same rule. Than system search for interceptions, to what groups belongs user and entity and calculate assess rights. Also do not forget that there are different types of groups that can be calculated differently (Group A, B, A Inverse, B Inverse)

您将所有不正确的实体加入用户。
我认为您需要编写某种过程/函数，该过程/函数将为实体和用户获取可用的组，并根据该条件进行加入。
可能只是您需要查找哪些用户和实体在同一位置上至少具有相同的一位-这意味着实体和用户属于同一组-但这仅适用于A组。

In your case you join all entities to users that is not correct. I think you need to write some sort of the procedure/function that will get available groups for entity and user and join by that condition. May be you just need to find what user and entity have at least one same bit in the same position - this means that entity and user belongs to the same group - but this will work just for Group A.

此外，复杂性在于SQL Server不支持2个二进制文件之间的按位操作（ https://msdn.microsoft.com/zh-cn/library/ms176122.aspx ），因此您必须将其转换为int。为避免溢出，您只需要获取字符串的一部分并进行逐部分比较。
我更愿意尝试从实体和用户那里获取网上论坛，并进行逐位操作以确保没有溢出。

Also the complexity is that SQL Server does not support bitwise operations between 2 binaries (https://msdn.microsoft.com/en-us/library/ms176122.aspx), so you have to convert it to int. To avoid the overflow, you have to get just a part of string and compare part by part. I prefer to try to get Groups from entity and user and do a bitwise operation bit by bit to ensure that there is no overflow.

希望这会有所帮助

这篇关于Acumatica：基于行级安全性的SQL视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！