查询用户权限的SQL语句

查询用户权限的SQL语句

 

下面以用户ID为1的用户为例，讲解查询用户权限的SQL语句写法。

1. 从用户的角色表中查询用户权限（去掉重复记录）

select 
distinct(e.AUTHKEY)
from USER  a
left join USER_ROLE b on a.USERID=b.USERID
left join ROLE c on b.ROLEID=c.ROLEID
left join ROLE_AUTHORITY  d on c.ROLEID=d.ROLEID
left join AUTHORITY  e on d.AUTHID=e.AUTHID
where a.USERID = 1;
    
    
2. 从用户的组角色表中查询用户权限（去掉重复记录）

select 
distinct(f.AUTHKEY)
from USER  a
left join GROUP_USER b on a.USERID=b.USERID
left join GROUP_ROLE c on b.GRPID=c.GRPID
left join ROLE d on c.ROLEID=d.ROLEID
left join ROLE_AUTHORITY  e on d.ROLEID=e.ROLEID
left join AUTHORITY  f on e.AUTHID=f.AUTHID
where a.USERID = 1;

3. 联合用户的角色表和组角色表查询用户权限（去掉重复记录）

select 
distinct(e.AUTHKEY)
from USER  a
left join USER_ROLE b on a.USERID=b.USERID
left join ROLE c on b.ROLEID=c.ROLEID
left join ROLE_AUTHORITY  d on c.ROLEID=d.ROLEID
left join AUTHORITY  e on d.AUTHID=e.AUTHID
where a.USERID = 1
union
select 
distinct(f.AUTHKEY)
from USER  a
left join GROUP_USER b on a.USERID=b.USERID
left join GROUP_ROLE c on b.GRPID=c.GRPID
left join ROLE d on c.ROLEID=d.ROLEID
left join ROLE_AUTHORITY  e on d.ROLEID=e.ROLEID
left join AUTHORITY  f on e.AUTHID=f.AUTHID
where a.USERID = 1;