数据库权限设计学习笔记

数据库权限设计


用户需求
用有用户/角色可以动态分配
所有系统菜单的权限
所有权限基于角色来进行划分
用户可拥有多个解色
系统菜单可以动态修改


uml建表,导出sql语句
/*==============================================================*/
/* DBMS name: MySQL 5.0 */
/* Created on: 2012-11-7 19:20:19 */
/*==============================================================*/




drop table if exists t_menu_privilege;


drop table if exists t_privilege;


drop table if exists t_role;


drop table if exists t_sys_menu;


drop table if exists t_user;


drop table if exists t_user_role;


/*==============================================================*/
/* Table: t_menu_privilege */
/*==============================================================*/
create table t_menu_privilege
(
pkid numeric not null,
roleid varchar(16),
menuid numeric(16),
userid varchar(16),
privliegeid char(3),
primary key (pkid)
);


/*==============================================================*/
/* Table: t_privilege */
/*==============================================================*/
create table t_privilege
(
privliegeid char(3) not null,
privilegetype varchar(8),
primary key (privliegeid)
);


/*==============================================================*/
/* Table: t_role */
/*==============================================================*/
create table t_role
(
roleid varchar(16) not null,
roledescr varchar(32),
primary key (roleid)
);


/*==============================================================*/
/* Table: t_sys_menu */
/*==============================================================*/
create table t_sys_menu
(
menuid numeric(16) not null,
menudescr varchar(32),
menuurl varchar(255),
menupid varchar(16),
primary key (menuid)
);


/*==============================================================*/
/* Table: t_user */
/*==============================================================*/
create table t_user
(
userid varchar(16) not null,
password char(8),
username varchar(12),
primary key (userid)
);


/*==============================================================*/
/* Table: t_user_role */
/*==============================================================*/
create table t_user_role
(
userid varchar(16) not null,
roleid varchar(16) not null,
primary key (userid, roleid)
);


alter table t_menu_privilege add constraint FK_fk_menu_to_priv foreign key


(menuid)
references t_sys_menu (menuid) on delete restrict on update


restrict;


alter table t_menu_privilege add constraint FK_fk_privilege_typ_to_priv


foreign key (privliegeid)
references t_privilege (privliegeid) on delete restrict on update


restrict;


alter table t_menu_privilege add constraint FK_fk_role_to_priv foreign key


(userid, roleid)
references t_user_role (userid, roleid) on delete restrict on update


restrict;


alter table t_user_role add constraint FK_fk_role_id foreign key (roleid)
references t_role (roleid) on delete restrict on update restrict;


alter table t_user_role add constraint FK_fk_user_id foreign key (userid)
references t_user (userid) on delete restrict on update restrict;




添加表数据


/*插入数据*/
INSERT INTO Privilege_sys.t_user (userid,PASSWORD,username) VALUES


(1,"123","yuezhenhua");
INSERT INTO Privilege_sys.t_user (userid,PASSWORD,username) VALUES


(2,"123","zhansan");
INSERT INTO Privilege_sys.t_user (userid,PASSWORD,username) VALUES


(3,"123","lisi");
INSERT INTO Privilege_sys.t_user (userid,PASSWORD,username) VALUES


(4,"123","wangwu");
INSERT INTO Privilege_sys.t_user (userid,PASSWORD,username) VALUES


(5,"123","zhaoliu");
INSERT INTO Privilege_sys.t_user (userid,PASSWORD,username) VALUES


(6,"123","qianqi");
INSERT INTO Privilege_sys.t_user (userid,PASSWORD,username) VALUES


(7,"123","sunba");
INSERT INTO Privilege_sys.t_user (userid,PASSWORD,username) VALUES


(8,"123","zhouba");


