用户、角色、权限表的设计(mysql)

1、查询某用户的拥有的角色信息

SELECT
	* 
FROM
	USER AS u
	LEFT JOIN user_role ur ON ur.uid = u.id
	LEFT JOIN role r ON ur.id = r.id
WHERE
	u.id =2

3、查询某个角色有什么权限

SELECT *
FROM
	permission p
	LEFT JOIN role_permission rp ON rp.pid = p.id
	LEFT JOIN role r ON rp.rid = r.id 
WHERE
	p.NAME LIKE '%管理员'

3、查询某用户拥有的权限。

SELECT *,rp.id
FROM
	permission p
	LEFT JOIN role_permission rp ON rp.pid = p.id
	LEFT JOIN role r ON rp.rid = r.id 
	WHERE r.id = (SELECT id FROM user WHERE id = 1)