系統許可權資料庫設計方案

cyxlzzs發表於2013-01-25
資料庫

一、問題描述

目前基本上的系統都會涉及到許可權的控制，而且粒度都比較小，一般都要控制到具體視窗的具體操作上。而要達到這種要求，一個可行的資料庫設計將顯得非常有幫助。下面我們就設計一個通用型的資料庫來達到許可權的控制

二、測試環境

作業系統：windows xp

資料庫：mysql5.0

輔助設計工具：PowerDesigner15

三、設計過程

1、利用PowerDesigner設計出需要的表，如下圖

2、匯出sql指令碼，如下

/*==============================================================*/
/* DBMS name:      MySQL 5.0                                    */
/* Created on:     2013-1-25 11:19:43                           */
/*==============================================================*/


drop table if exists T_MENU;

drop table if exists T_MENU_PRIVILEGE;

drop table if exists T_PRIVILEGE;

drop table if exists T_ROLE;

drop table if exists T_USER;

drop table if exists T_USER_ROLE;

/*==============================================================*/
/* Table: T_MENU                                                */
/*==============================================================*/
create table T_MENU
(
   MENUID               int(4) not null auto_increment,
   NAME                 varchar(50),
   URL                  varchar(100) comment '目標網址',
   PARENTID             bigint comment '父選單',
   LFT                  int(4) comment '左邊界',
   RGT                  int(4) comment '右邊界',
   primary key (MENUID)
);

alter table T_MENU comment '選單表';

/*==============================================================*/
/* Table: T_MENU_PRIVILEGE                                      */
/*==============================================================*/
create table T_MENU_PRIVILEGE
(
   MENU_PRIVILEGE_ID    bigint not null auto_increment,
   MENUID               int(4),
   PRIVILEGEID          int(3),
   ROLEID               int(3),
   primary key (MENU_PRIVILEGE_ID)
);

alter table T_MENU_PRIVILEGE comment '角色選單許可權表';

/*==============================================================*/
/* Table: T_PRIVILEGE                                           */
/*==============================================================*/
create table T_PRIVILEGE
(
   PRIVILEGEID          int(3) not null auto_increment,
   NAME                 varchar(50) comment '許可權名稱（增加，刪除……）',
   primary key (PRIVILEGEID)
);

alter table T_PRIVILEGE comment '許可權表';

/*==============================================================*/
/* Table: T_ROLE                                                */
/*==============================================================*/
create table T_ROLE
(
   ROLEID               int(3) not null auto_increment,
   ROLENAME             varchar(20) comment '角色名稱',
   primary key (ROLEID)
);

alter table T_ROLE comment '角色表';

/*==============================================================*/
/* Table: T_USER                                                */
/*==============================================================*/
create table T_USER
(
   USERID               bigint not null auto_increment,
   USERNAME             varchar(50),
   PASSWORD             varchar(20),
   primary key (USERID)
);

alter table T_USER comment '使用者表';

/*==============================================================*/
/* Table: T_USER_ROLE                                           */
/*==============================================================*/
create table T_USER_ROLE
(
   USER_ROLE_ID         bigint not null auto_increment,
   USERID               bigint,
   ROLEID               int(3),
   primary key (USER_ROLE_ID)
);

alter table T_USER_ROLE comment '使用者角色';

alter table T_MENU_PRIVILEGE add constraint FK_Reference_3 foreign key (MENUID)
      references T_MENU (MENUID) on delete restrict on update restrict;

alter table T_MENU_PRIVILEGE add constraint FK_Reference_4 foreign key (ROLEID)
      references T_ROLE (ROLEID) on delete restrict on update restrict;

alter table T_MENU_PRIVILEGE add constraint FK_Reference_5 foreign key (PRIVILEGEID)
      references T_PRIVILEGE (PRIVILEGEID) on delete restrict on update restrict;

alter table T_USER_ROLE add constraint FK_Reference_1 foreign key (USERID)
      references T_USER (USERID) on delete restrict on update restrict;

alter table T_USER_ROLE add constraint FK_Reference_2 foreign key (ROLEID)
      references T_ROLE (ROLEID) on delete restrict on update restrict;


從指令碼中可以清晰的看出每個表和欄位的作用及資料型別，這裡面著重要理解的是menu資料表中的左右邊界問題，可以結合下面的圖進行理解

3、對於選單的操作，我們開發以下幾個儲存過程來進行操作

1）新增子選單AddChildMenu，每次新增的子選單都在最前面，如果需要新增到後面，則可以採用下面的AppendMenu來實現，程式碼如下

CREATE DEFINER=`root`@`localhost` PROCEDURE `AddChildMenu`(
       IN P_PARENTID int(11),
       IN P_MENUNAME VARCHAR(50),
       IN P_MENUURL VARCHAR(100))