INSERT INTO Privilege_sys.t_role (roleid,roledescr) VALUES("user","


普通用户");
INSERT INTO Privilege_sys.t_role (roleid,roledescr) VALUES


("manager","部门老总");
INSERT INTO Privilege_sys.t_role (roleid,roledescr) VALUES


("gmanager","老总");
INSERT INTO Privilege_sys.t_role (roleid,roledescr) VALUES


("admin","it管理员");




INSERT INTO Privilege_sys.t_user_role (userid,roleid) VALUES


("1","admin");
INSERT INTO Privilege_sys.t_user_role (userid,roleid) VALUES


("2","gmanager");
INSERT INTO Privilege_sys.t_user_role (userid,roleid) VALUES


("3","manager");
INSERT INTO Privilege_sys.t_user_role (userid,roleid) VALUES


("4","user");
INSERT INTO Privilege_sys.t_user_role (userid,roleid) VALUES


("5","user");
INSERT INTO Privilege_sys.t_user_role (userid,roleid) VALUES


("6","user");
INSERT INTO Privilege_sys.t_user_role (userid,roleid) VALUES


("7","user");
INSERT INTO Privilege_sys.t_user_role (userid,roleid) VALUES


("8","user");


INSERT INTO Privilege_sys.t_sys_menu (menuid,menudescr,menuurl,menupid)


VALUES ("101","报表查询","aaaa.do","0");




INSERT INTO Privilege_sys.t_sys_menu (menuid,menudescr,menuurl,menupid)


VALUES ("101","报表查询","","0");
INSERT INTO Privilege_sys.t_sys_menu (menuid,menudescr,menuurl,menupid)


VALUES ("102","月报","a1.do","101");
INSERT INTO Privilege_sys.t_sys_menu (menuid,menudescr,menuurl,menupid)


VALUES ("103","季报","a2.do","101");
INSERT INTO Privilege_sys.t_sys_menu (menuid,menudescr,menuurl,menupid)


VALUES ("104","年报","a3.do","101");
INSERT INTO Privilege_sys.t_sys_menu (menuid,menudescr,menuurl,menupid)


VALUES ("105","系统管理","","0");
INSERT INTO Privilege_sys.t_sys_menu (menuid,menudescr,menuurl,menupid)


VALUES ("106","用户管理","","105");
INSERT INTO Privilege_sys.t_sys_menu (menuid,menudescr,menuurl,menupid)


VALUES ("107","新增用户","adduser.do","106");
INSERT INTO Privilege_sys.t_sys_menu (menuid,menudescr,menuurl,menupid)


VALUES ("108","删除用户","deluser.do","106");
INSERT INTO Privilege_sys.t_sys_menu (menuid,menudescr,menuurl,menupid)


VALUES ("109","角色管理","","105");
INSERT INTO Privilege_sys.t_sys_menu (menuid,menudescr,menuurl,menupid)


VALUES ("110","新增角色","addrole.do","109");
INSERT INTO Privilege_sys.t_sys_menu (menuid,menudescr,menuurl,menupid)


VALUES ("111","删除角色","delrole.do","109");






INSERT INTO t_privilege (privliegeid,privilegetype) VALUES ('101',"增加");
INSERT INTO t_privilege (privliegeid,privilegetype) VALUES ('102',"删除");
INSERT INTO t_privilege (privliegeid,privilegetype) VALUES ('103',"修改");
INSERT INTO t_privilege (privliegeid,privilegetype) VALUES ('104',"查询");
INSERT INTO t_privilege (privliegeid,privilegetype) VALUES ('105',"打印");
INSERT INTO t_privilege (privliegeid,privilegetype) VALUES ('106',"报表");




INSERT INTO t_menu_privilege VALUES ('1','admin','105','1','101');
INSERT INTO t_menu_privilege VALUES ('2','admin','105','1','102');
INSERT INTO t_menu_privilege VALUES ('3','admin','105','1','103');
INSERT INTO t_menu_privilege VALUES ('4','admin','105','1','104');
INSERT INTO t_menu_privilege VALUES ('5','admin','105','1','105');
INSERT INTO t_menu_privilege VALUES ('6','admin','105','1','106');


INSERT INTO t_menu_privilege VALUES ('7','admin','106','1','101');
INSERT INTO t_menu_privilege VALUES ('8','admin','106','1','102');
INSERT INTO t_menu_privilege VALUES ('9','admin','106','1','103');
INSERT INTO t_menu_privilege VALUES ('10','admin','106','1','104');
INSERT INTO t_menu_privilege VALUES ('11','admin','106','1','105');
INSERT INTO t_menu_privilege VALUES ('12','admin','106','1','106');

INSERT INTO t_menu_privilege VALUES ('13','admin','107','1','101');
INSERT INTO t_menu_privilege VALUES ('14','admin','107','1','102');
INSERT INTO t_menu_privilege VALUES ('15','admin','107','1','103');
INSERT INTO t_menu_privilege VALUES ('16','admin','107','1','104');
INSERT INTO t_menu_privilege VALUES ('17','admin','107','1','105');
INSERT INTO t_menu_privilege VALUES ('18','admin','107','1','106');

INSERT INTO t_menu_privilege VALUES ('19','admin','108','1','101');
INSERT INTO t_menu_privilege VALUES ('20','admin','108','1','102');
INSERT INTO t_menu_privilege VALUES ('21','admin','108','1','103');
INSERT INTO t_menu_privilege VALUES ('22','admin','108','1','104');
INSERT INTO t_menu_privilege VALUES ('23','admin','108','1','105');
INSERT INTO t_menu_privilege VALUES ('24','admin','108','1','106');

INSERT INTO t_menu_privilege VALUES ('25','admin','109','1','101');
INSERT INTO t_menu_privilege VALUES ('26','admin','109','1','102');
INSERT INTO t_menu_privilege VALUES ('27','admin','109','1','103');
INSERT INTO t_menu_privilege VALUES ('28','admin','109','1','104');
INSERT INTO t_menu_privilege VALUES ('29','admin','109','1','105');
INSERT INTO t_menu_privilege VALUES ('30','admin','109','1','106');




INSERT INTO t_menu_privilege VALUES ('31','admin','110','1','101');
INSERT INTO t_menu_privilege VALUES ('32','admin','110','1','102');
INSERT INTO t_menu_privilege VALUES ('33','admin','110','1','103');
INSERT INTO t_menu_privilege VALUES ('34','admin','110','1','104');
INSERT INTO t_menu_privilege VALUES ('35','admin','110','1','105');
INSERT INTO t_menu_privilege VALUES ('36','admin','110','1','106');





INSERT INTO t_menu_privilege VALUES ('37','admin','111','1','101');
INSERT INTO t_menu_privilege VALUES ('38','admin','111','1','102');
INSERT INTO t_menu_privilege VALUES ('39','admin','111','1','103');
INSERT INTO t_menu_privilege VALUES ('40','admin','111','1','104');
INSERT INTO t_menu_privilege VALUES ('41','admin','111','1','105');
INSERT INTO t_menu_privilege VALUES ('42','admin','111','1','106');




一级菜单
SELECT b.menuid,b.menudescr,b.menupid FROM t_sys_menu AS a, t_sys_menu AS


b WHERE a.menuid=b.menupid AND(a.menuid='105' OR b.menupid='105');
二级菜单
SELECT * FROM t_sys_menu t WHERE NOT EXISTS(SELECT * FROM t_sys_menu


a,t_sys_menu b WHERE a.menuid=b.menupid AND a.menuid=t.menuid );


查看
角色admin对哪些菜单，哪些操作权限
SELECT DISTINCT
m.menuid,m.menudescr,m.menuurl,m.menupid,p.privliegeid,p.privilegetype
FROM
t_menu_privilege mp,
t_sys_menu m,
t_privilege p,
t_user_role r
WHERE
mp.privliegeid=p.privliegeid
AND
mp.roleid=r.roleid
AND
mp.menuid=m.menuid
AND
r.roleid='admin'
ORDER BY m.menuid;


用户yuezhenhua对哪些菜单，有哪些操作权限
SELECT DISTINCT
m.menuid,m.menudescr,m.menuurl,m.menupid,p.privliegeid,p.privilegetype
FROM
t_menu_privilege mp,
t_sys_menu m,
t_privilege p,
t_user_role r
WHERE
mp.privliegeid=p.privliegeid
AND
mp.roleid=r.roleid
AND
mp.menuid=m.menuid
AND
r.userid="1"
ORDER BY m.menuid;




需要制作的界面
用户的管理界面
角色的管理界面
用户角色的分配界面
系统菜单的管理界面
具体权限项的管理界面
系统菜单与角色间具体的权限分配界面




改进t_sys_menu表的设计
oracle 有递归
mysql没有


添加left和right两个字段


左右值无限分类实现（预排序遍历树算法）


毗邻目录模式 adjacencylist model
预排序遍历算法 modifiedfreorder tree traversal algorithm


1
2
3月报4 5季报6 7年报8
9
10
11
12增加用户13 14删除用户15
16
17
18增加角色19 20删除角色21
22
23
24




ALTER TABLE t_sys_menu ADD lef DECIMAL(16);
ALTER TABLE t_sys_menu ADD rig DECIMAL(16);




UPDATE `privilege_sys`.`t_sys_menu` SET `lef`='2',`rig`='9' WHERE


`menuid`='101';
UPDATE `privilege_sys`.`t_sys_menu` SET `lef`='3',`rig`='4' WHERE


`menuid`='102';
UPDATE `privilege_sys`.`t_sys_menu` SET `lef`='5',`rig`='6' WHERE


`menuid`='103';
UPDATE `privilege_sys`.`t_sys_menu` SET `lef`='7',`rig`='8' WHERE


`menuid`='104';
UPDATE `privilege_sys`.`t_sys_menu` SET `lef`='10',`rig`='23' WHERE


`menuid`='105';
UPDATE `privilege_sys`.`t_sys_menu` SET `lef`='11',`rig`='16' WHERE


`menuid`='106';
UPDATE `privilege_sys`.`t_sys_menu` SET `lef`='12',`rig`='13' WHERE


`menuid`='107';
UPDATE `privilege_sys`.`t_sys_menu` SET `lef`='14',`rig`='15' WHERE


`menuid`='108';
UPDATE `privilege_sys`.`t_sys_menu` SET `lef`='17',`rig`='22' WHERE


`menuid`='109';
UPDATE `privilege_sys`.`t_sys_menu` SET `lef`='18',`rig`='19' WHERE


`menuid`='110';
UPDATE `privilege_sys`.`t_sys_menu` SET `lef`='20',`rig`='21' WHERE


`menuid`='111';






在现有的节点中插入一个子节点


1 选取要被插入的节点的父节点的left的值
select lef from t_sys_menu where menuid='101';

2 原有节点的rig>left,那么全部加2
update t_sys_menu set rig=rig+2 where rig>2;


3 原有节点的lef>left，那么全部加2
update t_sys_menu set lef=lef+2 where lef>2;


4 新节点的lef和rig为left+1和left+2
INSERT INTO t_sys_menu VALUES('113','周报','a4.do','101',(2+1),(2+2));
INSERT INTO t_sys_menu VALUES('112','菜单','','-1',1,26);
查询结果
SELECT
node.menuid,
node.menudescr,
node.lef,
node.rig,
node.menuurl
FROM
t_sys_menu node,
t_sys_menu parent
WHERE
node.lef BETWEEN parent.lef AND parent.rig
AND
node.menudescr!='菜单'
GROUP BY


node.menuid,node.menudescr,node.lef,node.rig,node.menuurl,node.menupid
ORDER BY node.lef






插入一个新的节点
1 取得要插入新节点的左边节点的right的值
select rig from t_sys_menu where menuid='101';
2 原有节点的rig>right,那么全部加2
update t_sys_menu set rig=rig+2 where rig>11;
3 原有节点的lef>right,那么全部加2
update t_sys_menu set lef=lef+2 where lef>11;
4 插入新节点,lef和rig分别为right+1和right+2
insert into t_sys_menu values ('114','保单审核','','',(11+1),(11+2));

在新插入节点中插出子节点


1 选取要被插入的节点的父节点的left的值
select lef from t_sys_menu where menuid='114';

2 原有节点的rig>left,那么全部加2
update t_sys_menu set rig=rig+2 where rig>12;


3 原有节点的lef>left，那么全部加2
update t_sys_menu set lef=lef+2 where lef>12;


4 新节点的lef和rig为left+1和left+2
INSERT INTO t_sys_menu VALUES('115','手工审核','shougong.do','',(12+1),


(12+2));


删除一个节点
1 取删除菜单的left的值,right的值,和宽度(width=rigth-left+1)
select lef,rig,(rig-lef+1) width from t_sys_menu where menuid='114';
2 删除所有位于第一步中得到的left和rigth之间的节点
delete from t_sys_menu where lef between 12 and 15;
3 所有rig>right,rig-width
update t_sys_menu set rig=rig-4 where rig>15;
4 所有lef>left,lef-width

update t_sys_menu set lef=lef-4 where lef>15;

参考博客

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