查询用户权限的SQL语句

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

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

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;