BEGIN
     DECLARE VAL_LFT INT(4);
     -- 獲取父選單的lft值
     SELECT LFT INTO VAL_LFT FROM T_MENU WHERE MENUID=P_PARENTID;
     -- 將所有rgt大於當前父選單lft值的選單的rgt+2
     UPDATE T_MENU SET RGT=RGT+2 WHERE RGT>VAL_LFT;
     -- 將所有lft大於當前父選單lft值的選單的lft+2
     UPDATE T_MENU SET LFT=LFT+2 WHERE LFT>VAL_LFT;
     -- 插入新的子選單lft=lft+1,rgt=lft+2
     INSERT INTO T_MENU(NAME,URL,PARENTID,LFT,RGT) VALUES(P_MENUNAME,P_MENUURL,P_PARENTID,VAL_LFT+1,VAL_LFT+2);
     -- 顯示結果
     SELECT * FROM T_MENU;
END;


呼叫示例 CALL AddChildMenu(1,'使用者管理',');

2)、附加選單AppendMenu，將選單附加都某個選單的後面，程式碼如下

CREATE DEFINER=`root`@`localhost` PROCEDURE `AppendMenu`(
       IN P_MENUID int(11),
       IN P_MENUNAME VARCHAR(50),
       IN P_MENUURL VARCHAR(100))
BEGIN
     DECLARE VAL_RGT INT(4);
     DECLARE VAL_PARENTID INT(11);
     -- 獲取當前選單的rgt值
     SELECT RGT,PARENTID INTO VAL_RGT,VAL_PARENTID FROM T_MENU WHERE MENUID=P_MENUID;
     -- 將所有rgt大於當前選單rgt值的選單的rgt+2
     UPDATE T_MENU SET RGT=RGT+2 WHERE RGT>VAL_RGT;
     -- 將所有lft大於當前選單rgt值的選單的lft+2
     UPDATE T_MENU SET LFT=LFT+2 WHERE LFT>VAL_RGT;
     -- 插入新的選單lft=rgt+1,rgt=rgt+2
     INSERT INTO T_MENU(NAME,URL,PARENTID,LFT,RGT) VALUES(P_MENUNAME,P_MENUURL,VAL_PARENTID,VAL_RGT+1,VAL_RGT+2);
     -- 顯示結果
     SELECT * FROM T_MENU;
END;


呼叫示例，CALL AppendMenu(2,'角色管理','');

3)、刪除選單DelMenu，程式碼如下

CREATE DEFINER=`root`@`localhost` PROCEDURE `DelMenu`(
       IN P_MENUID int(11)
       )
BEGIN
     DECLARE VAL_LFT INT(4);
     DECLARE VAL_RGT INT(4);
     DECLARE VAL_WIDTH INT(4);
     -- 獲取當前選單的lft和rgt值
     SELECT LFT,RGT INTO VAL_LFT,VAL_RGT FROM T_MENU WHERE MENUID=P_MENUID;
     SET VAL_WIDTH=VAL_RGT-VAL_LFT+1;
     -- 刪除lft到rgt之間的選單
     DELETE FROM T_MENU WHERE LFT BETWEEN VAL_LFT AND VAL_RGT;
     -- 將所有的右邊界大於第一步中得到的rgt的所有節點的rgt的值減去第一步中得到的寬度width
     UPDATE T_MENU SET RGT=RGT-VAL_WIDTH WHERE RGT>VAL_RGT;
     -- 將所有的左邊界大於第一步中得到的rgt的所有節點的lft的值減去第一步中得到的寬度width
     UPDATE T_MENU SET LFT=LFT-VAL_WIDTH WHERE LFT>VAL_RGT;
     -- 顯示結果
     SELECT * FROM T_MENU;
END;


呼叫示例 CALL DelMenu(2);

4)、每個儲存過程的實現和演算法具體參考程式碼註釋

4、測試結果

1)、在執行過插入選單操作後的資料如下

2)、利用sql語句查詢出所有選單及其層次關係，程式碼如下

SELECT MENU.MENUID,
       MENU.PARENTID,
       MENU.NAME,
       MENU.URL,
       MENU.LFT,
       MENU.RGT,
       COUNT(PARENT.MENUID) MENULEVEL
FROM T_MENU MENU,T_MENU PARENT
WHERE MENU.LFT BETWEEN PARENT.LFT AND PARENT.RGT
GROUP BY MENU.MENUID,MENU.PARENTID,MENU.NAME,MENU.URL,MENU.LFT,MENU.RGT
ORDER BY MENULEVEL


結果如下

版權宣告：本文為博主原創文章，未經博主允許不得轉載。

相關文章