权限管理中的数据库存储过程

1、根据用户读取相关权限

CREATE PROCEDURE [dbo].[GetRoleAction]
@UserGUID uniqueidentifier,
@DepartmentGUID uniqueidentifier
AS
BEGIN
    IF @UserGUID=‘f425ba38-df44-45d0-a9bb-f5c904bfee1c‘
        BEGIN
            SELECT a.GUID,a.ControllerName,a.ActionName FROM Action a
        END
    ELSE
    BEGIN
        SELECT DISTINCT a.GUID,a.ControllerName,a.ActionName FROM Action a
        INNER JOIN Role_Action ra ON ra.ActionGUID=a.GUID
        INNER JOIN Role r ON r.GUID=ra.RoleGUID
        LEFT JOIN User_Role ur ON ur.RoleGUID=r.GUID
        LEFT JOIN Department_Role dr ON dr.RoleGUID=r.GUID
        WHERE ur.UserGUID=@UserGUID OR dr.DepartmentGUID=@DepartmentGUID
    END
END

2、根据用户获取菜单权限列表

CREATE PROCEDURE [dbo].[GetRoleMenu]
@UserGUID uniqueidentifier,
@DepartmentGUID uniqueidentifier
AS
BEGIN
    IF @UserGUID=‘f425ba38-df44-45d0-a9bb-f5c904bfee1c‘
        BEGIN
            SELECT m.GUID,m.ParentGUID,m.DisplayName,m.IconClass,m.RequestUrl,m.RootLevel,m.OrderIndex FROM Menu m
        END
    ELSE
    BEGIN
        SELECT DISTINCT m.GUID,m.ParentGUID,m.DisplayName,m.IconClass,m.RequestUrl,m.RootLevel,m.OrderIndex FROM Menu m
        INNER JOIN Role_Menu rm ON rm.MenuGUID=m.GUID
        INNER JOIN Role r ON r.GUID=rm.RoleGUID
        LEFT JOIN User_Role ur ON ur.RoleGUID=r.GUID
        LEFT JOIN Department_Role dr ON dr.RoleGUID=r.GUID
        WHERE ur.UserGUID=@UserGUID OR dr.DepartmentGUID=@DepartmentGUID
    END
END

权限管理中的数据库存储过程

标签：