單使用者多角色許可權的MSSQL實現

資料表設計
分為使用者表、角色表、角色擁有許可權表、許可權表、使用者所屬角色表

表名：Users（使用者表）

欄位	類型	長度	說明
ID	int		自動編號，主鍵
UserName	varchar	20
Password	varchar	20

表名：Roles（角色表）

欄位	類型	長度	說明
ID	int		自動編號，主鍵
Name	varchar	50

表名：UsersRoles（使用者所屬角色表）

欄位	類型	長度	說明
ID	int		自動編號，主鍵
UserID	int		對Users.ID做外鍵
RoleID	int		對Roles.ID做外鍵

表名：Permissions（許可權表）

欄位	類型	長度	說明
ID	int		自動編號，主鍵
Name	varchar	50	許可權的名稱

表名：RolesPermissions（角色許可權表）

欄位	類型	長度	說明
ID	int		自動編號，主鍵
RoleID	int		對Roles.ID做外鍵
PermissionID	int		對Permissions.ID做外鍵
Allowed	small int		該許可權是否被允許

完成後的關係圖如下所示：
 

以下的預存程序用於檢查使用者@UserName是否擁有名稱為@Permission的許可權
CREATE Procedure CheckPermission
 (
    @UserName    varchar(20),
    @Permission    varchar(50)
 )
 AS
SELECT MIN(Allowed) FROM RolesPermissions
    INNER JOIN Permissions ON Permissions.ID = PermissionID
    INNER JOIN Roles ON Roles.ID = RoleID
    INNER JOIN UsersRoles ON UsersRoles.ID = Roles.ID
    INNER JOIN Users ON Users.ID = UsersRoles.UserID
WHERE Users.UserName=@UserName AND Permissions.Name=@Permission

單使用者多角色許可權的原理
假設使用者A現在同時有兩個角色Programmer和Contractor的許可權

Permission名稱	角色Programmer許可權	角色Contractor許可權	組合後許可權
查看檔案	允許(Allowed=1)	允許(Allowed=1)	允許
編輯檔案	允許(Allowed=1)	不允許(Allowed=0)	不允許
上傳圖片	允許(Allowed=1)	沒有此許可權的記錄	允許