UIs UIId Name 1 UserView 2 UserManagerRoles Table RoleId RoleName 1 admin 2 vip Permissions Table RoleID UIIdUsers UsersId UsersName UserRoles UsersId RoleId @UsersID --已知(或者根据UsersName获取)不同角色 SELECT r.RoleID,r.RoleName FROM Roles r INNER JOIN UserRoles ur ON r.RoleID = ru.RoleID WHERE ur.UsersID = @UsersID可以访问的界面 SELECT UIId, Name FROM UI u INNER JOIN Permissions p ON u.UIId = p.UIId INNER JOIN UserRoles ur ON p.RoleID = ur.RoleID WHERE ur.UsersID = @UsersID
UIId Name
1 UserView
2 UserManagerRole Table
RoleId RoleName
1 admin
2 vip Permission Table
RoleID UIId
1 1
1 2
1 3
2 3
把用户表这样设置合理吗 ?
Users
UsersId UsersName RoleId ... ... ...
UsersId UsersName UserRoles
UsersId RoleId
要么用一条SQL语句查询(对我来说比较难),要么分角色取出来放在数组里面然后合并数组,把重叠的权限去掉。要么还有新方法? 谢谢
UIId Name
1 UserView
2 UserManagerRoles Table
RoleId RoleName
1 admin
2 vip Permissions Table
RoleID UIIdUsers
UsersId UsersName UserRoles
UsersId RoleId
@UsersID --已知(或者根据UsersName获取)不同角色
SELECT r.RoleID,r.RoleName
FROM Roles r
INNER JOIN UserRoles ur
ON r.RoleID = ru.RoleID
WHERE ur.UsersID = @UsersID可以访问的界面
SELECT UIId, Name
FROM UI u
INNER JOIN Permissions p
ON u.UIId = p.UIId
INNER JOIN UserRoles ur
ON p.RoleID = ur.RoleID
WHERE ur.UsersID = @UsersID