create proc sp_GetPermissions
@userid int,@fid int
asSelect p.*,c.DESCRIPTION,e.username
from permission a
join role_permission b on a.p_id = b.p_id
join role c on b.roleid = c.roleid
join user_roles d on c.roleid = b.roleid
join users e on d.userid = e.userid
where e.userid = @userid
and a.f_id = @fid
@userid int,@fid int
asSelect p.*,c.DESCRIPTION,e.username
from permission a
join role_permission b on a.p_id = b.p_id
join role c on b.roleid = c.roleid
join user_roles d on c.roleid = b.roleid
join users e on d.userid = e.userid
where e.userid = @userid
and a.f_id = @fid
create proc sp_GetPermissions
@userid int,@fid int
as
Select p.*,c.DESCRIPTION,a.USERNAME
from USERS a,USER_ROLES b,ROLE c,ROLE_PERMISSION d,PERMISSION p
where a.USERID=b.USERID and b.ROLEID=c.ROLEID
and b.ROLEID=d.ROLEID and d.P_ID=p.P_IDGO
权限还有一个级别字段(数字大,级别高);即
USERA:ROLEA ROLEB (USERA可以有ROLEA和ROLEB的角色)
ROLEA:P_A P_B(1) (ROLEA可以有P_A和P_B的角色,其中P_B权限级别为1)
ROLEB: P_A P_B(2) P_C(1) (ROLEB可以有P_A、P_B和P_C的角色,其中P_B权限级别为2)PERMISSION表中的记录为
1 P_A 0
2 P_B 1
3 P_C 1现在:
DECLARE @USERIN INT
SET @USERIN =100
sp_GetPermissions(@USERIN ,1)返回的结果应该是
权限 级别
P_B 2
P_C 1