通向架构师的道路（第六天）之漫谈基于数据库的权限系统的设计
一、權限系統
這一天將講述一個基本的基于數據庫的權限管理系統的設計，在這一天的課程的最后將講述“左右值無限分類實現算法”如何來優化“系統菜單”的結構而告終。今天的內容和前幾天的基礎框架是一樣的它們都屬于基礎知識，在這些基礎知識上還可以擴展出無數的變種與進化設計。
二、先來看客戶的一個需求
2.1?用戶實際需求
1.?????? 所有的用戶、角色可動態配置
2.?????? 所有的系統菜單的權限要求具體到“增，刪，改、查、打印、導出”這樣的小權限的設計
3.?????? 所有的權限基于角色來進行劃分和判斷
4.?????? 一個用戶可能屬于多個角色
5.?????? 系統菜單也能夠動態的“增、刪、改、查”
2.2?系統權限菜單樣例
三、基于數據庫的系統權限表設計
3.1 ER(Entity Relationship)圖
圍繞上述需求，我們可以在數據庫內進行如下的表設計，下面直接給出ER圖：
3.2?表關系詳解
上述設計有6張表，其中：
T_User表
用于存放用戶信息，此處只存放基礎信息
T_Role表
用于存放系統角色信息
T_User_Role表
用于存放系統用戶與角色的匹配關系
T_Sys_Menu表
這張就是用于存放系統菜單的表了，這張表的設計主要使用了如下的表設計技巧：
注意這邊的MENU_ID與MENU_PID
如果這個菜單項是一級菜單，那么我們把它的MENU_PID設為0
如果這個菜單是另一個菜單的子菜單，那么我們就把它的MENU_PID設為它的父菜單的MENU_ID。
有了這樣的結構，我們一個遞歸就能把這顆“樹”顯示出來了，是不是？
此處以Oracle數據庫為例，不使用遞歸，直接把樹形結構在數據庫中就造型造好（當然，還有更好的方法如：有人喜歡設level或者是deep這樣的字段來簡化程序解析樹型結構菜單，稍后我們會來講一個根本不需要用遞歸的樹型菜單的設計來最大程度優化設計。）
顯示整顆樹型菜單結構的Oracle語句：| SELECT?*FROM?T_SYS_MENU STARTWITH?MENU_PID=0 CONNECTBYPRIOR?MENU_ID=MENU_PID orderby?MENU_ID |
上述語句，已經用數據查詢用句就將我們的這個“樹”的層次關系理出來了，如果我們手上有一個控件叫dtree.js，那么一個循環就可以把這個樹顯示出來了，不是嗎？
來看dtree.js的應用
| //公式:? d.add(menu_id, menu_pid, ‘menudescr’, ‘menu_url’); d = new dTree('d'); ?????????????????? d.add(0,-1,'菜單'); ?????????????????? d.add(1,0,'報表查詢','example01.html'); ?????????????????? d.add(2,1,'月報','example01.html'); ?????????????????? d.add(3,1,'季報','example01.html'); ?????????????????? d.add(4,1,'年報','example01.html'); ?????????????????? d.add(5,0,'系統管理','example01.html'); ?????????????????? d.add(6,5,'用戶管理','example01.html'); ?????????????????? d.add(7,6,'新增用戶','example01.html'); ?????????????????? d.add(8,6,'刪除用戶','example01.html'); ????????????? d.add(9,5,'角色管理','example01.html'); ?????????????????? d.add(10,9,'新增角色','example01.html'); ?????????????????? d.add(11,9,'刪除角色','example01.html') ?????????????????? document.write(d); |
大家看上面，這個是dtree.js插件，一個專門用于生成樹的js插件的使用方法，那么如果我們附以上述的sql語句在數據庫中把數據選出來后，是不是只要一個循環就可以給這個dtree.js插件顯示了，不是嗎？
我們如果不想顯示整顆樹只想顯示如：
只顯示系統管理菜單有其下列所有的子菜單，那么我們的Oracle中的Sql應該怎么寫呢？
經查“系統管理”這個菜單的MENU_ID=’105’，于是我們的Sql語句如下：
| SELECT?*FROM?T_SYS_MENU STARTWITH?MENU_ID='105' CONNECTBYPRIOR?MENU_ID=MENU_PID orderby?MENU_ID |
對吧？很簡單哈！
T_Privilege表
用于存放系統每個菜單的詳細子權限如“增，刪，改，查”
T_Menu_Privilege表
這張表就是我們的最終終結大BOSS表，它里面是一個完整的系統權限與角色關系間的對應。
比如說：
我們想要知道“user”這個角色，可以操作哪些菜單，哪些權限，那么我們的SQL語句如下：
| selectdistinct?m.menu_id,m.menu_descr,m.menu_url,m.menu_pid,p.privilege_id,p.privilege_type?from t_menu_privilege mp, t_sys_menu m, t_privilege p, t_user_role r where mp.privilege_id=p.privilege_id and?mp.role_id=r.role_id and?mp.menu_id=m.menu_id and?r.role_id='user' orderby?m.menu_id |
通過這個結果我們就知道了
1.?????? 角色“user”能操作哪些菜單
2.?????? 角色“user”對某個菜單具有什么樣的權限
進而，我們可以推出：
我們想要知道Danzel這個人，可以操作哪些菜單，以及在哪些菜單上有哪些可供操作的權限，我們使用如下的SQL語句：
| selectdistinct?m.menu_id,m.menu_descr,m.menu_url,m.menu_pid,p.privilege_id,p.privilege_type?from t_menu_privilege mp, t_sys_menu m, t_privilege p, t_user_role r where mp.privilege_id=p.privilege_id and?mp.role_id=r.role_id and?mp.menu_id=m.menu_id and?r.user_id='Danzel' orderby?m.menu_id; |
通過這個結果我們就知道了
1.?????? Danzel這個人能操作哪些菜單
2.?????? Danzel對某個菜單具有什么樣的權限
3.3?界面制作
關于jsp，什么dao層，service層的具體代碼這個就不講了，這個沒有意義的哦，我們來講設計。
登錄后如何顯示左邊的樹型菜單：
ü?? 取得用戶名
ü?? 將該用戶名作為參數input進如下的SQL語句得到該用戶在登錄后可以看到的系統菜單：
| selectdistinct?m.menu_id, m.menu_descr, m.menu_url, m.menu_pidfrom t_menu_privilege mp, t_sys_menu m, t_privilege p, t_user_role r where mp.privilege_id=p.privilege_id and?mp.role_id=r.role_id and?mp.menu_id=m.menu_id and?r.user_id='Danzel' STARTWITH?MENU_PID='0' CONNECTBYPRIOR?M.MENU_ID=M.MENU_PID orderby?M.MENU_ID |
將該結果直接給于index.jsp頁面上的dtree.js組件，一個循環，所有菜單曾樹形顯示。
知道用戶登錄后能夠對哪些菜單，并且在相關界面操作時有哪些子權限如：增、刪、改、查、打印、報表的設計：
ü?? 在登錄時得到用戶名等信息，然后將該用戶名作為參數input進入如下的sql語句：
| selectdistinct?m.menu_id,m.menu_descr,m.menu_url,m.menu_pid,p.privilege_id,p.privilege_type?from t_menu_privilege mp, t_sys_menu m, t_privilege p, t_user_role r where mp.privilege_id=p.privilege_id and?mp.role_id=r.role_id and?mp.menu_id=m.menu_id and?r.user_id='Danzel' orderby?m.menu_id; |
ü? 得到上述結果后，使用：Haspmap<String menuId, List menuList>這樣的結構將該用戶所屬的角色分對每個菜單有哪些操作（增、刪、改、查、打印、報表）進行存儲，放入用戶的session中，在以后用戶在每個界面進行點擊動作時進行判斷，或者可以寫個filter來進行判斷，是不是就可以作到：
知道該登錄用戶在登錄后可以對哪些菜單進行操作，并且擁有什么操作權限啦？
相應的我們還需要制作如下的界面：
ü?? 用戶的管理界面
ü?? 角色的管理界面
ü?? 用戶與角色的分配界面
ü?? 系統菜單的管理界面
ü?? 具體權限項的管理界面
ü?? 系統菜單與角色間具體的權限分配界面
好了，有了這些界面，一個完整的基于數據庫引擎的權限系統算是完成了。
嚴重注意：
在制作“系統菜單與角色間具體的權限分配界面”時，如果在界面上把某個角色對該條菜單的“查看”這個選項disable后，那么該角色將不擁有任何該菜單的所有權限，舉例：
某角色對菜單A擁有如下權限：
增、刪、改、打印
但是這個“查看”權限沒有，也有可能是管理員誤操作，但是從真實情況我們來說，這個角色連“查看”的權限都沒有，連菜單都進不了，他能做什么“增、刪、改。。。”等其它的操作啊？操作個頭啊！是吧？
所以一旦界面上該角色對某個系統菜單沒有了查看權限后，它對這個菜單的其它權限也必須從T_MENU_PRIVILEGE這個表中刪除。
四、改進T_SYSTEM_MENU表的設計
前面我們用的是Oracle特有的遞歸SQL將樹形菜單在從數據庫中選取出來時就已經是一顆樹的結構了，但是像MySQL，SQL SERVER， DB2等可能不帶有這樣的特SQL，那就需我們自己動手去寫遞歸，還有就是很多工程用的是jQuery的tree或者是其它相關的ajax tree，這些tree都需要用到一個字段叫level（此處指深度、層次的意思），如果按照原來的表結構，要取得這個level，恐怕是要寫遞歸算法了。就算有些數據庫有類似的語句，那也需要你去修改你的SQL語句從未影響了性能與通用性。
我們在這邊說，我們無論什么數據庫，如果都用相同的SQL就能把我們需要的東西在數據庫中就排好樹形結構然后一次性選取出來，那應該有多好啊。答案是有的，在原來的T_SYSTEM_MENU表中改動也不大，只需要增加兩個字段即可，即：lft與rgt（left, right），這種設計其實已經有了，我在此只不過結合實際例子把它應用到實際上，并且進一步詳細描述如果來實現它，它就是被稱為：
左右值無限分類實現算法也稱為預排序遍歷樹算法，對于這種層次型數據（Hierarchical Data）一般我們有兩種設計方法：
ü?? 毗鄰目錄模式(adjacencylist model)
ü?? 預排序遍歷樹算法(modifiedpreorder tree traversal algorithm)
4.1?基于lft, rgt的無限分類算法
我們來看一個圖，下面我們把我們原有的菜單畫成下面這樣的層次關系：
我們把原有的系統菜單畫成了一個個的橢圓，最外層的就是我們的菜單，然后在每個橢園的兩個端點即left與right，按照從左->右，開始用數字來標號，上面這個圖中可以看到最外層這個大橢園的lft(左)為1，它的rgt(右)為24。
那么我們可以用一條標準的SQL，而非什么數據庫自帶的特有的、特殊的SQL來顯示出這個樹形菜單，來看下面的SQL：
| SELECT ???????node.menu_id menuId, ???????node.menu_descr menuDescr, ???????node.lft, ???????node.rgt, ???????node.menu_url menuUrl, ???????(COUNT(parent.menu_id)-1) menuLevel, ???????node.menu_pid pid FROM?t_sys_menu?node, ???????t_sys_menu?parent WHERE ??????? node.lftBETWEEN?parent.lftAND?parent.rgt ????????AND?node.menu_descr!='菜單' GROUPBY?node.menu_id,node.menu_descr,node.lft,node.rgt,node.menu_url,node.menu_pid ORDERBY?node.lft |
來看顯示的結果
看看上面這個結果，怎么樣？
ü?? 樹形結構也有了（可以用于dtree來顯示）；
ü?? 層次level也有了（可以用于ajax的一些tree）；
ü?? 我們用的SQL又是最標準的所有的數據庫都能用到的SQL；
嘗到甜頭了是吧？那我們下面來看如何對這樣的基于t, rgt的數據結構來作插入操作？
4.2?如何在現有節點中插入新的子節點
如果現在我們要在“報表查詢”這個圓里加入一個菜單，假設我們就叫“周報”，那么再來看這個原有的圖發生了什么樣的改變，來看：
看到么，原有的最外層橢園的rgt+2，原有的報表查詢這個園的右邊界呢？是不是也加了2啊？而原有的“月報”這個圓的lft加了多少？也是+2！
那么來看“周報”這個圓的lft與rgt關系：
“周報”的lft= “報表查詢”這個圓的lft+1
“周報”的rgt=“報表查詢”這個圓的lft+2
于是我們就可以整理出在原有葉子中插入child的公式：
第一步：選取要被插入new child的外面這個圓的lft的值
第二步：原有的數據中所有的rgt如果>第一步中得到的lft的值，那么全部+2
第三步：原有的數據中所有的lft如果>第一步中得到的lft的值，那么全部+2
第四步：將插入的節點的lft與rgt的設計，新節點的lft =第一步中的lft+1，新節點的rgt=第一步中
的lft+2
來看一個具體的例子：
我們要在“報表查詢”即menu_id=’101’ 中插入一個新的菜單，叫“周報”，下面是按照上面四步算法的相關SQL語句：
第一步
| SELECT?lftFROM?t_sys_menuwhere?menu_id='101'; |
這一步我們得到的值為：2
第二步：
| UPDATE?t_sys_menuSET?rgt = rgt +2WHERE?rgt >2; |
第三步：
| UPDATE?t_sys_menuSET?lft = lft +2WHERE?lft >2; |
第四步：
| INSERTINTO?t_sys_menu(menu_id, menu_descr, menu_url, lft, rgt)VALUES('113','周報','周報的url', (2+1), (2+2)); |
插完后我們運行查詢SQL：
| SELECT ???????node.menu_id menuId, ???????node.menu_descr menuDescr, ???????node.lft, ???????node.rgt, ???????node.menu_url menuUrl, ???????(COUNT(parent.menu_id)-1) menuLevel, ???????node.menu_pid pid FROM?t_sys_menu?node, ???????t_sys_menu??parent WHERE ??????? node.lft?BETWEEN?parent.lft?AND?parent.rgt ????????AND?node.menu_descr!='菜單' GROUPBY?node.menu_id,node.menu_descr,node.lft,node.rgt,node.menu_url,node.menu_pid ORDERBY?node.lft |
Look, 數據正確無誤，我們來看整個t_sys_menu表里的數據：
Look，整個最外層的“圓”，右邊界增加了2，從原有的24變成了26。
1.3???? 如何插入一個新的節點
上面講的是在原有的節點中插入一個子節點，現在來講，如何插入一個新的節點，比如說：
我們現在有：報表查詢，系統管理兩大菜單，我們還想加一個菜單：保單審核，怎么來做？
我們把4.2節中“如何在現有節點中插入新的子節點”里四步公式，稍稍改動一下
第一步：選取要被插入新的節點左邊節點的rgt的值
第二步：原有的數據中所有的rgt如果>第一步中得到的rgt的值，那么全部+2
第三步：原有的數據中所有的lft如果>第一步中得到的rgt的值，那么全部+2
第四步：將插入的節點的lft與rgt的設計，新節點的lft =第一步中的rgt+1，新節點的rgt=第一步中
的rgt+2
下面來看我們在“報表查詢”與“系統管理”中間，插入一個菜單叫“保單審核”。
第一步
| SELECT?rgtFROM?t_sys_menuwhere?menu_id='101'; |
這一步我們得到的值為：11
第二步：
| UPDATE?t_sys_menuSET?rgt = rgt +2WHERE?rgt >11; |
第三步：
| UPDATE?t_sys_menuSET?lft = lft +2WHERE?lft >11; |
第四步：
| INSERTINTO?t_sys_menu(menu_id, menu_descr, menu_url, lft, rgt)VALUES('114','保單審核','', (11+1), (11+2)); |
運行下面的SQL語句我們來檢查一下插入的效果：
| SELECT ??????????????????node.menu_id menuId, ??????????????????node.menu_descr menuDescr, ??????????????????node.lft, ??????????????????node.rgt, ??????????????????node.menu_url menuUrl, ??????????????????(COUNT(parent.menu_id)-1) menuLevel, ???????????? node.menu_pid pid ??????????????FROM?t_sys_menu?node, ??????????????????t_sys_menu??parent ??????????????WHERE ?????????? node.lftBETWEEN?parent.lftAND?parent.rgt ???????????AND?node.menu_descr!='菜單' ??????????????GROUPBY?node.menu_id,node.menu_descr,node.lft,node.rgt,node.menu_url,node.menu_pid ??????????????ORDERBY?node.lft |
怎么樣，結果對了吧，呵呵！
看看整個菜單的右邊界吧，從原來的26變成了28了，是不是哦？
1.3???? 如何刪除一個節點
來看公式
第一步：選取要被刪除的菜單的lft的值，rgt的值，以及寬度（width=rgt-lft+1）；
第二步：刪除所有的位于第一步中得到的lft與rgt之間的節點；
第三步：將所有的右邊界大于第一步中得到的rgt的所有節點的rgt的值減去第一步中得到的width
第四步：將所有的左邊界大于第一步中得到的rgt的所有節點的lft的值減去第一步中得到的width
來看實際例子，我們有下面這樣的數據：
我們想將menu_id=114的保單審核刪除，當然，這是一個父節點，如果把它刪了，其子節點115即手工審核也必須被一起刪除，要不然它就成為臟數據了是不是？套用上述四步公式：
第一步：
| SELECT?lft, rgt, (rgt - lft +1) widthFROM?t_sys_menuWHERE?menu_id ='114' |
第二步：
| DELETEFROM?t_sys_menuWHERE?lftBETWEEN12AND15 |
第三步：
| UPDATE?t_sys_menuSET?rgt = rgt -4WHERE?rgt >15 |
第四部：
| UPDATE?t_sys_menuSET?lft = lft -4WHERE?lft >15 |
全部步驟完成后，我們來運行檢驗的SQL：
| SELECT ??????????????????node.menu_id menuId, ??????????????????node.menu_descr menuDescr, ??????????????????node.lft, ??????????????????node.rgt, ??????????????????node.menu_url menuUrl, ??????????????????(COUNT(parent.menu_id)-1) menuLevel, ???????????? node.menu_pid pid ??????????????FROM?t_sys_menu?node, ??????????????????t_sys_menu??parent ??????????????WHERE ?????????? node.lftBETWEEN?parent.lftAND?parent.rgt ???????????AND?node.menu_descr!='菜單' ??????????????GROUPBY?node.menu_id,node.menu_descr,node.lft,node.rgt,node.menu_url,node.menu_pid ??????????????ORDERBY?node.lft |
結果正確，再來看整個“菜單”的邊界，從原來的28縮減成了26了，結果正確。
上述這種基于lft, rgt左右值無限分類實現算法的個菜單的好處在于：
ü?? SQL語句不受特定的數據庫的限制
ü?? SQL語句通用
ü?? 直接從數據庫中遠取出來的結構化的數據即可滿足需要pid的如：dtree.js這樣的JS控件的需要也可以滿足需要level的ajax tree控件的需要。
總結
以上是生活随笔為你收集整理的通向架构师的道路（第六天）之漫谈基于数据库的权限系统的设计的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 通向架构师的道路（第五天）之tomcat
- 下一篇： 通向架构师的道路（第七天）之漫谈使用Th