查询时根据权限更改sql
import java.lang.reflect.Method; import org.apache.log4j.Logger; import org.springframework.aop.MethodBeforeAdvice; import org.springframework.jdbc.core.JdbcOperations; import org.springframework.web.context.ContextLoader; /** * * We construct data security layer,Generally speaking: * When the wms start, we detect the security metadata and put it into WMSSecurityContext * A request arrive,if there is an Annotation EnbableDataSewcurity upon the method,we register(mistake) method info into WMSSecurityContext * Before data access,we will check if the "data access session" need in an "security environment". * If yes,we alternative sql.then post to db. * please see detail information about alternative sql in class DataPermissionSqlUtil * * */ public class DataPermissionBeforeAdvice implements MethodBeforeAdvice { //logger for class DataPermissionBeforeAdvice private static final Logger logger = Logger.getLogger(DataPermissionBeforeAdvice.class); @Override public void before(Method method, Object[] args, Object target) throws Throwable { boolean shouldExecuteDataSecurity = checkIfNeedExecuteDataSecurity(); if (shouldExecuteDataSecurity) { executeDataSecurity(method, args, target, shouldExecuteDataSecurity); } } /** * check if the "session" need an security context. * */ private boolean checkIfNeedExecuteDataSecurity() { boolean shouldExecuteDataSecurity = false ; String dataSecurityMethodSignature = WMSSecurityContext.getDataSecurityMethodSignature().get(); if (dataSecurityMethodSignature != null) { // 确定调用方法拥有 @EnableDataSecurity注解标识 // web.xml文件中已经启用数据权限 String enableDataSecurity = ContextLoader.getCurrentWebApplicationContext().getServletContext().getInitParameter("EnableDataSecurity"); if (enableDataSecurity != null && "true".equals(enableDataSecurity)) { shouldExecuteDataSecurity = true; } } return shouldExecuteDataSecurity; } /** * retrieve security metadata ,and alternative sql(s). * */ @SuppressWarnings("rawtypes") private void executeDataSecurity(Method method, Object[] args, Object target, boolean shouldExecuteDataSecurity) throws Throwable { if (target instanceof JdbcOperations) { if (logger.isDebugEnabled()) { logger.debug("exeute data security begin."); } // do alternative sql operation if (shouldExecuteDataSecurity) { Class[] parameterTypes = method.getParameterTypes(); for (int i = 0; i < parameterTypes.length; i++) { Class paramterType = parameterTypes[i]; if ("String".equals(paramterType.getSimpleName())) { String sql = (String) args[i]; String sqlByDataPermission = DataPermissionSqlUtil.alternateSqlByDataPermission(sql); if (logger.isDebugEnabled()) { logger.debug("sql after data permission: " + sqlByDataPermission); } args[i] = sqlByDataPermission; } else if ("String[]".equals(paramterType.getSimpleName())) { String[] sqls = (String[]) args[i]; for (int j = 0; j < sqls.length; j++) { String sql = sqls[j]; String sqlByDataPermission = DataPermissionSqlUtil.alternateSqlByDataPermission(sql); sqls[j] = sqlByDataPermission; } args[i] = sqls; } } } if (logger.isDebugEnabled()) { logger.debug("exeute data security end."); } } } }
import java.io.StringReader; import java.util.List; import net.sf.jsqlparser.parser.CCJSqlParserManager; import net.sf.jsqlparser.statement.Statement; import net.sf.jsqlparser.statement.delete.Delete; import net.sf.jsqlparser.statement.insert.Insert; import net.sf.jsqlparser.statement.replace.Replace; import net.sf.jsqlparser.statement.select.Select; import net.sf.jsqlparser.statement.update.Update; import net.sf.jsqlparser.util.TablesNamesFinder; import org.apache.commons.lang3.StringUtils; import org.apache.log4j.Logger; /** * Handy class for alternative SQL with data authority and user context */ public class DataPermissionSqlUtil { //logger for class DataPermissionSqlUtil private static final Logger logger = Logger.getLogger(DataPermissionSqlUtil.class); private static CCJSqlParserManager pm = new CCJSqlParserManager(); /** * calculate subquery by table name * */ public static String alternateSqlByDataPermission(String sql) throws Exception { if (logger.isDebugEnabled()) { logger.debug("alternateSqlByDataPermission begin..."); } List<String> tableNames = getTableNames(sql); // if sql is not SELECT type // return sql directly if (null == tableNames) { return sql; } for (String tablename : tableNames) { // calculate alternative SQL String subquery = getSubqueryByTableName(tablename); if (logger.isDebugEnabled()) { logger.debug("table name is : " + tablename + " subquery is : " + subquery); } // replace table name to subquery sql = sql.replaceAll(tablename, subquery); // 只替换第一个表名（主表），关联表名 不替换 break; } return sql; } /** * detect table names from given table * ATTENTION : WE WILL SKIP SCALAR SUBQUERY IN PROJECTION CLAUSE * */ private static List<String> getTableNames(String sql) throws Exception { List<String> tablenames = null; TablesNamesFinder tablesNamesFinder = new TablesNamesFinder(); Statement statement = pm.parse(new StringReader(sql)); if (statement instanceof Select) { tablenames = tablesNamesFinder.getTableList((Select) statement); } else if (statement instanceof Update) { return null; } else if (statement instanceof Delete) { return null; } else if (statement instanceof Replace) { return null; } else if (statement instanceof Insert) { return null; } return tablenames; } private static String getSubqueryByTableName(String tableName) throws Exception { String subquery = null; String relatedFromClause = processFromConstriant(tableName); // 拼接关联表查询字段 控制权限 String experiyWhereClause = processExperiyConstriant(tableName); // 拼接Status字段控制启用 禁用，指定状态字段 String domainWhereClause = processDomainConstriant(tableName); // 根据员工登录 控制只能看到当前登录机构的数据，指定机构字段 String dataPermissionWhereClause = processDataPermissionConstriant(tableName); // 数据权限控制 if(StringUtils.isEmpty(experiyWhereClause) && StringUtils.isEmpty(domainWhereClause) && StringUtils.isEmpty(dataPermissionWhereClause)) { return tableName; } else { StringBuilder querySb = new StringBuilder() .append(" ( ").append(SqlConstants.SQL_SELECT).append(" ").append(DataSecurityHelper.getMainTableAlias(tableName)).append(" * ") .append(SqlConstants.SQL_FROM).append(" ").append(tableName).append(" "); if(StringUtils.isNotBlank(relatedFromClause)) { querySb.append(relatedFromClause).append(" "); } querySb.append(SqlConstants.SQL_WHERE).append(" "); if(StringUtils.isNotBlank(experiyWhereClause)) { querySb.append(experiyWhereClause).append(" ").append(SqlConstants.SQL_AND); } if(StringUtils.isNotBlank(domainWhereClause)) { querySb.append(domainWhereClause).append(" ").append(SqlConstants.SQL_AND); } if(StringUtils.isNotBlank(dataPermissionWhereClause)) { querySb.append(dataPermissionWhereClause).append(" ").append(SqlConstants.SQL_AND); } querySb = StringUtil.deleteLastSequence(querySb,SqlConstants.SQL_AND); querySb.append(" ) "); subquery = querySb.toString(); } return subquery; } /** * filter related from table column data * lifeng * */ private static String processFromConstriant(String tableName) { String relatedFromClause = DataSecurityHelper.buildRelatedConstriantSql(tableName); return relatedFromClause; } /** * filter expiry data * */ private static String processExperiyConstriant(String tableName) { String experiyWhereClause = DataSecurityHelper.buildExpiryConstriantSql(tableName); return experiyWhereClause; } /** * filter domain data * */ private static String processDomainConstriant(String tableName) { String domainWhereClause = DataSecurityHelper.buildDomainConstriantSql(tableName); return domainWhereClause; } /** * filter permission data * */ private static String processDataPermissionConstriant(String tableName) { String dataPermissionWhereClause = DataSecurityHelper.buildDataAuthorityConstriantSql(tableName); return dataPermissionWhereClause; } }
applicationContext-security.xml
<?xml version="1.0" encoding="UTF-8" ?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx" xmlns:context="http://www.springframework.org/schema/context" xmlns:mongo="http://www.springframework.org/schema/data/mongo" xmlns:mvc="http://www.springframework.org/schema/mvc" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.2.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.2.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.2.xsd http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.2.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.5.xsd http://www.springframework.org/schema/data/mongo http://www.springframework.org/schema/data/mongo/spring-mongo-1.0.xsd"> <mvc:annotation-driven /> <context:annotation-config /> <context:component-scan base-package="com.yundaex.wms" /> <!-- Base package to scan the mongo repositories, where we create de DAOS to access data and domain objects --> <mongo:repositories base-package="com.yundaex.wms.security.mongo" /> <bean id="dataPermissionBeforeAdvice" class="com.yundaex.common.security.advice.DataPermissionBeforeAdvice" /> <aop:config proxy-target-class="true"> <aop:pointcut expression="execution(public * org.springframework.jdbc.core.JdbcTemplate.*(..)) and !execution(public * org.springframework.jdbc.core.JdbcTemplate.queryForList(*))" id="jdbcTemplatePointcut"/> <aop:advisor advice-ref="dataPermissionBeforeAdvice" pointcut-ref="jdbcTemplatePointcut"/> </aop:config> <bean id="mongoRoleDao" class="com.yundaex.wms.security.mongo.impl.MongoRoleDaoImpl"> </bean> <bean id="mongoFunctionAuthorityDao" class="com.yundaex.wms.security.mongo.impl.MongoFunctionAuthorityDaoImpl"> </bean> <bean id="mongoUserDao" class="com.yundaex.wms.security.mongo.impl.MongoUserDaoImpl"></bean> </beans>
web.xml
<!-- Enable data security --> <context-param> <param-name>EnableDataSecurity</param-name> <param-value>true</param-value> </context-param>
