* 本篇目標

設計選擇及權限管理的資料表

* 前言

.NET Freamwork 4.5 微軟推出ASP.NET Identity，這讓我們實作登入機制及權限管理更加的方便及更有安全性，詳細可參考這篇文章。本篇會自行設計資料表來實作。

* 選單資料表

新增一個Permission_Menus資料表，而表的結構如下

MenuID : 唯一值(PK)

Name : 選單名稱

Controller : MVC Controller名稱

Action : MVC View名稱

Url : 完整網址(可Null)

Description : 選單描述

ParentID : 父選單的PK

Status : 狀態 (如顯示或隱藏)

OrderSerial : 排序

其實這就是無限層選單的設計方式，透過ParentID指向所屬的父選單(如果沒有則是空值)，就可以用程式組出無限樹狀選單。

* 角色資料表

新增一個Permission_Roles資料表，而表的結構如下

RoleID : 唯一值(PK)

Name : 角色名稱 (e.g Admin , Sales .....)

Status : 狀態 (有效或無效)

* 選單 & 角色

選單與角色的關係：一個選單可能會分配給多個角色，譬如：後台維護(選單)，只能給 系統管理員 或 主管級帳號 (角色)才可瀏覽，是一對多的關係，所以我們新增一個Permission_RoleMenuMapping資料表如下：

MRoleID：唯一值(PK)

RoleID : 外來鍵，角色的PK

MenuID : 外來鍵，選單的PK

Status : 狀態 (有效或無效)

* 角色 & 使用者的關係

角色與使用者的關係：一個使用者可能會分配給多的角色，譬如：Kyle 使用者，他可能具備系統管理員，主管級身分(角色)，故他是系統管理員，所以可以看到後台維護的選單。所以我們資料表可以這樣設計：

* 關聯圖

把這個四個表關聯拉出來，就會呈現如下圖：

* 取得使用者所分配的選單

接著我們開始寫個SP，來取得所有選單所分配的角色：

角色我們用SQL的XML Path語法將他逗號分隔，詳細用法可參考此篇

[MS SQL]將多筆資料合併欄位,減少不必要的連線

ALTER proc [dbo].[sp_Permission_GetMenuList]
as

DECLARE @errorMsg nvarchar(255)

begin
SET NOCOUNT ON;
Begin Try

select * ,
isnull((
SELECT (cast(roles.Name AS NVARCHAR ) + ',') FROM Permission_RoleMenuMapping menu
left join Permission_Roles roles on menu.RoleID = roles.RoleID
where menu.MenuID = permission.MenuID
FOR XML PATH('')
),'None') as Roles
from Permission_Menus permission
order by OrderSerial

End Try
Begin Catch
set @errorMsg =error_message()
raiserror(@errorMsg,16,1)
End Catch

end

執行結果：

本資料庫可至此連結下載，使用前先新增Northwind並執行SQL Scripts

本系列程式原始碼請至我的Github下載 https://github.com/kyleap

** 如內容有誤請告知，將進行修改，謝謝 **