本文介绍在持久化层使用Mybatis时,如何自动实现数据权限的SQL拼接。实现思路是通过注解配置数据权限信息,通过Mybatis的插件功能,动态的修改执行的SQL。通过解析原查询SQL和注解配置信息,拼接数据权限SQL到查询条件中。
1.配置注解
使用注解,可以方便配置和业务逻辑处理。只对配置了注解的Mapper方法进行SQL增强处理。
代码示例:
@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
public @interface DataPermissions {
/**
* 权限控制信息
* [{table_name:column_name[:role_type]}]
* role_type 与 列名相同时,可以不写
* 例如:
* @DataPermissions(
* {
* 'order_info:dept_id:dept',
* 'dept_info:dept_id'
* }
* )
* @return
*/
String[] value() ;
}
@Mapper
public interface TestMapper {
@DataPermissions({"f_goods_info:org_id:"+Constant.PERMISSIONS_TYPE_ALL_DEPT})
List<FResult> listAll(FParamDTO param);
}
2.创建插件
Mybatis插件通过实现接口org.apache.ibatis.plugin.Interceptor完成。具体业务逻辑在方法intercept中进行实现.
1 @Component
2 @Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class,Integer.class})})
3 public class PermissionsInterceptor implements Interceptor {
4
5 @Autowired
6 private PermissionsValueUtil valueUtil;
7
8 private Logger log = LoggerFactory.getLogger(PermissionsInterceptor.class);
9
10 @Override
11 public Object intercept(Invocation invocation) throws Throwable {
12 // 方法一
13 StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
14 MetaObject metaObject = MetaObject.forObject(statementHandler, SystemMetaObject.DEFAULT_OBJECT_FACTORY, SystemMetaObject.DEFAULT_OBJECT_WRAPPER_FACTORY, new DefaultReflectorFactory());
15 //先拦截到RoutingStatementHandler,里面有个StatementHandler类型的delegate变量,其实现类是BaseStatementHandler,然后就到BaseStatementHandler的成员变量mappedStatement
16 MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement");
17 //id为执行的mapper方法的全路径名,如com.uv.dao.UserMapper.insertUser
18 String id = mappedStatement.getId();
19 //sql语句类型 select、delete、insert、update
20 String sqlCommandType = mappedStatement.getSqlCommandType().toString();
21 BoundSql boundSql = statementHandler.getBoundSql();
22
23 //获取到原始sql语句
24 String sql = boundSql.getSql();
25 String[] permissionsValue = null;
26
27 //注解逻辑判断 添加注解了才拦截
28 Class<?> classType = Class.forName(mappedStatement.getId().substring(0, mappedStatement.getId().lastIndexOf(".")));
29 String mName = mappedStatement.getId().substring(mappedStatement.getId().lastIndexOf(".") + 1, mappedStatement.getId().length());
30 for (Method method : classType.getDeclaredMethods()) {
31 if (method.isAnnotationPresent(DataPermissions.class) && (mName.equals(method.getName()) || mName.equals(method.getName()+"_COUNT") )) {
32 DataPermissions permissions = method.getAnnotation(DataPermissions.class);
33 permissionsValue = permissions.value();
34 }
35 }
36
37 if(permissionsValue != null && permissionsValue.length > 0){
38 try {
39 List<PermissionCondition> permissionConditions = new ArrayList<PermissionCondition>();
40 for(String permissions : permissionsValue){
41 String[] strs = permissions.split(":");
42 if(strs.length >= 2){
43 String valueName = strs[1];
44 if(strs.length>2){
45 valueName = strs[2];
46 }
47 List<String> values = valueUtil.getValues(valueName);
48 if(CollectionUtils.isNotEmpty(values)){
49 PermissionCondition condition = new PermissionCondition();
50 condition.setTableName(strs[0]);
51 condition.setColumnName(strs[1]);
52 condition.setValues(values);
53 //
54 60 condition.setOperator(PermissionCondition.CONDITION_IN);
61 63 permissionConditions.add(condition);
64 }
65 }
66 }
67
68 //生成增强权限控制后的SQL语句
69 String newSql = DataPermissionsFdbSqlParseUtil.convert(sql,permissionConditions);
70 if(StringUtils.isNotBlank(newSql)){
71 Field field = boundSql.getClass().getDeclaredField("sql");
72 field.setAccessible(true);
73 field.set(boundSql, newSql);
74 }
75 } catch (Exception e) {
76 log.error("生成权限SQL时,发生错误!"+e.getMessage(),e);
77 log.error("CLASS:"+classType.getName());
78 log.error("METHOD:"+mName);
79 }
80 }
81 return invocation.proceed();
82 }
83
84 @Override
85 public Object plugin(Object target) {
86 if (target instanceof StatementHandler) {
87 return Plugin.wrap(target, this);
88 } else {
89 return target;
90 }
91
92 }
93
94 @Override
95 public void setProperties(Properties properties) {
96
97 }
98
99
100 }
备注:PermissionsValueUtil 需要自己实现,通过自己的业务逻辑返回权限值List
先获取到执行的sql类和方法名(_COUNT后缀的方法是,分页插件生成的,涉及分页插件时,注意特殊处理一下)。
通过方法是否配置注解,判断是否需要sql处理。如果配置了数据权限,就需要解析注解配置信息。然后通过配置信息和用户信息进行逻辑处理(此处根据具体业务进行实现),返回数据权限具体值。
生成增强SQL后,通过反射替换执行sql。
1 public class PermissionCondition {
2
3 public final static String CONDITION_EQUALS = " = ";
4
5 public final static String CONDITION_LIKE = " LIKE ";
6
7 public final static String CONDITION_START_WITH = " START_WITH ";
8
9 public final static String CONDITION_IN= " in ";
10
11 private String tableName;
12 private String columnName;
13 private String operator;
14 private List<String> values;
15 }
3.SQL编辑
编辑SQL需要解析,在查询片段的指定部分插入对应条件。sql的解析是一件很麻烦的事情,因为不同的数据库语法有差异,还有子查询、函数等很多种情况。所以采用第三方解析JAR,本人采用的是foundationdb,实际应用上不是很方便,很多SQL经处理后变化很大,需要加很多特殊处理。如果有更好的选择,尽量在做此功能时不要采用他
依赖:
1 <dependency>
2 <groupId>com.foundationdb</groupId>
3 <artifactId>fdb-sql-parser</artifactId>
4 <version>1.3.0</version>
5 </dependency>
DataPermissionsFdbSqlParseUtil:
1 /**
2 * 权限控制SQL处理类
3 */
4 public class DataPermissionsFdbSqlParseUtil {
5
6 private static Pattern paramPattern = Pattern.compile("\\$\\d*");
7 private static String LIMIT_OFFSET_STR = "LIMIT ? OFFSET ?";
8 private static String LIMIT_STR = "LIMIT ?, ? ";
9
10 private static final String[] PARSE_KEY_WORD = {"year"};
11
12 public static String convert(String sql,List<PermissionCondition> permissionConditions){
13 try {
14
15 sql = before(sql);
16 //原SQL解析
17 SQLParser parser = new SQLParser();
18 StatementNode stmt = parser.parseStatement(sql);
19
20 //解释的节点文件转换成SQL,转换的过程中在WHERE中增加权限条件
21 NodeToString unparser = new FNodeToString(permissionConditions);
22 String newSql = unparser.toString(stmt);
23
24 Matcher matcher = paramPattern.matcher(newSql);
25 StringBuffer retStr = new StringBuffer();
26 while (matcher.find()) {
27 matcher.appendReplacement(retStr, "?");
28 }
29 matcher.appendTail(retStr);
30 newSql = retStr.toString();
31 if(newSql.indexOf(LIMIT_OFFSET_STR)>0){
32 newSql = newSql.replace(LIMIT_OFFSET_STR,LIMIT_STR);
33 }
34 if(newSql.indexOf("(INTERVAL")>=0){
35 newSql = newSql.replaceAll("(\\()(INTERVAL \\d MONTH)(\\))","$2");
36 }
37 if(newSql.indexOf("CAST(1 AS INTERVAL YEAR)")>=0){
38 newSql = newSql.replaceAll("\\(CAST\\(1 AS INTERVAL YEAR\\)\\)","INTERVAL 1 YEAR");
39 }
40 newSql = after(newSql);
41 System.out.println("ORI SQL :");
42 System.out.println(sql);
43 System.out.println("NEW SQL :");
44 System.out.println(newSql);
45 return newSql;
46 } catch (StandardException e) {
47 sql = after(sql);
48 e.printStackTrace();
49 }
50 return sql;
51 }
52
53 public static String before(String sql){
54
55 if(sql.indexOf("INTERVAL 1 YEAR")<0){
56 for(String kw : PARSE_KEY_WORD){
57 int index = sql.toUpperCase().indexOf(kw.toUpperCase());
58 while(index >= 0){
59 String[] ks = kw.split("");
60 String w =StringUtils.join(ks,"_");
61 sql = sql.substring(0,index)+w+sql.substring(index+kw.length());
62 index = sql.toUpperCase().indexOf(kw.toUpperCase());
63 }
64 }
65 }
66 return sql;
67 }
68
69 public static String after(String sql){
70 for(String kw : PARSE_KEY_WORD){
71 String[] ks = kw.split("");
72 String w =StringUtils.join(ks,"_");
73 int index = sql.toUpperCase().indexOf(w.toUpperCase());
74 while(index >= 0){
75 sql = sql.substring(0,index)+kw+sql.substring(index+w.length());
76 index = sql.toUpperCase().indexOf(w.toUpperCase());
77 }
78 }
79 return sql;
80 }81 }
此处代码比较混乱,因为在使用中发现很多时候好用SQL变成不好用了,加了很多特殊处理。
通过FNodeToString类来处理SQL,处理后,根据具体情况,进行一些SQL修正。
SQL编辑核心处理类 FNodeToString :
核心业务逻辑是,在解析FROM时记录表名和别名,然后在解析WHERE时(所以必须包含WHERE条件),根据表名和传进来的权限信息,进行动态SQL拼接。处理完成后,清空FROM解析的表名,以便UNION或其他多段SQL情况时,解析不完全。
通过fromList方法和parseTable方法进行表名解析和记录,binaryLogicalOperatorNode、binaryComparisonOperatorNode、inListOperatorNode几个方法是目前处理时,涉及可以添加where条件的方法。getWhere方法,生成where条件。里面的其他重写方法,是为了处理sql解析时会出现的问题的特殊处理。
如果使用的是其他JAR,可能不用我这里写的这么混乱。
1 /**
2 * 节点转换SQL
3 * 过程中会根据权限配置,动态增加WHERE条件
4 * 处理过程
5 * 1.解析原SQL语句为节点结构
6 * 2.将节点结构在组装成SQL
7 * 自定义处理部分
8 * 1.解析出SQL包含的表
9 * 2.根据表增加权限where条件
10 */
11 public class FNodeToString extends NodeToString {
12
13
14 //SQL中解析设计的表
15 private List<FdbSqlParseTable> tables = new ArrayList<>();
16 //需要添加的权限
17 private List<PermissionCondition> conditions = new ArrayList<>();
18 //条件标识符
19 private boolean initCondition = true;
20 private String where = "";
21
22 /**
23 * FROM 节点
24 *
25 * @param node
26 * @return
27 * @throws StandardException
28 */
29 @Override
30 protected String fromList(FromList node) throws StandardException {
31 this.initCondition = true;
32 String fromList = super.fromList(node);
33 node.forEach(new Consumer<FromTable>() {
34 @Override
35 public void accept(FromTable fromTable) {
36 parseTable(fromTable);
37 }
38 });
39 return fromList;
40 }
41
42 /**
43 * 解析表
44 * 主要解析,直接的表和 外关联的表
45 *
46 * @param fromTable
47 */
48 private void parseTable(ResultSetNode fromTable) {
49 if (fromTable == null) {
50
51 } else if (fromTable instanceof FromBaseTable) {
52 FromBaseTable baseTable = (FromBaseTable) fromTable;
53 FdbSqlParseTable table = new FdbSqlParseTable(baseTable.getOrigTableName().getFullTableName(), baseTable.getCorrelationName());
54 tables.add(table);
55 } else if (fromTable instanceof FromSubquery) {
56 // FromSubquery subquery = (FromSubquery)fromTable;
57 // FdbSqlParseTable table = new FdbSqlParseTable(subquery.getExposedName(),subquery.getCorrelationName());
58 // tables.add(table);
59 // System.out.println(table);
60 } else if (fromTable instanceof HalfOuterJoinNode) {
61 HalfOuterJoinNode outerJoinNode = (HalfOuterJoinNode) fromTable;
62 parseTable(outerJoinNode.getLeftResultSet());
63 parseTable(outerJoinNode.getRightResultSet());
64 }else if (fromTable instanceof JoinNode) {
65 JoinNode joinNode = (JoinNode) fromTable;
66 parseTable(joinNode.getLeftResultSet());
67 parseTable(joinNode.getRightResultSet());
68 } else {
69 }
70 }
71
72 @Override
73 protected String subqueryNode(SubqueryNode node) throws StandardException {
74 String subQuery = super.subqueryNode(node);
75 System.out.println("subQuery =>" + subQuery);
76 return subQuery;
77 }
78
79 @Override
80 protected String fromSubquery(FromSubquery node) throws StandardException {
81 String fromSubquery = super.fromSubquery(node);
82 System.out.println("fromSubquery =>" + fromSubquery);
83 return fromSubquery;
84 }
85
86 @Override
87 protected String unionNode(UnionNode node) throws StandardException {
88 String union = " UNION ";
89 if(node.isAll()){
90 union += " ALL ";
91 }
92 String sql = this.toString(node.getLeftResultSet()) + union + this.toString(node.getRightResultSet());
93 return sql;
94 }
95
96 @Override
97 protected String javaToSQLValueNode(JavaToSQLValueNode node) throws StandardException {
98 JavaValueNode jNode = node.getJavaValueNode();
99
100 if (jNode != null && jNode instanceof StaticMethodCallNode) {
101 StaticMethodCallNode smcn = (StaticMethodCallNode) jNode;
102 if ("CONCAT".equals(smcn.getMethodName().toUpperCase())) {
103
104 JavaValueNode[] values = smcn.getMethodParameters();
105 if (values != null) {
106 Object[] vArray = new Object[values.length];
107 for(int i=0;i<values.length;i++){
108 JavaValueNode v = values[i];
109 if(v instanceof SQLToJavaValueNode){
110 ValueNode vn = ((SQLToJavaValueNode)v).getSQLValueNode();
111 if(vn instanceof ColumnReference){
112 vArray[i] = ((ColumnReference)vn).getColumnName();
113 }else if(vn instanceof CharConstantNode){
114 vArray[i] = "'"+((CharConstantNode)vn).getValue()+"'";
115 }else if(vn instanceof ParameterNode){
116 vArray[i] = "?";
117 }
118 }else{
119 vArray[i] = v;
120 }
121
122 }
123 return " CONCAT(" + StringUtils.join(vArray, ",") + ") ";
124 }
125 }
126
127 }
128 return super.javaToSQLValueNode(node);
129 }
130
131 @Override
132 protected String resultColumnList(ResultColumnList node) throws StandardException {
133 return super.resultColumnList(node);
134 }
135
136 protected String aggregateNode(AggregateNode node) throws StandardException {
137 String distinct = node.isDistinct()?"DISTINCT ":"";
138 return node.getOperand() == null?node.getAggregateName():node.getAggregateName() + "("+distinct + this.toString(node.getOperand()) + ")";
139 }
140
141 @Override
142 protected String resultColumn(ResultColumn node) throws StandardException {
143 String ret = super.resultColumn(node);
144 ValueNode expNode = node.getExpression();
145 if (expNode != null && expNode instanceof GroupConcatNode) {
146 ValueNode operNode = ((GroupConcatNode) expNode).getOperand();
147 if (operNode != null && operNode instanceof JavaToSQLValueNode) {
148 String str = javaToSQLValueNode((JavaToSQLValueNode)operNode);
149 String operNodeStr = operNode.toString();
150 ret = ret.replace(operNodeStr,str);
151 }
152 }
153 return ret;
154 }
155
156 /**
157 * 解析WHERE 条件节点
158 *
159 * @param node
160 * @return
161 * @throws StandardException
162 */
163 @Override
164 protected String binaryLogicalOperatorNode(BinaryLogicalOperatorNode node) throws StandardException {
165 String binaryLogicalOperatorNode = super.binaryLogicalOperatorNode(node);
166 //条件节点会有多个,但权限条件的增加只调用一次
167 if (this.initCondition && tables.size() > 0) {
168 //生成WHERE 语句
169 this.where = getWhere(this.tables, this.conditions);
170 this.initCondition = false;
171 if (StringUtils.isNotBlank(this.where)) {
172 binaryLogicalOperatorNode = this.where + " AND " + binaryLogicalOperatorNode;
173 }
174 this.tables.clear();
175 }
176 // System.out.println("binaryLogicalOperatorNode =>" + binaryLogicalOperatorNode);
177 return binaryLogicalOperatorNode;
178 }
179
180 // binaryComparisonOperatorNode
181
182 @Override
183 protected String binaryComparisonOperatorNode(BinaryComparisonOperatorNode node) throws StandardException {
184 String binaryLogicalOperatorNode = super.binaryComparisonOperatorNode(node);
185 if (this.initCondition && tables.size() > 0) {
186 //生成WHERE 语句
187 this.where = getWhere(this.tables, this.conditions);
188 this.initCondition = false;
189 if (StringUtils.isNotBlank(this.where)) {
190 binaryLogicalOperatorNode = this.where + " AND " + binaryLogicalOperatorNode;
191 }
192 this.tables.clear();
193 }
194 return binaryLogicalOperatorNode;
195 }
196
197 @Override
198 protected String inListOperatorNode(InListOperatorNode node) throws StandardException {
199 String inListOperatorNode = super.inListOperatorNode(node);
200 if (this.initCondition && tables.size() > 0) {
201 //生成WHERE 语句
202 this.where = getWhere(this.tables, this.conditions);
203 this.initCondition = false;
204 if (StringUtils.isNotBlank(this.where)) {
205 inListOperatorNode = this.where + " AND " + inListOperatorNode;
206 }
207 this.tables.clear();
208 }
209 return inListOperatorNode;
210 }
211
212 @Override
213 protected String castNode(CastNode node) throws StandardException {
214 String castNodeSql = super.castNode(node);
215 if(node.getType().toString().equals("INTERVAL MONTH")){
216 castNodeSql = "INTERVAL "+this.toString(node.getCastOperand())+" MONTH";
217 }
218 return castNodeSql;
219 }
220
221
222 /**
223 * 将权限语句生成WHERE 条件
224 *
225 * @param tables
226 * @param conditions
227 * @return
228 */
229 public static String getWhere(List<FdbSqlParseTable> tables, List<PermissionCondition> conditions) {
230 StringBuffer ret = new StringBuffer();
231 if (tables != null && conditions != null) {
232 //解析表名
233 Map<String, FdbSqlParseTable> tableMap = new HashMap<String, FdbSqlParseTable>();
234 for (FdbSqlParseTable table : tables) {
235 tableMap.put(table.getTableName(), table);
236 }
237 boolean first = true;
238 //处理条件
239 for (PermissionCondition condition : conditions) {
240 FdbSqlParseTable table = tableMap.get(condition.getTableName());
241 //解析出该表时才动态添加该条件 and
242 if (table != null) {
243 if (first) {
244 first = false;
245 } else {
246 ret.append(" AND ");
247 }
248 //条件
249 ret.append(condition.toWhere(table.getAlias()));
250 }
251 }
252 }
253 return ret.toString();
254 }
255
256 public BrokerNodeToString(List<PermissionCondition> conditions) {
257 this.conditions = conditions;
258 }
259
260 public List<FdbSqlParseTable> getTables() {
261 return tables;
262 }
263
264 public void setTables(List<FdbSqlParseTable> tables) {
265 this.tables = tables;
266 }
267
268 public List<PermissionCondition> getConditions() {
269 return conditions;
270 }
271
272 public void setConditions(List<PermissionCondition> conditions) {
273 this.conditions = conditions;
274 }
275
276 public boolean isInitCondition() {
277 return initCondition;
278 }
279
280 public void setInitCondition(boolean initCondition) {
281 this.initCondition = initCondition;
282 }
283
284 public String getWhere() {
285 return where;
286 }
287
288 public void setWhere(String where) {
289 this.where = where;
290 }
291 }
4.配置插件
1 <plugins>
2 <plugin interceptor="com.f.common.mybatis.PermissionsInterceptor">
3 </plugin>
4 </plugins>