通用权限管理系统

DROP TABLE IF EXISTS operate;
CREATE TABLE operate (
  code varchar(50) NOT NULL,
  name varchar(50) NOT NULL,
  icon varchar(50),
  fun varchar(50) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS menu;
CREATE TABLE menu (
  code varchar(50) NOT NULL,
  name varchar(50) NOT NULL,
  href varchar(100),
  icon varchar(50),
  parent varchar(50) NOT NULL,
  type int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS menuop;
CREATE TABLE menuop (
  menucode varchar(50) NOT NULL,
  opcode varchar(50) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS rolepower;
CREATE TABLE rolepower (
  rolecode varchar(50) NOT NULL,
  powercode varchar(1024) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS user;
CREATE TABLE user (
  code varchar(50) NOT NULL,
  name varchar(50) NOT NULL,
  pwd varchar(50) NOT NULL,
  state int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS role;
CREATE TABLE role (
  code varchar(50) NOT NULL,
  name varchar(50) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS userrole;
CREATE TABLE userrole (
  usercode varchar(50) NOT NULL,
  rolecode varchar(50) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

insert into operate values('01','新增','add','toAdd');
insert into operate values('02','修改','modify','toUpdate');
insert into operate values('03','删除','del','doDel');
insert into menu values('01','系统管理',null,'system.gif','00',1);
insert into menu values('0101','操作管理','system/operateList.html','operate.gif','01',2);
insert into menu values('0102','菜单管理','system/menuList.html','menu.gif','01',2);
insert into menu values('0103','角色管理','system/roleList.html','role.gif','01',2);
insert into menu values('0104','用户管理','system/userList.html','user.gif','01',2);
insert into menuop values('0101','01');
insert into menuop values('0101','02');
insert into menuop values('0101','03');
insert into menuop values('0102','01');
insert into menuop values('0102','02');
insert into menuop values('0102','03');
insert into menuop values('0103','01');
insert into menuop values('0103','02');
insert into menuop values('0103','03');
insert into menuop values('0104','01');
insert into menuop values('0104','02');
insert into menuop values('0104','03');
insert into user values('0001','admin','KID/yvZHk4dbIgTPwBTwgQ==',2);
insert into role values('0001','admin');
insert into userrole values('0001','0001');
insert into rolepower values('0001','01,0101,0102,0103,0104,010101,010102,010103,010104,010201,010202,010203,010204,010301,010302,010303,010304,010401,010402,010403,010404');

 

基本业务：

1.查询用户的菜单权限：查询菜单

1.menucodes = select menucode from rolepower where rolecode =  (select userrole.rolecode from user left join userrole on user.code = userrole.usercode where user.code = '0001')

2.select * from menu where code in(menucodes);

2.查询用户的某菜单的操作权限：查询按钮

1.opcodes = select opcode from rolepower where rolecode =  (select userrole.rolecode from user left join userrole on user.code = userrole.usercode where user.code = '0001') and menucode = '0101'

2.select * from operate where code in(opcodes);

3.查询用户的所有权限：查询菜单树（采用的延迟加载技术）

首先查询一级节点，parent=00

若点击一级菜单

select * from menu where parent=’节点id’

若点击二级菜单

首先查询该菜单下可以进行的操作select opcode from menuop where menucode=’节点id’

 

 

操作管理：

删除时，先判断是否被menu引用，若引用给出提示被引用不能删除

 

菜单管理：

增加二级菜单时,还要保存选择的操作到menuop表中(一个操作一条记录)

修改二级菜单时,还要先删除menuop表中相关记录，再增加 选择的操作

删除时，先判断是否被角色引用，若引用给出提示被引用不能删除

若没被引用先删除menu表中的数据和menuop表中的数据