用于搜索授予用户的访问权限的存储过程

Question

我想显示授予用户特定模块的访问权限。

• 我有七个表，例如 RoleAccess、Roles、Functions、Module、SubModule、Company 和 Unit。 RoleAccess 是主表。
• 给定的 AccessRights 将仅存储在 RoleAccess 表中。
• RoleAccess 表具有以下列，例如 RoleID、CompanyID、UnitID、FunctionID、ModuleID、SubModuleID、Create、Update、Delete、Read、Approve。这里Create_f、Update_f、Delete_f、Read_f 和Approve_f 是标志。
• 公司表有两列，例如 CompanyID 和 CompanyName。
• 单位表具有三列，例如单位ID、单位名称和公司ID。
• 角色表有四列，例如 RoleID、RoleName、CompanyID 和 UnitID。
• 模块表有两列，例如 ModuleID 和 ModuleName。
• 子模块表具有三列，例如 ModuleID、SubModuleID、SubModuleName。
• 函数表有五列，例如FunctionID、FunctionName、ModuleID 和SubModuleID。

我想在 RoleAccess 表中显示以下记录。

 ModuleName, SubModuleName, FunctionName,
CompanyID, RoleID, UnitID, FunctionID, ModuleID, SubModuleID,
Create_f, Update_f, Delete_f, Read_f and Approve_f.  

如果 RoleAccess 表中不存在 RoleID，则它将照常显示所有记录，但标志列为零。

我编写了一个存储过程，但它显示基于 RoleAccess 表中存储的 RoleID 的记录。但我还想将未存储在 RoleAccess 表中的角色的标志显示为 0。

我想要这个的存储过程。任何人请帮助我。

我的存储过程如下。其中一些模块有一些两个或更多子模块。我无法在搜索结果中显示所有子模块。如果只有一个子模块，那么它将给出正确的结果。请帮我。

CREATE PROCEDURE [dbo].[SEC_GetAccessRight]
@CompanyID INT,
@UnitID Int,
@RoleID INT,
@ModuleID INT
AS
BEGIN

SELECT DISTINCT Module.ModuleName,
                SubModule.SubModuleName, 
                Functions.FunctionName,
                Functions.FunctionDescription,
                RoleAccess.CompanyID,
                RoleAccess.UnitID, 
                RoleAccess.RoleID, 
                Functions.FunctionID, 
                Module.ModuleID, 
                SubModule.SubModuleID,
                ISNULL(RoleAccess.Create_f, 0)      AS [CREATE],
                ISNULL(RoleAccess.Update_f, 0)      AS [UPDATE],
                ISNULL(RoleAccess.Delete_f, 0)      AS [DELETE],
                ISNULL(RoleAccess.Read_f, 0)        AS [READ],
                ISNULL(RoleAccess.Approval_f, 0)    AS [APPROVE]    
FROM Module INNER JOIN SubModule 
ON Module.Moduleid = SubModule.Moduleid INNER JOIN Functions
ON Module.Moduleid = Functions.Moduleid AND SubModule.SubModuleid = Functions.SubModuleid 
LEFT OUTER JOIN
roleaccess ON roleaccess.Moduleid = Module.Moduleid AND roleaccess.SubModuleid = SubModule.SubModuleid
AND Functions.Functionid = roleaccess.Functionid 
WHERE Module.ModuleID = (SELECT DISTINCT Module.ModuleID FROM Module INNER JOIN SubModule
on SubModule.ModuleID = Module.ModuleID LEFT OUTER JOIN
RoleAccess ON RoleAccess.ModuleID = Module.ModuleID AND RoleAccess.SubModuleID = SubModule.SubModuleID
WHERE Module.ModuleID = @ModuleID)
 AND RoleAccess.RoleID = @RoleID OR RoleAccess.RoleID IS NULL
AND RoleAccess.ModuleID IS NOT NULL AND RoleAccess.SubModuleID IS NULL
END

Answer 1

您的代码包含一个错误...

您对 RoleAccess 进行了左连接，这是正确的。

LEFT OUTER JOIN roleaccess ON roleaccess.Moduleid = Module.Moduleid 
         AND roleaccess.SubModuleid = SubModule.SubModuleid
AND Functions.Functionid = roleaccess.Functionid 

就其本身而言，这将导致从 RoleAccess 返回行，如果未找到匹配项，您将从表中获得一个空行集（这就是 ISNULL 函数正在查找的内容）

但是，稍后您将在WHERE 子句。这基本上击败了左连接...

WHERE Module.ModuleID = @ModuleID)
 AND RoleAccess.RoleID = @RoleID OR RoleAccess.RoleID IS NULL
AND RoleAccess.ModuleID IS NOT NULL AND RoleAccess.SubModuleID IS NULL

尝试将 RoleID 条件移动到连接中，而不是 WHERE 中，看看是否可以解决您的问题...

左连接应该如下所示：

LEFT OUTER JOIN roleaccess ON roleaccess.Moduleid = Module.Moduleid 
             AND roleaccess.SubModuleid = SubModule.SubModuleid
             AND Functions.Functionid = roleaccess.Functionid 
             AND RoleAccess.RoleID = @RoleID