1、查詢用戶所擁有的安全形色： select su.FullName,r.Name,bu.name bussinessname from SystemUserRoles sur left join SystemUserBase su on su.SystemUserId = sur.SystemUs ...
1、查詢用戶所擁有的安全形色：
select su.FullName,r.Name,bu.name bussinessname from SystemUserRoles sur
left join SystemUserBase su on su.SystemUserId = sur.SystemUserId
left join BusinessUnitBase bu on bu.BusinessUnitId=su.BusinessUnitId
left join Role r on r.RoleId=sur.RoleId
where su.IsDisabled=0
2、安全形色的具體許可權明細：
SELECT DISTINCT
r.Name 安全形色名稱
,COALESCE(e.OriginalLocalizedName, e.Name) AS [EntityName]
,CASE p.AccessRight
WHEN 32 THEN 'Create-創建'
WHEN 1 THEN 'Read-讀'
WHEN 2 THEN 'Write-寫'
WHEN 65536 THEN 'Delete-刪除'
WHEN 4 THEN 'Append-追加'
WHEN 16 THEN 'AppendTo-追加到'
WHEN 524288 THEN 'Assign-分派'
WHEN 262144 THEN 'Share-共用'
ELSE 'None'
END AS [Privilege]
,CASE (rp.PrivilegeDepthMask % 0x0F)
WHEN 1 THEN 'User (Basic)-個人'
WHEN 2 THEN 'Business Unit (Local)-業務部門'
WHEN 4 THEN 'Parental (Deep)-上下級'
WHEN 8 THEN 'Organization (Global)-組織'
ELSE 'Unknown'
END AS [PrivilegeLevel]
,(rp.PrivilegeDepthMask % 0x0F) as [PrivilegeDepthMask]
,CASE WHEN e.IsCustomEntity = 1 THEN 'Yes' ELSE 'No' END AS [IsCustomEntity]
FROM Role AS r
INNER JOIN RolePrivileges AS rp
ON r.RoleId = rp.RoleId
INNER JOIN Privilege AS p
ON rp.PrivilegeId = p.PrivilegeId
INNER JOIN PrivilegeObjectTypeCodes AS potc
ON potc.PrivilegeId = p.PrivilegeId
INNER JOIN MetadataSchema.Entity AS e
ON e.ObjectTypeCode = potc.ObjectTypeCode
ORDER BY r.Name, [EntityName]
3、用戶有用的實際許可權總和（比如A用戶有三個角色，那麼這裡就是取這三個角色的並集，並且取最高許可權）
select su.FullName 用戶名,t.EntityName 實體,t.Privilege 許可權名稱, CASE (max(t.PrivilegeLevel))
WHEN 1 THEN 'User (Basic)-個人'
WHEN 2 THEN 'Business Unit (Local)-業務部門'
WHEN 4 THEN 'Parental (Deep)-上下級'
WHEN 8 THEN 'Organization (Global)-組織'
ELSE 'Unknown'
END 許可權大小 from SystemUserRoles sur
left join SystemUserBase su on su.SystemUserId = sur.SystemUserId
left join BusinessUnitBase bu on bu.BusinessUnitId=su.BusinessUnitId
left join Role r on r.RoleId=sur.RoleId
left join (SELECT DISTINCT
r.Name 安全形色名稱
,COALESCE(e.OriginalLocalizedName, e.Name) AS [EntityName]
,CASE p.AccessRight
WHEN 32 THEN 'Create-創建'
WHEN 1 THEN 'Read-讀'
WHEN 2 THEN 'Write-寫'
WHEN 65536 THEN 'Delete-刪除'
WHEN 4 THEN 'Append-追加'
WHEN 16 THEN 'AppendTo-追加到'
WHEN 524288 THEN 'Assign-分派'
WHEN 262144 THEN 'Share-共用'
ELSE 'None'
END AS [Privilege]
,rp.PrivilegeDepthMask % 0x0F
AS [PrivilegeLevel]
,(rp.PrivilegeDepthMask % 0x0F) as [PrivilegeDepthMask]
,CASE WHEN e.IsCustomEntity = 1 THEN 'Yes' ELSE 'No' END AS [IsCustomEntity]
FROM Role AS r
INNER JOIN RolePrivileges AS rp
ON r.RoleId = rp.RoleId
INNER JOIN Privilege AS p
ON rp.PrivilegeId = p.PrivilegeId
INNER JOIN PrivilegeObjectTypeCodes AS potc
ON potc.PrivilegeId = p.PrivilegeId
INNER JOIN MetadataSchema.Entity AS e
ON e.ObjectTypeCode = potc.ObjectTypeCode) as t on r.Name=t.安全形色名稱
where su.IsDisabled=0
group by su.FullName,t.EntityName,t.Privilege
--行轉列
select *
from (select su.FullName 用戶名,t.EntityName 實體,t.Privilege 許可權名稱, CASE (max(t.PrivilegeLevel))
WHEN 1 THEN 'User (Basic)-個人'
WHEN 2 THEN 'Business Unit (Local)-業務部門'
WHEN 4 THEN 'Parental (Deep)-上下級'
WHEN 8 THEN 'Organization (Global)-組織'
ELSE 'Unknown'
END 許可權大小 from SystemUserRoles sur
left join SystemUserBase su on su.SystemUserId = sur.SystemUserId
left join BusinessUnitBase bu on bu.BusinessUnitId=su.BusinessUnitId
left join Role r on r.RoleId=sur.RoleId
left join (SELECT DISTINCT
r.Name 安全形色名稱
,COALESCE(e.OriginalLocalizedName, e.Name) AS [EntityName]
,CASE p.AccessRight
WHEN 32 THEN 'Create-創建'
WHEN 1 THEN 'Read-讀'
WHEN 2 THEN 'Write-寫'
WHEN 65536 THEN 'Delete-刪除'
WHEN 4 THEN 'Append-追加'
WHEN 16 THEN 'AppendTo-追加到'
WHEN 524288 THEN 'Assign-分派'
WHEN 262144 THEN 'Share-共用'
ELSE 'None'
END AS [Privilege]
,rp.PrivilegeDepthMask % 0x0F
AS [PrivilegeLevel]
,(rp.PrivilegeDepthMask % 0x0F) as [PrivilegeDepthMask]
,CASE WHEN e.IsCustomEntity = 1 THEN 'Yes' ELSE 'No' END AS [IsCustomEntity]
FROM Role AS r
INNER JOIN RolePrivileges AS rp
ON r.RoleId = rp.RoleId
INNER JOIN Privilege AS p
ON rp.PrivilegeId = p.PrivilegeId
INNER JOIN PrivilegeObjectTypeCodes AS potc
ON potc.PrivilegeId = p.PrivilegeId
INNER JOIN MetadataSchema.Entity AS e
ON e.ObjectTypeCode = potc.ObjectTypeCode) as t on r.Name=t.安全形色名稱
where su.IsDisabled=0
group by su.FullName,t.EntityName,t.Privilege) as tt pivot(max(許可權大小) for 許可權名稱 in([Create-創建],[Read-讀]
,[Write-寫],[Delete-刪除],[Append-追加],[AppendTo-追加到],[Assign-分派],[Share-共用]
))t
4、用戶擁有的具體許可權明細表（未合併，A用戶有三個角色，三個角色的許可權都會展示出來）
select su.FullName,r.Name,bu.name bussinessname,t.EntityName,t.Privilege,t.PrivilegeLevel from SystemUserRoles sur
left join SystemUserBase su on su.SystemUserId = sur.SystemUserId
left join BusinessUnitBase bu on bu.BusinessUnitId=su.BusinessUnitId
left join Role r on r.RoleId=sur.RoleId
left join (SELECT DISTINCT
r.Name 安全形色名稱
,COALESCE(e.OriginalLocalizedName, e.Name) AS [EntityName]
,CASE p.AccessRight
WHEN 32 THEN 'Create-創建'
WHEN 1 THEN 'Read-讀'
WHEN 2 THEN 'Write-寫'
WHEN 65536 THEN 'Delete-刪除'
WHEN 4 THEN 'Append-追加'
WHEN 16 THEN 'AppendTo-追加到'
WHEN 524288 THEN 'Assign-分派'
WHEN 262144 THEN 'Share-共用'
ELSE 'None'
END AS [Privilege]
,CASE (rp.PrivilegeDepthMask % 0x0F)
WHEN 1 THEN 'User (Basic)-個人'
WHEN 2 THEN 'Business Unit (Local)-業務部門'
WHEN 4 THEN 'Parental (Deep)-上下級'
WHEN 8 THEN 'Organization (Global)-組織'
ELSE 'Unknown'
END AS [PrivilegeLevel]
,(rp.PrivilegeDepthMask % 0x0F) as [PrivilegeDepthMask]
,CASE WHEN e.IsCustomEntity = 1 THEN 'Yes' ELSE 'No' END AS [IsCustomEntity]
FROM Role AS r
INNER JOIN RolePrivileges AS rp
ON r.RoleId = rp.RoleId
INNER JOIN Privilege AS p
ON rp.PrivilegeId = p.PrivilegeId
INNER JOIN PrivilegeObjectTypeCodes AS potc
ON potc.PrivilegeId = p.PrivilegeId
INNER JOIN MetadataSchema.Entity AS e
ON e.ObjectTypeCode = potc.ObjectTypeCode) as t on r.Name=t.安全形色名稱
where su.IsDisabled=0
