com.hangum.tadpole.commons.sql/src/com/hangum/tadpole/engine/security/DBAccessCtlManager.java

   1 /*******************************************************************************
   2  * Copyright (c) 2012 - 2015 hangum.
   3  * All rights reserved. This program and the accompanying materials
   4  * are made available under the terms of the GNU Lesser Public License v2.1
   5  * which accompanies this distribution, and is available at
   6  * http://www.gnu.org/licenses/old-licenses/gpl-2.0.html
   7  *
   8  * Contributors:
   9  *     hangum - initial API and implementation
  10  ******************************************************************************/
  11 package com.hangum.tadpole.engine.security;
  12
  13 import java.io.StringReader;
  14 import java.util.ArrayList;
  15 import java.util.List;
  16 import java.util.Map;
  17 import java.util.regex.Matcher;
  18 import java.util.regex.Pattern;
  19
  20 import org.apache.commons.lang.StringUtils;
  21 import org.apache.log4j.Logger;
  22
  23 import com.hangum.tadpole.commons.libs.core.define.PublicTadpoleDefine;
  24 import com.hangum.tadpole.commons.sql.core.manager.TDBObjectParser;
  25 import com.hangum.tadpole.engine.define.TDBResultCodeDefine;
  26 import com.hangum.tadpole.engine.query.dao.mysql.ProcedureFunctionDAO;
  27 import com.hangum.tadpole.engine.query.dao.mysql.TableColumnDAO;
  28 import com.hangum.tadpole.engine.query.dao.mysql.TableDAO;
  29 import com.hangum.tadpole.engine.query.dao.system.UserDBDAO;
  30 import com.hangum.tadpole.engine.query.dao.system.accesscontrol.AccessCtlObjectDAO;
  31 import com.hangum.tadpole.engine.restful.TadpoleException;
  32 import com.hangum.tadpole.engine.sql.parser.define.ParserDefine;
  33 import com.hangum.tadpole.engine.sql.util.SQLUtil;
  34
  35 import net.sf.jsqlparser.parser.CCJSqlParserManager;
  36
  37 /**
  38  * DBAccess Control manager
  39  *
  40  * @author hangum
  41  * @version 1.6.1
  42  * @since 2015. 4. 2.
  43  *
  44  */
  45 public class DBAccessCtlManager {
  46         private static final Logger logger = Logger.getLogger(DBAccessCtlManager.class);
  47         private static DBAccessCtlManager instance = null;
  48
  49         /**
  50          *
  51          */
  52         private DBAccessCtlManager() {}
  53
  54         public static DBAccessCtlManager getInstance() {
  55                 if(instance == null) {
  56                         instance = new DBAccessCtlManager();
  57                 }
  58
  59                 return instance;
  60         }
  61
  62         /**
  63          * table filter test
  64          *
  65          * @param userDB
  66          * @param strSQL
  67          * @throws Exception
  68          */
  69         public void tableFilterTest(final UserDBDAO userDB, final String strSQL) throws TadpoleException {
  70                 if(userDB.getDbAccessCtl().getAllAccessCtl().isEmpty()) return;
  71                 if(logger.isDebugEnabled()) logger.debug("####################### SQL: " + strSQL);
  72
  73                 CCJSqlParserManager pm = new CCJSqlParserManager();
  74                 net.sf.jsqlparser.statement.Statement statement = null;
  75                 try {
  76                         statement = pm.parse(new StringReader(strSQL));
  77                 } catch(Exception e) {
  78                         logger.error("SQL Parsing exception", e);
  79
  80 //                      if(logger.isDebugEnabled()) {
  81                                 logger.debug("#########################################################");
  82                                 logger.error(String.format("==[sql parsing exception]===DB : %s, SQL: %s", userDB.getDbms_type(), strSQL));
  83                                 logger.debug("#########################################################");
  84 //                      }
  85                 }
  86
  87                 //
  88                 // statement 가 에러이면 어떻게 하지?
  89                 //              쿼리 파싱이 잘 못 되었으면...
  90                 //
  91                 //
  92                 boolean isClearTable            = true;
  93                 String strMsgTable                      = "";
  94 //              boolean isClearProcedure        = true;
  95 //              String strMsgProcedure          = "";
  96                 boolean isClearFunction         = true;
  97                 String strMsgFunction           = "";
  98
  99                 if(statement == null) {
 100                         // MySQL procedure 호출 인지 검사합니다. (CALL 명령)
 101                         String strOriSQL = SQLUtil.removeComment(strSQL);
 102                         Matcher matcher = Pattern.compile("CALL\\s+([A-Z0-9_\\.\"'`]+)", ParserDefine.PATTERN_FLAG).matcher(strOriSQL);
 103                         if(matcher.find()) {
 104                                 if(logger.isDebugEnabled()) {
 105                                         logger.debug("#########################################################");
 106                                         logger.debug("=-------=>>> " + matcher.group(1));
 107                                         logger.debug("#########################################################");
 108                                 }
 109                                 String procedureName = matcher.group(1);
 110
 111 //                              // =========[procedure]=====================================================================================================================================================
 112                                 final Map<String, AccessCtlObjectDAO> mapProcedureAccessCtl = userDB.getDbAccessCtl().getMapSelectProcedureAccessCtl();
 113                                 if(!mapProcedureAccessCtl.isEmpty()) {
 114                                         final AccessCtlObjectDAO accCtlObj = (AccessCtlObjectDAO)mapProcedureAccessCtl.get(""+mapProcedureAccessCtl.keySet().toArray()[0]);
 115                                         final String strFilterType = accCtlObj.getFilter_type();
 116
 117                                         if(logger.isDebugEnabled()) logger.debug("### Table filter test ####### SQL: " + strSQL);
 118                                         if(PublicTadpoleDefine.FILTER_TYPE.EXCLUDE.name().equals(strFilterType)) {
 119                                 String fullName = StringUtils.contains(procedureName, ".")?procedureName:userDB.getSchema() + "." + procedureName;
 120
 121                                 if(logger.isDebugEnabled()) logger.debug("\t#### parse procedure " + fullName);
 122                                 if(mapProcedureAccessCtl.containsKey(fullName)) {
 123                                         if(logger.isDebugEnabled()) logger.debug(String.format("\t type: %s, table: %s 있습니다.", PublicTadpoleDefine.FILTER_TYPE.EXCLUDE.name(), fullName));
 124                                                 throw new TadpoleException(TDBResultCodeDefine.FORBIDDEN, String.format("Procedure: %s은 접근 할 수 없습니다.", fullName));
 125                                 }
 126                                         } else {
 127                                                 String fullName = StringUtils.contains(procedureName, ".")?procedureName:userDB.getSchema() + "." + procedureName;
 128
 129                                 if(logger.isDebugEnabled()) logger.debug("\t#### parse procedure " + fullName);
 130                                 if(!mapProcedureAccessCtl.containsKey(fullName)) {
 131                                         throw new TadpoleException(TDBResultCodeDefine.FORBIDDEN, String.format("Procedure: %s은 접근 할 수 없습니다.", fullName));
 132                                 }
 133                                         }
 134                                 }
 135
 136                         }
 137                 } else {
 138
 139                         final TDBObjectParser tableFunctionFinder = new TDBObjectParser();
 140                         final List<String> _listTables = tableFunctionFinder.getTableList(statement);
 141                         final List<String> _listFunctionProcedure = tableFunctionFinder.getFunctionProcedureList();
 142
 143                         final Map<String, AccessCtlObjectDAO> mapTableAccessCtl = userDB.getDbAccessCtl().getMapSelectTableAccessCtl();
 144                         if(!mapTableAccessCtl.isEmpty()) {
 145                                 final AccessCtlObjectDAO accCtlObj = (AccessCtlObjectDAO)mapTableAccessCtl.get(""+mapTableAccessCtl.keySet().toArray()[0]);
 146                                 final String strFilterType = accCtlObj.getFilter_type();
 147
 148                                 List<String> _listObject = new ArrayList<String>();
 149                                 boolean isFindTable = true;
 150                                 if(PublicTadpoleDefine.FILTER_TYPE.EXCLUDE.name().equals(strFilterType)) {
 151                                         for(String tableName : _listTables) {
 152                                 String fullName = StringUtils.contains(tableName, ".")?tableName:userDB.getSchema() + "." + tableName;
 153
 154                                 if(logger.isDebugEnabled()) logger.debug("\t#### parse tableName " + fullName);
 155                                 if(mapTableAccessCtl.containsKey(fullName)) {
 156                                         _listObject.add(fullName);
 157                                         if(logger.isDebugEnabled()) logger.debug(String.format("\t type: %s, table: %s 있습니다.", PublicTadpoleDefine.FILTER_TYPE.EXCLUDE.name(), fullName));
 158                                         isFindTable = false;
 159                                 }
 160                             }
 161                                 } else {
 162                                         for(String tableName : _listTables) {
 163                                 String fullName = StringUtils.contains(tableName, ".")?tableName:userDB.getSchema() + "." + tableName;
 164
 165                                 if(logger.isDebugEnabled()) logger.debug("\t#### parse tableName " + fullName);
 166                                 if(!mapTableAccessCtl.containsKey(fullName)) {
 167                                         _listObject.add(fullName);
 168                                         if(logger.isDebugEnabled()) logger.debug(String.format("\t type: %s, table: %s 있습니다.", PublicTadpoleDefine.FILTER_TYPE.INCLUDE.name(), fullName));
 169                                         isFindTable = false;
 170                                 }
 171                             }
 172                                 }
 173
 174                                 if(!isFindTable) {
 175                                         isClearTable = false;
 176                             strMsgTable = String.format("Table: %s은 접근 할 수 없습니다.\n", StringUtils.join(_listObject.toArray(), ", " ));
 177                                 }
 178                         }
 179
 180                         // =========[function]=====================================================================================================================================================
 181                         final Map<String, AccessCtlObjectDAO> mapFunctionAccessCtl = userDB.getDbAccessCtl().getMapSelectFunctionAccessCtl();
 182                         if(!mapFunctionAccessCtl.isEmpty()) {
 183                                 final AccessCtlObjectDAO accCtlObj = (AccessCtlObjectDAO)mapFunctionAccessCtl.get(""+mapFunctionAccessCtl.keySet().toArray()[0]);
 184                                 final String strFilterType = accCtlObj.getFilter_type();
 185
 186                                 List<String> _listObject = new ArrayList<String>();
 187                                 boolean isFindTable = true;
 188                                 if(PublicTadpoleDefine.FILTER_TYPE.EXCLUDE.name().equals(strFilterType)) {
 189                                         for(String procedureName : _listFunctionProcedure) {
 190                                 String fullName = StringUtils.contains(procedureName, ".")?procedureName:userDB.getSchema() + "." + procedureName;
 191
 192                                 if(logger.isDebugEnabled()) logger.debug("\t#### parse function " + fullName);
 193                                 if(mapFunctionAccessCtl.containsKey(fullName)) {
 194                                         _listObject.add(fullName);
 195                                         if(logger.isDebugEnabled()) logger.debug(String.format("\t type: %s, table: %s 있습니다.", PublicTadpoleDefine.FILTER_TYPE.EXCLUDE.name(), fullName));
 196                                         isFindTable = false;
 197                                 }
 198                             }
 199                                 } else {
 200                                         for(String procedureName : _listFunctionProcedure) {
 201                                 String fullName = StringUtils.contains(procedureName, ".")?procedureName:userDB.getSchema() + "." + procedureName;
 202
 203                                 if(logger.isDebugEnabled()) logger.debug("\t#### parse function " + fullName);
 204                                 if(!mapFunctionAccessCtl.containsKey(fullName)) {
 205                                         _listObject.add(fullName);
 206                                         if(logger.isDebugEnabled()) logger.debug(String.format("\t type: %s, table: %s 있습니다.", PublicTadpoleDefine.FILTER_TYPE.INCLUDE.name(), fullName));
 207                                         isFindTable = false;
 208                                 }
 209                             }
 210                                 }
 211
 212                                 if(!isFindTable) {
 213                                         isClearFunction = false;
 214                         strMsgFunction = String.format("Function: %s은 접근 할 수 없습니다.\n", StringUtils.join(_listObject.toArray(), ", " ));
 215                                 }
 216                         }
 217                 }
 218
 219                 if(!isClearTable || !isClearFunction) {
 220                         throw new TadpoleException(TDBResultCodeDefine.FORBIDDEN, String.format("%s%s", strMsgTable, strMsgFunction));
 221                 }
 222         }
 223
 224         /**
 225          * table filter
 226          * @param showTables
 227          * @param userDB
 228          * @return
 229          */
 230         public List<TableDAO> getTableFilter(List<TableDAO> showTables, UserDBDAO userDB) {
 231                 if(userDB.getDbAccessCtl().getMapSelectTableAccessCtl().isEmpty()) return showTables;
 232
 233                 List<TableDAO> returnTables = new ArrayList<TableDAO>();
 234
 235                 Map<String, AccessCtlObjectDAO> mapSelectAccessCtl = userDB.getDbAccessCtl().getMapSelectTableAccessCtl();
 236
 237                 AccessCtlObjectDAO accCtlObj = (AccessCtlObjectDAO)mapSelectAccessCtl.get(""+mapSelectAccessCtl.keySet().toArray()[0]);
 238                 String strFilterType = accCtlObj.getFilter_type();
 239                 if(PublicTadpoleDefine.FILTER_TYPE.EXCLUDE.name().equals(strFilterType)) {
 240                         returnTables.addAll(showTables);
 241                         for (TableDAO tableDAO : showTables) {
 242                                 if(mapSelectAccessCtl.containsKey(tableDAO.getSchema_name() + "." + tableDAO.getName())) {
 243                                         boolean bool = returnTables.remove(tableDAO);
 244                                         if(logger.isDebugEnabled()) logger.debug(tableDAO.getName() + " object removed.");
 245                                 }
 246                         }
 247
 248                 } else {
 249                         for (TableDAO tableDAO : showTables) {
 250                                 if(logger.isDebugEnabled()) logger.debug("===> " + tableDAO.getSchema_name() + "." + tableDAO.getName());
 251                                 if(mapSelectAccessCtl.containsKey(tableDAO.getSchema_name() + "." + tableDAO.getName())) {
 252                                         boolean bool = returnTables.add(tableDAO);
 253                                         if(logger.isDebugEnabled()) logger.debug(tableDAO.getName() + " add object.");
 254                                 }
 255                         }
 256                 }
 257
 258                 return returnTables;
 259         }
 260
 261         /**
 262          * function filter
 263          *
 264          * @param listFunction
 265          * @param userDB
 266          * @return
 267          */
 268         public List<ProcedureFunctionDAO> getFunctionFilter(List<ProcedureFunctionDAO> listFunction, UserDBDAO userDB) {
 269                 if(userDB.getDbAccessCtl().getMapSelectFunctionAccessCtl().isEmpty()) return listFunction;
 270
 271                 List<ProcedureFunctionDAO> returnTables = new ArrayList<ProcedureFunctionDAO>();
 272
 273                 Map<String, AccessCtlObjectDAO> mapSelectAccessCtl = userDB.getDbAccessCtl().getMapSelectFunctionAccessCtl();
 274                 AccessCtlObjectDAO accCtlObj = (AccessCtlObjectDAO)mapSelectAccessCtl.get(""+mapSelectAccessCtl.keySet().toArray()[0]);
 275                 String strFilterType = accCtlObj.getFilter_type();
 276                 if(PublicTadpoleDefine.FILTER_TYPE.EXCLUDE.name().equals(strFilterType)) {
 277                         returnTables.addAll(listFunction);
 278                         for (ProcedureFunctionDAO funcDAO : listFunction) {
 279                                 if(mapSelectAccessCtl.containsKey(funcDAO.getSchema_name() + "." + funcDAO.getName())) {
 280                                         boolean bool = returnTables.remove(funcDAO);
 281                                         if(logger.isDebugEnabled()) logger.debug(funcDAO.getName() + " object removed.");
 282                                 }
 283                         }
 284
 285                 } else {
 286                         for (ProcedureFunctionDAO funcDAO : listFunction) {
 287                                 if(mapSelectAccessCtl.containsKey(funcDAO.getSchema_name() + "." + funcDAO.getName())) {
 288                                         boolean bool = returnTables.add(funcDAO);
 289                                         if(logger.isDebugEnabled()) logger.debug(funcDAO.getName() + " add object.");
 290                                 }
 291                         }
 292                 }
 293
 294                 return returnTables;
 295         }
 296
 297         /**
 298          * procedure filter
 299          *
 300          * @param listProcedure
 301          * @param userDB
 302          * @return
 303          */
 304         public List<ProcedureFunctionDAO> getProcedureFilter(List<ProcedureFunctionDAO> listProcedure, UserDBDAO userDB) {
 305                 if(userDB.getDbAccessCtl().getMapSelectFunctionAccessCtl().isEmpty()) return listProcedure;
 306
 307                 List<ProcedureFunctionDAO> returnTables = new ArrayList<ProcedureFunctionDAO>();
 308
 309                 Map<String, AccessCtlObjectDAO> mapSelectAccessCtl = userDB.getDbAccessCtl().getMapSelectProcedureAccessCtl();
 310                 AccessCtlObjectDAO accCtlObj = (AccessCtlObjectDAO)mapSelectAccessCtl.get(""+mapSelectAccessCtl.keySet().toArray()[0]);
 311                 String strFilterType = accCtlObj.getFilter_type();
 312                 if(PublicTadpoleDefine.FILTER_TYPE.EXCLUDE.name().equals(strFilterType)) {
 313                         returnTables.addAll(listProcedure);
 314                         for (ProcedureFunctionDAO procDAO : listProcedure) {
 315                                 if(mapSelectAccessCtl.containsKey(procDAO.getSchema_name() + "." + procDAO.getName())) {
 316                                         boolean bool = returnTables.remove(procDAO);
 317                                         if(logger.isDebugEnabled()) logger.debug(procDAO.getName() + " object removed.");
 318                                 }
 319                         }
 320
 321                 } else {
 322                         for (ProcedureFunctionDAO procDAO : listProcedure) {
 323                                 if(mapSelectAccessCtl.containsKey(procDAO.getSchema_name() + "." + procDAO.getName())) {
 324                                         boolean bool = returnTables.add(procDAO);
 325                                         if(logger.isDebugEnabled()) logger.debug(procDAO.getName() + " add object.");
 326                                 }
 327                         }
 328                 }
 329
 330                 return returnTables;
 331         }
 332
 333         /**
 334          * get column filter
 335          *
 336          * @param strTableName
 337          * @param listTableColumns
 338          * @param userDB
 339          * @return
 340          */
 341         public List<TableColumnDAO> getColumnFilter(TableDAO tableDao, List<TableColumnDAO> listTableColumns, UserDBDAO userDB) {
 342                 return listTableColumns;
 343 //              if(userDB.getDbAccessCtl().getMapSelectAccessCtl().isEmpty()) return listTableColumns;
 344 //
 345 //              List<TableColumnDAO> returnColumns = new ArrayList<TableColumnDAO>();
 346 //              returnColumns.addAll(listTableColumns);
 347 //              String strTableName = "";
 348 //              if(DBGroupDefine.SQLITE_GROUP == userDB.getDBGroup()) strTableName = tableDao.getSysName();
 349 //              else                                                                                            strTableName = tableDao.getName();
 350 //
 351 //              // db access control
 352 //              Map<String, AccessCtlObjectDAO> mapSelectAccessCtl = userDB.getDbAccessCtl().getMapSelectAccessCtl();
 353 //              if(mapSelectAccessCtl.containsKey(strTableName)) {
 354 //                      AccessCtlObjectDAO accessCtlObjectDao = mapSelectAccessCtl.get(strTableName);
 355 //
 356 //                      for (TableColumnDAO tableColumnDAO : listTableColumns) {
 357 //                              if(StringUtils.containsIgnoreCase(accessCtlObjectDao.getDetail_obj(), tableColumnDAO.getField())) {
 358 //                                      returnColumns.remove(tableColumnDAO);
 359 //                              }
 360 //                      }
 361 //              }
 362
 363 //              return returnColumns;
 364         }
 365
 366 }