資料許可權技術驗證
資料許可權技術驗證.
資料許可權本質就是根據使用者的資訊,固定的拼接一些SQL
基於阿里開源的Druid的SQL Parse模組 ，做了一些動態拼接SQL的技術驗證.
參考:
http://mrchenatu.com/2017/01/19/druid-sql-parse/
資料許可權的配置在資料庫中建表
CREATE TABLE `dataprivilegeconfig` ( `Id` bigint(20) NOT NULL AUTO_INCREMENT, `Project` varchar(128) NOT NULL COMMENT '專案名稱', `TableName` varchar(128) NOT NULL COMMENT '表名稱', `ColName` varchar(128) NOT NULL COMMENT '列名稱', `Oper` varchar(8) NOT NULL COMMENT '操作.僅限於[>,<,=,>=,<=,!=,in,not in]', `Var` varchar(32) NOT NULL COMMENT '程式中定義的變數名稱', PRIMARY KEY (`Id`) ) ENGINE=InnoDB COMMENT='資料許可權配置表'
以上的配置,
id=1 的配置 希望在所有的nums表的id欄位 增加 小於等於的一個許可權.但是具體的值，需要程式動態的傳入.
id=2 的配置 在kylin_query_log的表的cube_id 增加一個 in的列表,這個列表的值需要動態傳入
id=3 的配置 在kylin_query_log的表的query_time欄位增加一個固定的時間,也就是所有的查詢,都限制在一個範圍內
依賴
<dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.1.10</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.37</version> </dependency>
SQLDataPrivilege.java 核心解析類
package org.datapriv.datapriv;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import com.alibaba.druid.sql.ast.SQLExpr;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.ast.expr.SQLBinaryOpExpr;
import com.alibaba.druid.sql.ast.expr.SQLBinaryOperator;
import com.alibaba.druid.sql.ast.expr.SQLQueryExpr;
import com.alibaba.druid.sql.ast.statement.SQLExprTableSource;
import com.alibaba.druid.sql.ast.statement.SQLJoinTableSource;
import com.alibaba.druid.sql.ast.statement.SQLSelect;
import com.alibaba.druid.sql.ast.statement.SQLSelectItem;
import com.alibaba.druid.sql.ast.statement.SQLSelectQuery;
import com.alibaba.druid.sql.ast.statement.SQLSelectQueryBlock;
import com.alibaba.druid.sql.ast.statement.SQLSelectStatement;
import com.alibaba.druid.sql.ast.statement.SQLSubqueryTableSource;
import com.alibaba.druid.sql.ast.statement.SQLTableSource;
import com.alibaba.druid.sql.ast.statement.SQLUnionQuery;
import com.alibaba.druid.sql.parser.SQLExprParser;
import com.alibaba.druid.sql.parser.SQLParserUtils;
import com.alibaba.druid.sql.parser.SQLStatementParser;
import com.alibaba.druid.util.JdbcUtils;
/**
* Hello world!
*
*/
public class SQLDataPrivilege {
public static void main(String[] args) {
UserPrivSession.set("nums.id", "3");
List<String> list = new ArrayList<String>();
list.add("0342e276-c046-40f0-be4a-1a04092bc7dd");
list.add("090493b4-aa59-4680-a707-907cb139ebd1");
list.add("098806d5-c07a-46c9-8901-e50045466455");
list.add("0bbfa1ed-9ec2-4e9e-9dfd-181945cb5eed");
list.add("0bd9528a-447b-4dc3-950a-4a45e066f22d");
UserPrivSession.set("kylin_query_log.cube_id", list, true);
SQLDataPrivilege a = new SQLDataPrivilege();
String sql = "select t4.dt,ifnull(t3.value,0) value from\r\n" +
" (\r\n" +
" select '2018-07-28'+interval (id-1) day dt from nums where id<=datediff('2018-08-03','2018-07-28')+1\r\n" +
" ) t4\r\n" +
" left join\r\n" +
" (\r\n" +
" select * from (\r\n" +
" select date_format(query_time,'%Y-%m-%d') dt,cast(sum(result) as SIGNED) value from kylin_result_cache_by_cubeid\r\n" +
" where\r\n" +
" module='query_total_by_cubeid' and\r\n" +
" cube_id in (select id from kylin_cube where is_delete=0) and\r\n" +
" \r\n" +
" query_time< date_format('2018-08-03'+interval 1 day,'%Y-%m-%d 00:00:00') and query_time>= date_format('2018-07-28','%Y-%m-%d 00:00:00')\r\n" +
" \r\n" +
" \r\n" +
" group by date_format(query_time,'%Y-%m-%d')\r\n" +
" ) t1 union all\r\n" +
"\r\n" +
" select date_format(query_time,'%Y-%m-%d') dt,count(*) value from kylin_query_log\r\n" +
" where\r\n" +
" cube_id in (select id from kylin_cube where is_delete=0) and\r\n" +
" \r\n" +
" query_time>=date_format(now() ,'%Y-%m-%d 00:00:00')\r\n" +
" and query_time<date_format(now()+interval 1 day ,'%Y-%m-%d 00:00:00')\r\n" +
" \r\n" +
" \r\n" +
" group by date_format(query_time,'%Y-%m-%d')\r\n" +
"\r\n" +
" ) t3\r\n" +
" on(t3.dt=t4.dt)\r\n" +
" where t4.dt between '2018-07-28' and '2018-08-03'\r\n" +
" order by t4.dt\r\n" +
"";
a.addPrivilege(sql, null);
}
//單例.該物件用於給已經存在的SQL增加資料許可權
private static SQLDataPrivilege INSTANCE = new SQLDataPrivilege();
public static SQLDataPrivilege getInstance() {
return INSTANCE;
}
//從資料庫中獲取配置資訊
private SQLDataPrivilege() {
try {
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "root", "laohuali@58");
PreparedStatement ps = con.prepareStatement("select * from DataPrivilegeConfig where Project='測試'");
ResultSet rs = ps.executeQuery();
while (rs.next()) {
Privilege p = new Privilege();
p.setTableName(rs.getString("TableName"));
p.setColName(rs.getString("ColName"));
p.setOper(rs.getString("Oper"));
p.setValue(rs.getString("Var"));
privList.add(p);
}
rs.close();
ps.close();
con.close();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
//儲存本專案的資料許可權配置資訊
private List<Privilege> privList = new ArrayList<Privilege>();
//在SQL上拼接資料許可權
public String addPrivilege(final String sql, Map<String, String> varMap) {
if (varMap == null) {
varMap = UserPrivSession.get();
}
// SQLParserUtils.createSQLStatementParser可以將sql裝載到Parser裡面
SQLStatementParser parser = SQLParserUtils.createSQLStatementParser(sql, JdbcUtils.MYSQL);
// parseStatementList的返回值SQLStatement本身就是druid裡面的語法樹物件
List<SQLStatement> stmtList = parser.parseStatementList();
SQLStatement stmt = stmtList.get(0);
//如果不是查詢,則返回
if (!(stmt instanceof SQLSelectStatement)) {
return sql;
}
SQLSelectStatement selectStmt = (SQLSelectStatement) stmt;
// 拿到SQLSelect 通過在這裡打斷點看物件我們可以看出這是一個樹的結構
SQLSelect sqlselect = selectStmt.getSelect();
SQLSelectQueryBlock query = (SQLSelectQueryBlock) sqlselect.getQuery();
parseSubQuery(query.getSelectList(), varMap);
parseTable(query, varMap);
System.out.println(sqlselect.toString());
return sqlselect.toString();
}
//給子查詢增加資料許可權
private void parseSubQuery(final List<SQLSelectItem> fieldList, final Map<String, String> varMap) {
for (SQLSelectItem item : fieldList) {
if (item.getExpr() instanceof SQLQueryExpr) {
SQLQueryExpr expr = (SQLQueryExpr) item.getExpr();
parseTable(expr.getSubQuery().getQueryBlock(), varMap);
}
}
}
//遞迴處理巢狀表
private void parseTable(final SQLSelectQueryBlock query, final Map<String, String> varMap) {
if (query == null) {
return;
}
SQLTableSource tableSource = query.getFrom();
if (tableSource instanceof SQLExprTableSource) {
//如果是普通的表,則在where中增加資料許可權
SQLExprTableSource table = ((SQLExprTableSource) tableSource);
String tableName = table.getName().getSimpleName();
String aliasName = table.getAlias();
SQLExpr sqlExpr = createSQLExpr(tableName, aliasName, varMap);
createWhereSQLExpr(query, varMap, sqlExpr);
} else if (tableSource instanceof SQLSubqueryTableSource) {
//如果是巢狀表,則遞迴到內層
SQLSubqueryTableSource table = ((SQLSubqueryTableSource) tableSource);
parseTable(table.getSelect().getQueryBlock(), varMap);
} else if (tableSource instanceof SQLJoinTableSource) {
//如果是兩個表關聯.則在on條件中增加資料許可權。並且在左右表中分別判斷是否是union all的情況
SQLJoinTableSource table = ((SQLJoinTableSource) tableSource);
SQLTableSource left = table.getLeft();
SQLTableSource right = table.getRight();
SQLExpr onExpr = table.getCondition();
if (left instanceof SQLSubqueryTableSource) {
SQLSubqueryTableSource leftTable = ((SQLSubqueryTableSource) left);
parseUnion(leftTable.getSelect().getQuery(), varMap);
parseTable(leftTable.getSelect().getQueryBlock(), varMap);
} else if (left instanceof SQLExprTableSource) {
SQLExprTableSource joinTable = ((SQLExprTableSource) left);
onExpr = createOnExpr(joinTable, onExpr, varMap);
}
if (right instanceof SQLSubqueryTableSource) {
SQLSubqueryTableSource rightTable = ((SQLSubqueryTableSource) right);
parseUnion(rightTable.getSelect().getQuery(), varMap);
parseTable(rightTable.getSelect().getQueryBlock(), varMap);
} else if (right instanceof SQLExprTableSource) {
SQLExprTableSource joinTable = ((SQLExprTableSource) right);
onExpr = createOnExpr(joinTable, onExpr, varMap);
}
table.setCondition(onExpr);
}
}
//如果是union all的情況,則通過遞迴進入內層
private boolean parseUnion(final SQLSelectQuery query, final Map<String, String> varMap) {
if (query instanceof SQLUnionQuery) {
SQLUnionQuery unionQuery = (SQLUnionQuery) query;
if (unionQuery.getLeft() instanceof SQLUnionQuery) {
parseUnion(unionQuery.getLeft(), varMap);
} else if (unionQuery.getLeft() instanceof SQLSelectQueryBlock) {
SQLSelectQueryBlock queryBlock = (SQLSelectQueryBlock) unionQuery.getLeft();
parseTable(queryBlock, varMap);
}
if (unionQuery.getRight() instanceof SQLUnionQuery) {
parseUnion(unionQuery.getRight(), varMap);
} else if (unionQuery.getRight() instanceof SQLSelectQueryBlock) {
SQLSelectQueryBlock queryBlock = (SQLSelectQueryBlock) unionQuery.getRight();
parseTable(queryBlock, varMap);
}
return true;
}
return false;
}
//在連線的on條件中拼接許可權
private SQLExpr createOnExpr(SQLExprTableSource joinTable, SQLExpr onExpr, final Map<String, String> varMap) {
String tableName = joinTable.getName().getSimpleName();
String aliasName = joinTable.getAlias();
SQLExpr sqlExpr = createSQLExpr(tableName, aliasName, varMap);
if (sqlExpr != null) {
SQLBinaryOpExpr newWhereExpr = new SQLBinaryOpExpr(onExpr, SQLBinaryOperator.BooleanAnd, sqlExpr);
onExpr = newWhereExpr;
}
return onExpr;
}
//根據配置獲取拼接好的許可權SQL
private SQLExpr createSQLExpr(String tableName, String aliasName, final Map<String, String> varMap) {
StringBuffer constraintsBuffer = new StringBuffer("");
for (Privilege p : privList) {
if (tableName.equals(p.getTableName())) {
constraintsBuffer.append(" and ");
constraintsBuffer.append(p.toString(aliasName, varMap));
}
}
if ("".equals(constraintsBuffer.toString())) {
return null;
}
SQLExprParser constraintsParser = SQLParserUtils
.createExprParser(constraintsBuffer.toString().replaceFirst(" and ", ""), JdbcUtils.MYSQL);
SQLExpr constraintsExpr = constraintsParser.expr();
return constraintsExpr;
}
//拼接where中的許可權資訊
private void createWhereSQLExpr(final SQLSelectQueryBlock query, final Map<String, String> varMap,
SQLExpr sqlExpr) {
if (sqlExpr == null) {
return;
}
SQLExpr whereExpr = query.getWhere();
// 修改where表示式
if (whereExpr == null) {
query.setWhere(sqlExpr);
} else {
SQLBinaryOpExpr newWhereExpr = new SQLBinaryOpExpr(whereExpr, SQLBinaryOperator.BooleanAnd, sqlExpr);
query.setWhere(newWhereExpr);
}
}
}
Privilege 儲存配置資訊的類
package org.datapriv.datapriv;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
public class Privilege {
private String tableName;
private String colName;
private String oper;
private String value;
private Pattern pattern = Pattern.compile("\\{.*\\}");
public String toString(String aliasName, Map<String, String> varMap) {
if (aliasName == null || "".equals(aliasName)) {
aliasName = tableName + ".";
} else {
aliasName = aliasName + ".";
}
String sqlString = aliasName + colName + " " + oper + " " + value;
Matcher m = pattern.matcher(value);
if (m.find()) {
String var = m.group().replaceAll("(\\{|\\})", "");
if (varMap.containsKey(var)) {
sqlString = sqlString.replaceAll("(\\{.*\\})", varMap.get(var));
} else {
System.out.println("缺少資訊");
throw new RuntimeException("缺少必要資訊");
}
}
return sqlString;
}
public String toString(Map<String, String> varMap) {
return toString(null, varMap);
}
@Override
public String toString() {
return tableName + "." + colName + " " + oper + " " + value;
}
public String getTableName() {
return tableName;
}
public void setTableName(String tableName) {
this.tableName = tableName;
}
public String getColName() {
return colName;
}
public void setColName(String colName) {
this.colName = colName;
}
public String getOper() {
return oper;
}
public void setOper(String oper) {
this.oper = oper;
}
public String getValue() {
return value;
}
public void setValue(String value) {
this.value = value;
}
}
UserPrivSession 通過ThreadLocal 傳引數的類
package org.datapriv.datapriv;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class UserPrivSession {
private static ThreadLocal<Map<String,String>> threadLocal=new ThreadLocal<Map<String,String>>();
public static Map<String,String> get(){
Map<String,String> map=threadLocal.get();
if(map==null) {
threadLocal.set(new HashMap<String,String>());
map=threadLocal.get();
}
return map;
}
public static void set(String key,String value) {
Map<String,String> map=get();
map.put(key, value);
}
public static void set(String key,List<String> list,boolean isVarchar) {
Map<String,String> map=get();
StringBuilder sb=new StringBuilder(128);
sb.append(" ( ");
for(String str:list) {
if(isVarchar) {
sb.append("'");
sb.append(str);
sb.append("',");
}else {
sb.append(str);
sb.append(",");
}
}
sb.deleteCharAt(sb.length()-1);
sb.append(" ) ");
map.put(key, sb.toString());
}
}
執行 SQLDataPrivilege 的main方法, 檢視測試結果.
可以看到已經把許可權資訊拼接成功.
具體應用場景.
將UserPrivSession 類作為Client給業務方
將SQLDataPrivilege和Privilege 作為獨立的jar包
通過javaagent和javassist 在啟動時改寫MySQL jdbc驅動的com.mysql.jdbc.ConnectionImpl 類
將SQL許可權拼接的功能加上
package org.datapriv.agent;
import java.io.IOException;
import java.lang.instrument.ClassDefinition;
import java.lang.instrument.Instrumentation;
import java.lang.instrument.UnmodifiableClassException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.datapriv.datapriv.UserPrivSession;
import javassist.CannotCompileException;
import javassist.ClassClassPath;
import javassist.ClassPool;
import javassist.CtClass;
import javassist.CtMethod;
import javassist.NotFoundException;
public class JavaAgent {
public static void premain(String agentArgs, Instrumentation inst) throws NotFoundException, CannotCompileException,
ClassNotFoundException, UnmodifiableClassException, IOException {
org.datapriv.datapriv.SQLDataPrivilege.getInstance();
ClassPool pool = ClassPool.getDefault();
pool.insertClassPath(new ClassClassPath(JavaAgent.class));
CtClass cc = pool.get("com.mysql.jdbc.ConnectionImpl");
CtClass[] paras = new CtClass[1];
paras[0] = pool.get("java.lang.String");
CtMethod method = cc.getDeclaredMethod("prepareStatement", paras);
StringBuffer sb = new StringBuffer();
sb.append("org.datapriv.datapriv.SQLDataPrivilege p= org.datapriv.datapriv.SQLDataPrivilege.getInstance();");
sb.append("sql=p.addPrivilege(sql,null);");
method.insertBefore(sb.toString());
ClassDefinition defException = new ClassDefinition(com.mysql.jdbc.ConnectionImpl.class, cc.toBytecode());
inst.redefineClasses(new ClassDefinition[] { defException });
}
public static void main(String[] args) throws ClassNotFoundException, SQLException {
UserPrivSession.set("deptName", "研發");
UserPrivSession.set("userid", "123456");
UserPrivSession.set("id", "10");
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "root", "laohuali@58");
PreparedStatement ps = con.prepareStatement("select * from nums limit 20 ");
ResultSet rs = ps.executeQuery();
while (rs.next()) {
System.out.println(rs.getInt(1));
}
rs.close();
ps.close();
con.close();
}
}
啟動命令增加javaagent引數，
其中DataPriv.jar
就包括
JavaAgent類
Privilege類
SQLDataPrivilege類
組成的Runnable jar包
（注意 不包括UserPrivSession 類，這個類需要整合在業務方）
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29254281/viewspace-2212990/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 資料許可權驗證MyBatis版MyBatis
- ylbtech-許可權管理-資料庫設計-功能許可權管理技術資料庫
- mysql之許可權驗證MySql
- 資料許可權
- 小知識：軟體開發的許可權控制和許可權驗證
- Vue 配合eiement動態路由，許可權驗證Vue路由
- [技術討論]資料許可權中的理論和實際
- sap許可權管理技術架構分析架構
- nodejs rbac 許可權驗證（匿名，普通，admin）NodeJS
- ASP.NET MVC許可權驗證 封裝類ASP.NETMVC封裝
- MySQL資料庫許可權體系入門(5)---管理資料庫許可權MySql資料庫
- [資料庫]MYSQL之授予/查驗binlog許可權資料庫MySql
- Hadoop 許可權認證Hadoop
- spring boot 利用註解實現許可權驗證Spring Boot
- 資料分析的許可權控制
- 資料庫的許可權管理資料庫
- 許可權+訂單資料模型模型
- 資料安全之許可權控制
- Oracle 使用者許可權管理與常用許可權資料字典列表Oracle
- spring security許可權認證Spring
- 基於全流量許可權漏洞檢測技術
- Windows 反除錯技術——OpenProcess 許可權過濾Windows除錯
- 【專案實踐】一文帶你搞定頁面許可權、按鈕許可權以及資料許可權
- SpringSecurity許可權管理系統實戰—九、資料許可權的配置SpringGse
- MySQL資料庫許可權體系入門(4)---管理全域性許可權MySql資料庫
- 查詢沒有許可權但資料字典中顯示有許可權
- 往hdfs寫資料無許可權
- 資料庫學習：許可權管理資料庫
- Mysql資料庫許可權問題MySql資料庫
- sap資料--sap許可權解剖（一）
- sap資料--sap許可權解剖（二）
- EBS 臨時資料夾許可權
- 修改檔案、資料夾許可權
- Oracle的物件許可權、角色許可權、系統許可權Oracle物件
- 認證/授權與許可權的問題
- 許可權之選單許可權
- [Abp vNext 原始碼分析] - 7. 許可權與驗證原始碼
- 一文讀懂k8s rbac 許可權驗證K8S