表结构如下,求 SQL 语句,查询所有角色拥有的权限!
如:
User Shop Select
Admin Shop Select
Admin Shop UpdateRole 角色表
RoleID RoleName
1 User
2 AdminPrivilegeInRole 角色与权限对应表
RoleID PrivilegeID
1 1
2 2
2 2Privilege 权限表
PrivilegeID ResourceID OperationID
1 1 1
2 1 1
2 1 2 Resource 资源表
ResourceID Description
1 ShopOperation 操作表
OperationID Description
1 Select
2 Update
如:
User Shop Select
Admin Shop Select
Admin Shop UpdateRole 角色表
RoleID RoleName
1 User
2 AdminPrivilegeInRole 角色与权限对应表
RoleID PrivilegeID
1 1
2 2
2 2Privilege 权限表
PrivilegeID ResourceID OperationID
1 1 1
2 1 1
2 1 2 Resource 资源表
ResourceID Description
1 ShopOperation 操作表
OperationID Description
1 Select
2 Update
from (
select e.RoleID ,d.*
from Role e
left join (
select PrivilegeID c ,a.Description as Description1 ,b.Description as Description2
from Privilege
left join Resource a on c.ResourceID = a.ResourceID
left join Operation b on c.OperationID = b.OperationID )d on e.PrivilegeID= d.PrivilegeID) f
left join Role g on g.RoleID = f.RoleID
from PrivilegeInRole P,
Role R,
(
select P.PrivilegeID,R.Description as R_Description,O.Description as O_Description
from Privilege P,
Resource R,
Operation O
Where P.ResourceID = R.ResourceID
and P.OperationID=O.OperationID
)T
where P.RoleID=R.RoleID
and T.PrivilegeID=R.PrivilegeID
create table Role (RoleID smallint, RoleName varchar(10))
insert into Role
select 1, 'User' union
select 2, 'Admin' 角色与权限对应表
create table PrivilegeInRole
(RoleID smallint, PrivilegeID smallint)
insert into PrivilegeInRole
select 1, 1 union all
select 2, 2 union all
select 2, 2
权限表
create table Privilege (PrivilegeID smallint, ResourceID smallint, OperationID smallint)
insert into Privilege
select 1, 1, 1 union all
select 2, 1, 1 union all
select 2, 1, 2
资源表
create table Resource(ResourceID smallint, Description varchar(10))
insert into Resource
select 1, 'Shop' 操作表create table Operation
(OperationID smallint,Description varchar(10))
insert into Operation
select 1, 'Select' union all
select 2, 'Update'select distinct * from
(
select a.*,b.PrivilegeID from Role a left join PrivilegeInRole b on a.RoleID=b.RoleID
)a
left join
(
select b.Description,c.* from Operation b
left join
(
select a.Description as Description1,b.* from Resource a left join Privilege b on a.ResourceID=b.ResourceID
)c on b.OperationID=c.OperationID
)d on a.PrivilegeID=d.PrivilegeID
RoleID RoleName PrivilegeID Description Description1 PrivilegeID ResourceID OperationID
------ ---------- ----------- ----------- ------------ ----------- ----------
1 User 1 Select Shop 1 1 1
2 Admin 2 Select Shop 2 1 1
2 Admin 2 Update Shop 2 1 2(所影响的行数为 3 行)