多公司权限模型

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61

-- ----------------------------
-- Table structure for `users` 
-- 都是常用的字段
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` varchar(40) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  `userCode` varchar(50) NOT NULL,
  `password` varchar(50) NOT NULL,
  `email` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

---角色表
---角色是继承关系
---level,roleCode为表示层级关系,部门表类似.主要用于快速查询,抛弃递归函数.
DROP TABLE IF EXISTS `role`;
CREATE TABLE `role` (
  `id` varchar(50) NOT NULL DEFAULT '',
  `pid` varchar(50) NOT NULL DEFAULT '',--父角色ID
  `roleName` varchar(200) NOT NULL,
  `level` int NOT NULL DEFAULT 0,--角色处的层级,根角色为1,它的子角色为2 孙角色为3
  `roleCode` varchar(200) NOT NULL,--根角色001 他的子角色为001001,001002...孙角色为001001001,001002001....依次类推.
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for `model`
-- ----------------------------
DROP TABLE IF EXISTS `model`;
CREATE TABLE `model` (
  `id` varchar(50) NOT NULL,
  `modelName` varchar(200) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

----
-- ----------------------------
-- Table structure for `re_user_role`用户角色中间表
-- ----------------------------
DROP TABLE IF EXISTS `re_user_role`;
CREATE TABLE `re_user_role` (
  `id` varchar(50) NOT NULL,
  `userId` varchar(50) NOT NULL,
  `roleId` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--角色资源中间表
DROP TABLE IF EXISTS `re_role_model`;
CREATE TABLE `re_role_model` (
  `id` varchar(50) NOT NULL,
  `roleId` varchar(50) NOT NULL,
 -- `startDate` datetime,
 -- `endDate` datetime,--表示权限的生存周期,用于领导临时分配权限,这里简化暂不讨论
 -- allunit int,--主要用于判断是否级联下级部门,这里不讨论
  `pageurl` varchar(1000) NOT NULL,
  `bool` int(11) NOT NULL DEFAULT '1',--表示真假,0为假,1为真
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1
2

INSERT INTO `re_role_model` VALUES ('b1156249-9a8c-4f71-99ca-f56864401c41', 'superuser', '(.*)', '1');
INSERT INTO `re_role_model` VALUES ('fa1f2c33-b5b8-4134-ae50-53334de61fc3', 'superuser', '.*/admin/.*', '0');

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

String model_sql="SELECT re.* FROM re_role_model as re,re_user_role as rerole  
where 
re.roleId=rerole.roleId and rerole.userId=:userId order by re.bool asc";

//对比url是否匹配
boolean flag=false;
for(Re_Role_Model m:models){
String pageurl=m.getPageurl();
Integer bool=m.getBool();
Pattern pattern = Pattern.compile(pageurl);
Matcher matcher = pattern.matcher(requestURL);//请求的url是否具有权限
boolean f=matcher.matches();
if(f&&(bool==0)){//如果没有权限,跳出.
flag=false;
break;
}

if(f&&(bool==1)){
flag=true;
break;
}
}
return flag;

1
2

insert into role values('admin','','超级管理员','1','001')
insert into role values('superuser','admin','超级用户','2','001001')

1

select u.* from unit as u ,re_role_model as re where regfun('/update/'+u.id+'/',re.pageurl,re.bool) and re.roleid='admin'