查询用户权限的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;