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.engine.define.TDBResultCodeDefine;
  25 import com.hangum.tadpole.engine.query.dao.mysql.ProcedureFunctionDAO;
  26 import com.hangum.tadpole.engine.query.dao.mysql.TableColumnDAO;
  27 import com.hangum.tadpole.engine.query.dao.mysql.TableDAO;
  28 import com.hangum.tadpole.engine.query.dao.system.UserDBDAO;
  29 import com.hangum.tadpole.engine.query.dao.system.accesscontrol.AccessCtlObjectDAO;
  30 import com.hangum.tadpole.engine.restful.TadpoleException;
  31 import com.hangum.tadpole.engine.sql.parser.define.ParserDefine;
  32 import com.hangum.tadpole.engine.sql.util.SQLUtil;
  33
  34 import net.sf.jsqlparser.parser.CCJSqlParserManager;
  35
  36 /**
  37  * DBAccess Control manager
  38  *
  39  * @author hangum
  40  * @version 1.6.1
  41  * @since 2015. 4. 2.
  42  *
  43  */
  44 public class DBAccessCtlManager {
  45         private static final Logger logger = Logger.getLogger(DBAccessCtlManager.class);
  46         private static DBAccessCtlManager instance = null;
  47
  48         /**
  49          *
  50          */
  51         private DBAccessCtlManager() {}
  52
  53         public static DBAccessCtlManager getInstance() {
  54                 if(instance == null) {
  55                         instance = new DBAccessCtlManager();
  56                 }
  57
  58                 return instance;
  59         }
  60
  61         /**
  62          * table filter test
  63          *
  64          * @param userDB
  65          * @param strSQL
  66          * @throws Exception
  67          */
  68         public void tableFilterTest(final UserDBDAO userDB, final String strSQL) throws TadpoleException {
  69                 if(userDB.getDbAccessCtl().getAllAccessCtl().isEmpty()) return;
  70                 if(logger.isDebugEnabled()) logger.debug("####################### SQL: " + strSQL);
  71
  72                 CCJSqlParserManager pm = new CCJSqlParserManager();
  73                 net.sf.jsqlparser.statement.Statement statement = null;
  74                 try {
  75                         statement = pm.parse(new StringReader(strSQL));
  76                 } catch(Exception e) {
  77                         logger.error("SQL Parsing exception", e);
  78
  79 //                      if(logger.isDebugEnabled()) {
  80                                 logger.debug("#########################################################");
  81                                 logger.error(String.format("==[sql parsing exception]===DB : %s, SQL: %s", userDB.getDbms_type(), strSQL));
  82                                 logger.debug("#########################################################");
  83 //                      }
  84                 }
  85
  86                 //
  87                 // statement 가 에러이면 어떻게 하지?
  88                 //              쿼리 파싱이 잘 못 되었으면...
  89                 //
  90                 //
  91                 boolean isClearTable            = true;
  92                 String strMsgTable                      = "";
  93 //              boolean isClearProcedure        = true;
  94 //              String strMsgProcedure          = "";
  95                 boolean isClearFunction         = true;
  96                 String strMsgFunction           = "";
  97
  98                 if(statement == null) {
  99                         // MySQL procedure 호출 인지 검사합니다. (CALL 명령)
 100                         String strOriSQL = SQLUtil.removeComment(strSQL);
 101                         Matcher matcher = Pattern.compile("CALL\\s+([A-Z0-9_\\.\"'`]+)", ParserDefine.PATTERN_FLAG).matcher(strOriSQL);
 102                         if(matcher.find()) {
 103                                 if(logger.isDebugEnabled()) {
 104                                         logger.debug("#########################################################");
 105                                         logger.debug("=-------=>>> " + matcher.group(1));
 106                                         logger.debug("#########################################################");
 107                                 }
 108                                 String procedureName = matcher.group(1);
 109
 110 //                              // =========[procedure]=====================================================================================================================================================
 111                                 final Map<String, AccessCtlObjectDAO> mapProcedureAccessCtl = userDB.getDbAccessCtl().getMapSelectProcedureAccessCtl();
 112                                 if(!mapProcedureAccessCtl.isEmpty()) {
 113                                         final AccessCtlObjectDAO accCtlObj = (AccessCtlObjectDAO)mapProcedureAccessCtl.get(""+mapProcedureAccessCtl.keySet().toArray()[0]);
 114                                         final String strFilterType = accCtlObj.getFilter_type();
 115
 116                                         if(logger.isDebugEnabled()) logger.debug("### Table filter test ####### SQL: " + strSQL);
 117                                         if(PublicTadpoleDefine.FILTER_TYPE.EXCLUDE.name().equals(strFilterType)) {
 118                                 String fullName = StringUtils.contains(procedureName, ".")?procedureName:userDB.getSchema() + "." + procedureName;
 119
 120                                 if(logger.isDebugEnabled()) logger.debug("\t#### parse procedure " + fullName);
 121                                 if(mapProcedureAccessCtl.containsKey(fullName)) {
 122                                         if(logger.isDebugEnabled()) logger.debug(String.format("\t type: %s, table: %s 있습니다.", PublicTadpoleDefine.FILTER_TYPE.EXCLUDE.name(), fullName));
 123                                                 throw new TadpoleException(TDBResultCodeDefine.FORBIDDEN, String.format("Procedure: %s은 접근 할 수 없습니다.", fullName));
 124                                 }
 125                                         } else {
 126                                                 String fullName = StringUtils.contains(procedureName, ".")?procedureName:userDB.getSchema() + "." + procedureName;
 127
 128                                 if(logger.isDebugEnabled()) logger.debug("\t#### parse procedure " + fullName);
 129                                 if(!mapProcedureAccessCtl.containsKey(fullName)) {
 130                                         throw new TadpoleException(TDBResultCodeDefine.FORBIDDEN, String.format("Procedure: %s은 접근 할 수 없습니다.", fullName));
 131                                 }
 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 }