通向架構師的道路（第六天）之漫談基於資料庫的許可權系統的設計

（點選上方公眾號，可快速關註）

來源：袁鳴凱 ，

blog.csdn.net/lifetragedy/article/details/7734864

一、許可權系統

這一天將講述一個基本的基於資料庫的許可權管理系統的設計，在這一天的課程的最後將講述“左右值無限分類實現演演算法”如何來最佳化“系統選單”的結構而告終。今天的內容和前幾天的基礎框架是一樣的它們都屬於基礎知識，在這些基礎知識上還可以擴展出無數的變種與進化設計。

二、先來看客戶的一個需求

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這樣的結構將該使用者所屬的角色分對每個選單有哪些操作（增、刪、改、查、列印、報表）進行儲存，放入使用者的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。

4.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了，是不是哦？

4.4 如何刪除一個節點

來看公式

第一步：選取要被刪除的選單的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控制元件的需要。

系列

• 通向架構師的道路（第一天）之 Apache 整合 Tomcat

• 通向架構師的道路（第二天）之 apache tomcat https 應用

• 通向架構師的道路（第三天）之 apache 效能調優

• 通向架構師的道路（第四天）之 Tomcat 效能調優

• 通向架構師的道路（第五天）之 tomcat 叢集 – 群貓亂舞

看完本文有收穫？請轉發分享給更多人

關註「ImportNew」，提升Java技能