● 开发环境:Eclipse+Tomcat+MySQL+SVN
● 系统架构:JQuery+Bootstrap+JFinal+Memcache
概述:每次执行查询时,会从数据权限表中获取需要权限的字段,然后从session中取出当前用户的数据权限进行,拼接sql语句进行查询数据的控制;
1:登录时将数据权限列表放到session中
//****************************只截取数据权限相关代码,登录详细代码见:T手帐01*****************************
public class SystemUtil{
/**
* 方法描述:初始化用户权限
*/
@DataSource(type=DataSourceMap.MASTER)
public static void initDataAndSource(Controller con,IUserDataService userDataService,Users user){
//获取用户数据权限,如果Ioc为空则手动实例化一个,因为拦截器调用了此方法
if(userDataService == null){
IUserDataService userDS = new UserDataServiceImpl();
userDataAuthority = userDS.getUserDataByUser(user); //非职能部门的校区可以用key为schoolIds取值
}else{
//*****************************查询方法见下**************************************
userDataAuthority = userDataService.getUserDataByUser(user);//非职能部门的校区可以用key为schoolIds取值
}
//将数据权限放到session中
con.setSessionAttr("userDataAuthority", userDataAuthority);
}
@Service.BY_TYPE
public class UserDataServiceImpl implements IUserDataService {
/**
* 方法描述:查询数据权限
*/
@Override
public Map<String, Object> getUserDataByUser(Users user) {
Map<String, Object> map = new HashMap<String, Object>();
//************************查询角色数据权限/个人数据权限*****************************
List<UserData> userDataList = UserData.dao.queryUDListGroupByColumn(user);
if(userDataList != null){
for(UserData ud:userDataList){
map.put(ud.getStr("udColumnName"), ud.getStr("udColumnId"));
}
}
//权限部门卡控时,检出非职能部分的校区
String schoolIds = Department.dao.schoolIds((String)map.get("deptId"));
map.put("schoolIds", schoolIds);
return map;
}
}
tb_user_data
/**
* 类描述:用户数据权限实体类
*/
@ModelAnno(tableName="tb_user_data",primaryKey="udId", prefix = "ud")
public class UserData extends ExXhModel<UserData> {
public static final UserData dao = new UserData();
// 页面传值,每个子项包括[daColumnId,daColumnName]
@Getter@Setter
private String[] idValueAndColumn;
//用户ID
@Getter@Setter
private Integer userId;
/**
* 方法描述:
*/
public List<UserData> queryUDListGroupByColumn(Users user){
StringBuffer sql = new StringBuffer("NOPERMISSION ");
StringBuffer sql2 = new StringBuffer("NOPERMISSION ");
//从查询岗位数据管理的结果
sql.append(" SELECT da.daColumnName AS 'udColumnName',CONCAT(GROUP_CONCAT(DISTINCT da.daColumnId),IFNULL(IF(daColumnName='deptId',(SELECT CONCAT(',',GROUP_CONCAT(deptId)) FROM tb_department WHERE deptType != 2),''),'')) AS 'udColumnId' FROM ");
sql.append(" tb_user u LEFT JOIN tb_data_role dr ON u.userDataRoleId = dr.drId LEFT JOIN tb_data_authority da ON dr.drId = da.drId LEFT JOIN tb_data_authority_time dat ON da.daId = dat.datDaId ");
sql.append(" WHERE ((CASE WHEN dat.datIsLoop = 0 THEN (dat.datStartDate <= NOW()) AND (NOW() <= dat.datEndDate) WHEN dat.datIsLoop = 1 THEN (DAY(dat.datStartDate) <= DAY(NOW())) AND (DAY(NOW()) <= DAY(dat.datEndDate)) END )OR (dat.datStartDate IS NULL AND dat.datEndDate IS NULL)) ");
sql.append(" AND u.userId = "+user.getInt("userId"));
sql.append(" GROUP BY da.daColumnName ");
//从用户自己数据管理的结果
sql2.append(" SELECT ud.udColumnName AS 'udColumnName',CONCAT(GROUP_CONCAT(DISTINCT ud.udColumnId),IFNULL(IF(udColumnName = 'deptId',(SELECT CONCAT(',', GROUP_CONCAT(deptId)) FROM tb_department WHERE deptType != 2 AND deptId IS NOT NULL),''),'')) AS 'udColumnId' FROM ");
sql2.append(" tb_user u LEFT JOIN tb_user_data ud ON u.userId = ud.userId LEFT JOIN tb_user_data_time udt ON ud.udId = udt.udtUdId ");
sql2.append(" WHERE ((CASE WHEN udt.udtIsLoop = 0 THEN (udt.udtStartDate <= NOW()) AND (NOW() <= udt.udtEndDate) WHEN udt.udtIsLoop = 1 THEN (DAY(udt.udtStartDate) <= DAY(NOW())) AND (DAY(NOW()) <= DAY(udt.udtEndDate)) END) OR (udt.udtStartDate IS NULL AND udt.udtEndDate IS NULL)) ");
sql2.append(" AND u.userId ="+user.getInt("userId"));
sql2.append(" GROUP BY ud.udColumnName");
//*****如果用户"无修改"或"修改了操作权限"(没有修改数据权限),则直接取角色(tb_data_authority)的权限,否则取个人数据权限(tb_user_data)******
if(user.getInt("userIsSpecial") == SecurityConstants.userPowerType.UPDATE_SECURITY_NO || user.getInt("userIsSpecial") == SecurityConstants.userPowerType.UPDATE_SECURITY_MANAGER){
return this.find(sql.toString());
}else{
return this.find(sql2.toString());
}
}
}
2:执行查询时,再拼接sql语句的限制条件
- 每次访问都会先经过DateJurisdictionInterceptor拦截器:
public class DateJurisdictionInterceptor implements Interceptor {
public final static ThreadLocal<Map<String, String>> jurisdictionSession = new ThreadLocal<Map<String, String>>();
public final static ThreadLocal<Map<String, Map<String, String>>> tableconfigureSession = new ThreadLocal<Map<String, Map<String, String>>>();
private ITableConfigureService service = new TableConfigureServiceImpl();
@Override
public void intercept(ActionInvocation ai) {
Controller controller = ai.getController();
Map<String, String> map = controller.getSessionAttr("userDataAuthority");
Map<String, Map<String, String>> tablemap = service.getTableConfige();
//-------------》添加可配置属性
Properties proes = new Properties();
InputStream in = this.getClass().getResourceAsStream(".."+File.separator+".."+File.separator+".."+File.separator+".."+File.separator+"applicationResources.properties");
try {
proes.load(in);
} catch (IOException e) {
e.printStackTrace();
}
if(proes.get("userDataAuthority_switch").equals("off")){
map = null;
tablemap = null;
}
//《--------------结束
jurisdictionSession.set(map);
tableconfigureSession.set(tablemap);
ai.invoke();
jurisdictionSession.remove();
tableconfigureSession.remove();
}
}
这里的两个线程局部变量的含义:
1) jurisdictionSession(ThreadLocal<Map<String, String>>)是当前用户的全部数据权限,
如:entry(“cityId”,“1”)、entry(“cityId”,“2”)
表示此用户具有city基础表的id为1和2的两个城市的数据查询权限,
结合上面的tb_user_data表更容易理解;
2 ) tableconfigureSession(ThreadLocal<Map<String, Map<String, String>>>)则是保存此系统的所有基础表及其表中需要权限控制的字段(tb_sys_table_configure),
如:entry(“tb_course_templet” ,(entry(“cityId”,“cityId”),entry(“deptId”,“deptIds”))),entry(“tb_finance_group” ,(entry(“cityId”,“fgCityId”),entry(“deptId”,null))),
这里的key和value都是“cityId”,但意义不同,前者是系统统一的“城市”字段,后者是每个表中的“表示城市字段的列名”,
结合下面的表tb_sys_table_configure更容易理解;
tb_sys_table_configure
- 经过上述的拦截器后,已把该用户的数据权限都放到本次访问的线程中,在执行find()等方法进行查询时,会根据该数据权限拼接sql语句以达到查询数据的过滤;
/**
* 继承JFinal的Model
*/
public abstract class ExXhModel<M extends XhModel> extends XhModel<M> implements Cloneable{
private static final TableInfoMapping tableInfoMapping = TableInfoMapping.me();
/**
* 重写 model父类的方法 (查询前拼接数据权限)
*/
@Override
public List<M> find(String sql, Object... paras) {
try {
boolean flag = true;
for (int i=0; i<paras.length; i++) {
if(paras[i]!=null&¶s[i].toString().indexOf(TableConfigureConstants.NOPERMISSION)>-1){
flag = false;
break;
}
}
String where = "";
if(flag){
//********************拼接数据权限*******************
where = getPermissionWhere(sql);
}
String sql1 = setPermissionWhere(where, sql);
String sqlselect = sql1.replace(TableConfigureConstants.NOPERMISSION, "");
return super.find(sqlselect,paras);
} catch (Exception e) {
throw new ActiveRecordException(e);
}
}
/**
* @Title: getPermissionWhere
* @Description: 根据sql语句获取权限sql条件
*/
private static String getPermissionWhere(String sql){
if(sql.indexOf(TableConfigureConstants.NOPERMISSION)<0){
Map<String, String> map = DateJurisdictionInterceptor.jurisdictionSession.get();
Map<String, Map<String, String>> tablemap = DateJurisdictionInterceptor.tableconfigureSession.get();
if(map!=null){
String where = "";
String sql2 = sql.toLowerCase();
Pattern p = Pattern.compile("\\btb_\\w*\\b");
Matcher m = p.matcher(sql);
while (m.find()){
String tableName = m.group();
Map<String, String> map2 = tablemap.get(tableName);
int subscript = m.end();
for (String key : map.keySet()) {//遍历数据权限
Pattern pat = Pattern.compile("\\b[a-zA-Z]\\w*\\b");
String str = "";
String sql3 = sql2.substring(subscript);
if(!StringUtil.stringIsNull(sql3)){
Matcher mat = pat.matcher(sql2.substring(subscript));
if(mat.find()){
str = mat.group();
}
}
//******************此处拼接, 如:cityId IN (1,2,3)**********************
where += addSql(str, map2, map, key,tableName);
}
}
return where;
}
}
return "";
}
/**
* @Title: addSql
* @Description: 拼SQL,如:cityId IN (1,2,3);
*/
private static String addSql(String str,Map<String, String> map,Map<String, String> jurisdictionMap,String key,String tableName){
if(map!=null){
String _key = key;
if("schoolIds".equals(_key)){
return "";
}
String KEY = map.get(_key); //字段名称 cityId
String type = map.get(_key+"Type");
String sql = "";
if(str.contains("left")||str.contains("right")||str.contains("join")||str.contains(" as ")||str.contains("where")||str.contains("on")||str.contains("inner")){
if(!StringUtil.stringIsNull(KEY)){
if("1".equals(type)){
if(XhModel.hasColumnLabel(KEY, tableName)){//sql语句中有需要控制权限的表
String str1 = jurisdictionMap.get(key);
String[] ids = str1.split(",");
for (String id : ids) {
sql += " or instr("+KEY+","+id+")>0 ";
}
sql = sql.substring(sql.indexOf("or")+2, sql.length());
sql = " and ("+sql+" or ISNULL("+KEY+") )";
}
}else{
if(XhModel.hasColumnLabel(KEY, tableName)){//sql语句中有需要控制权限的表
sql = " and ("+KEY+" in ("+jurisdictionMap.get(key)+") or ISNULL("+KEY+") ) ";
}
}
}
}else{
if(!StringUtil.stringIsNull(KEY)){
if("1".equals(type)){
if(XhModel.hasColumnLabel(KEY, tableName)){//sql语句中有需要控制权限的表
String str1 = jurisdictionMap.get(key);
String[] ids = str1.split(",");
if(StringUtil.stringIsNull(str)){
for (String id : ids) {
sql += " or instr("+KEY+","+id+")>0 ";
}
}else{
for (String id : ids) {
sql += " or instr("+str+"."+KEY+","+id+")>0 ";
}
}
sql = sql.substring(sql.indexOf("or")+2, sql.length());
if(StringUtil.stringIsNull(str)){
sql = " and ("+sql+" or ISNULL("+KEY+") )";
}else{
sql = " and ("+sql+" or ISNULL("+str+"."+KEY+") )";
}
}
}else{
if(XhModel.hasColumnLabel(KEY, tableName)){//sql语句中有需要控制权限的表
if(StringUtil.stringIsNull(str)){
sql = " and ("+KEY+" in ("+jurisdictionMap.get(key)+") or ISNULL("+KEY+") )";
}else{
sql = " and ("+str+"."+KEY+" in ("+jurisdictionMap.get(key)+") or ISNULL("+str+"."+KEY+"))";
}
}
}
}
}
return sql;
}else{
return "";
}
}
}
最终拼接结果为:
SELECT * FROM tb_city WHERE cityId IN (1, 2, 3) OR ISNULL(cityId)
后端拼接的cityId IN (1, 2, 3, 4, 5) OR ISNULL(cityId)即可达到数据控制的效果,即此用户只有id为1,2,3的城市的数据权限(查询表格的“表示城市的字段”有可能为NULL)。
顺带一提:在Controller上贴@Before(DateJurisdictionInterceptor.class)注解表示此控制器下的所有方法都要经过里面的拦截器;
@ControllerAnno(controllerkey = "/adminClass")
@Before({LoginInterceptor.class,DateJurisdictionInterceptor.class})
public class AdminClassController extends MyController {
private IClassService classService = new ClassService();
......
}