《通向架构师的道路(第六天)之漫谈基于数据库的权限系统的设计.docx》由会员分享，可在线阅读，更多相关《通向架构师的道路(第六天)之漫谈基于数据库的权限系统的设计.docx（9页珍藏版）》请在三一办公上搜索。
1、通向架构师的道路（第六天）之漫谈基于数据库的权限系统的设计一、权限系统二、先来看客户的一个需求2.12.2三、基于数据库的系统权限表设计3.1 ER(Entity Relationship)3.2当然，还有更好的方法如：SELECT*FROMT_SYS_MENUSTARTWITHMENU_PID=0CONNECTBYPRIORMENU_ID=MENU_PIDorderbyMENU_IDSELECT*FROMT_SYS_MENUSTARTWITHMENU_ID=105CONNECTBYPRIORMENU_ID=MENU_PIDorderbyMENU_IDselectdistinctm.menu_
2、id,m.menu_descr,m.menu_url,m.menu_pid,p.privilege_id,p.privilege_typefromt_menu_privilege mp,t_sys_menu m,t_privilege p,t_user_role rwheremp.privilege_id=p.privilege_idandmp.role_id=r.role_idandmp.menu_id=m.menu_idandr.role_id=userorderbym.menu_idselectdistinctm.menu_id,m.menu_descr,m.menu_url,m.men
3、u_pid,p.privilege_id,p.privilege_typefromt_menu_privilege mp,t_sys_menu m,t_privilege p,t_user_role rwheremp.privilege_id=p.privilege_idandmp.role_id=r.role_idandmp.menu_id=m.menu_idandr.user_id=Danzelorderbym.menu_id;3.3selectdistinctm.menu_id, m.menu_descr, m.menu_url, m.menu_pidfromt_menu_privile
4、ge mp,t_sys_menu m,t_privilege p,t_user_role rwheremp.privilege_id=p.privilege_idandmp.role_id=r.role_idandmp.menu_id=m.menu_idandr.user_id=DanzelSTARTWITHMENU_PID=0CONNECTBYPRIORM.MENU_ID=M.MENU_PIDorderbyM.MENU_IDselectdistinctm.menu_id,m.menu_descr,m.menu_url,m.menu_pid,p.privilege_id,p.privilege
5、_typefromt_menu_privilege mp,t_sys_menu m,t_privilege p,t_user_role rwheremp.privilege_id=p.privilege_idandmp.role_id=r.role_idandmp.menu_id=m.menu_idandr.user_id=Danzelorderbym.menu_id;一旦界面上该角色对某个系统菜单没有了查看权限后，它对这个菜单的其它权限也必须从T_MENU_PRIVILEGE这个表中删除。四、改进T_SYSTEM_MENU4.1SELECTCOUNT(parent.menu_id)-1) m
6、enuLevel,FROMt_sys_menuparentWHEREBETWEENparent.lftANDparent.rgtANDnode.menu_descr!=菜单GROUPBYnode.menu_id,node.menu_descr,node.lft,node.rgt,node.menu_url,node.menu_pidORDERBYnode.lft4.2SELECTlftFROMt_sys_menuwheremenu_id=101;UPDATEt_sys_menuSETrgt = rgt +2WHERErgt 2;UPDATEt_sys_menuSETlft = lft +2WH
7、ERElft 2;INSERTINTOt_sys_menu(menu_id, menu_descr, menu_url, lft, rgt)VALUES(113,周报,周报的url, (2+1), (2+2);SELECTCOUNT(parent.menu_id)-1) menuLevel,FROMt_sys_menuparentWHEREBETWEENparent.lftANDparent.rgtANDnode.menu_descr!=菜单GROUPBYnode.menu_id,node.menu_descr,node.lft,node.rgt,node.menu_url,node.menu
8、_pidORDERBYnode.lft1.3 如何插入一个新的节点第一步：选取要被插入新的节点左边节点的rgt的值rgtrgtrgtrgtSELECTrgtFROMt_sys_menuwheremenu_id=101;UPDATEt_sys_menuSETrgt = rgt +2WHERErgt 11;UPDATEt_sys_menuSETlft = lft +2WHERElft 11;INSERTINTOt_sys_menu(menu_id, menu_descr, menu_url, lft, rgt)VALUES(114,保单审核, (11+1), (11+2);SELECTCOUNT(
9、parent.menu_id)-1) menuLevel,FROMt_sys_menuparentWHEREBETWEENparent.lftANDparent.rgtANDnode.menu_descr!=菜单GROUPBYnode.menu_id,node.menu_descr,node.lft,node.rgt,node.menu_url,node.menu_pidORDERBYnode.lft1.3 如何删除一个节点SELECTlft, rgt, (rgt - lft +1) widthFROMt_sys_menuWHEREmenu_id =114DELETEFROMt_sys_men
10、uWHERElftBETWEEN12AND15UPDATEt_sys_menuSETrgt = rgt -4WHERErgt 15UPDATEt_sys_menuSETlft = lft -4WHERElft 15SELECTCOUNT(parent.menu_id)-1) menuLevel,FROMt_sys_menuparentWHEREBETWEENparent.lftANDparent.rgtANDnode.menu_descr!=菜单GROUPBYnode.menu_id,node.menu_descr,node.lft,node.rgt,node.menu_url,node.menu_pidORDERBYnode.lft
