Merge "Fix sql injection vulnerability"
authorSunder Tattavarada <statta@research.att.com>
Tue, 18 Jun 2019 16:04:36 +0000 (16:04 +0000)
committerGerrit Code Review <gerrit@onap.org>
Tue, 18 Jun 2019 16:04:36 +0000 (16:04 +0000)
1  2 
ecomp-portal-BE-common/src/main/java/org/onap/portalapp/portal/service/UserRolesCommonServiceImpl.java
ecomp-portal-BE-common/src/test/java/org/onap/portalapp/portal/service/UserRolesCommonServiceImplTest.java

@@@ -2,7 -2,7 +2,7 @@@
   * ============LICENSE_START==========================================
   * ONAP Portal
   * ===================================================================
 - * Copyright (C) 2017-2018 AT&T Intellectual Property. All rights reserved.
 + * Copyright (C) 2019 AT&T Intellectual Property. All rights reserved.
   * ===================================================================
   *
   * Unless otherwise specified, all software contained herein is licensed
@@@ -114,7 -114,6 +114,7 @@@ import org.springframework.http.HttpHea
  import org.springframework.http.HttpMethod;
  import org.springframework.http.HttpStatus;
  import org.springframework.http.ResponseEntity;
 +import org.springframework.web.client.HttpClientErrorException;
  import org.springframework.web.client.RestTemplate;
  
  import com.fasterxml.jackson.core.JsonProcessingException;
@@@ -177,7 -176,7 +177,7 @@@ public class UserRolesCommonServiceImp
         * 
         * @param userId
         */
 -      protected void createLocalUserIfNecessary(String userId) {
 +      protected void createLocalUserIfNecessary(String userId,boolean isSystemUser) {
                if (StringUtils.isEmpty(userId)) {
                        logger.error(EELFLoggerDelegate.errorLogger, "createLocalUserIfNecessary : empty userId!");
                        return;
                        transaction = localSession.beginTransaction();
                        @SuppressWarnings("unchecked")
                        List<EPUser> userList = localSession
 -                                      .createQuery("from " + EPUser.class.getName() + " where orgUserId='" + userId + "'").list();
 +                                      .createQuery("from :name where orgUserId=:userId")
 +                                      .setParameter("name",EPUser.class.getName())
 +                                      .setParameter("userId",userId)
 +                                      .list();
                        if (userList.size() == 0) {
 -                              EPUser client = searchService.searchUserByUserId(userId);
 +                              EPUser client = null;
 +                              if (!isSystemUser) {
 +                                      client = searchService.searchUserByUserId(userId);
 +                              } else {
 +                                      client = new EPUser();
 +                                      client.setOrgUserId(userId);
 +                                      client.setSystemUser(true);
 +                                      client.setFirstName(userId.substring(0,userId.indexOf("@")));
 +                              }
                                if (client == null) {
                                        String msg = "createLocalUserIfNecessary: cannot create user " + userId
                                                        + ", because not found in phonebook";
                        transaction = localSession.beginTransaction();
                        // Attention! All roles from remote application supposed to be
                        // active!
 +
                        @SuppressWarnings("unchecked")
 -                      List<EPRole> currentAppRoles = localSession
 -                                      .createQuery("from " + EPRole.class.getName() + " where appId=" + appId).list();
 +                      List<EPRole> currentAppRoles = localSession.createQuery("from :name where appId = :appId")
 +                                      .setParameter("name",EPRole.class.getName())
 +                                      .setParameter("appId",appId)
 +                                      .list();
 +
                        List<EPRole> obsoleteRoles = new ArrayList<EPRole>();
                        for (int i = 0; i < currentAppRoles.size(); i++) {
                                EPRole oldAppRole = currentAppRoles.get(i);
                                        // Delete from fn_user_role
                                        @SuppressWarnings("unchecked")
                                        List<EPUserApp> userRoles = localSession.createQuery(
 -                                                      "from " + EPUserApp.class.getName() + " where app.id=" + appId + " and role_id=" + roleId)
 +                                                      "from :name where app.id=:appId and role_id=:roleId")
 +                                                      .setParameter("name",EPUserApp.class.getName())
 +                                                      .setParameter("appId",appId)
 +                                                      .setParameter("roleId",roleId)
                                                        .list();
  
                                        logger.debug(EELFLoggerDelegate.debugLogger, "syncAppRoles: number of userRoles to delete: " + userRoles.size());
                                        // Delete from fn_menu_functional_roles
                                        @SuppressWarnings("unchecked")
                                        List<FunctionalMenuRole> funcMenuRoles = localSession
-                                                       .createQuery("from " + FunctionalMenuRole.class.getName() + " where roleId=" + roleId)
+                                                       .createQuery("from :name where roleId=:roleId")
+                                                       .setParameter("name",FunctionalMenuRole.class.getName())
+                                                       .setParameter("roleId",roleId)
                                                        .list();
                                        int numMenuRoles = funcMenuRoles.size();
                                        logger.debug(EELFLoggerDelegate.debugLogger,
                                                // so must null out the url too, to be consistent
                                                @SuppressWarnings("unchecked")
                                                List<FunctionalMenuRole> funcMenuRoles2 = localSession
-                                                               .createQuery("from " + FunctionalMenuRole.class.getName() + " where menuId=" + menuId)
+                                                               .createQuery("from :name where menuId=:menuId")
+                                                               .setParameter("name",FunctionalMenuRole.class.getName())
+                                                               .setParameter("menuId",menuId)
                                                                .list();
                                                int numMenuRoles2 = funcMenuRoles2.size();
                                                logger.debug(EELFLoggerDelegate.debugLogger,
                result = new RolesInAppForUser();
                result.appId = appId;
                result.orgUserId = userId;
 +
                for (EcompRole role : userRolesInRemoteApp) {
                        RoleInAppForUser roleInAppForUser = new RoleInAppForUser();
                        roleInAppForUser.roleId = role.getId();
         * @throws HTTPException
         */
        protected Set<EcompRole> postUsersRolesToRemoteApp(List<RoleInAppForUser> roleInAppForUserList, ObjectMapper mapper,
 -                      ApplicationsRestClientService applicationsRestClientService, Long appId, String userId)
 +                      ApplicationsRestClientService applicationsRestClientService, Long appId, String userId,boolean systemUser)
                        throws JsonProcessingException, HTTPException {
                Set<EcompRole> updatedUserRolesinRemote = constructUsersRemoteAppRoles(roleInAppForUserList);
                Set<EcompRole> updateUserRolesInEcomp = constructUsersEcompRoles(roleInAppForUserList);
         *                      set to false if requests from Users page otherwise true
         * @return true on success, false otherwise
         */
 -      protected boolean applyChangesInUserRolesForAppToEcompDB(RolesInAppForUser rolesInAppForUser, boolean externalSystemRequest, String reqType) throws Exception {
 +      protected boolean applyChangesInUserRolesForAppToEcompDB(RolesInAppForUser rolesInAppForUser, boolean externalSystemRequest, String reqType,boolean isSystemUser) throws Exception {
                boolean result = false;
                String userId = rolesInAppForUser.orgUserId;
                Long appId = rolesInAppForUser.appId;
                synchronized (syncRests) {
                        if (rolesInAppForUser != null) {
 -                              createLocalUserIfNecessary(userId);
 +                              createLocalUserIfNecessary(userId, isSystemUser);
                        }
  
                        if (rolesInAppForUser != null) {
                return addRemoteUser;
        }
  
 +      @SuppressWarnings("unchecked")
        protected void pushUserOnRemoteApp(String userId, EPApp app,
                        ApplicationsRestClientService applicationsRestClientService, SearchService searchService,
                        ObjectMapper mapper, boolean postOpenSource, List<RoleInAppForUser> roleInAppForUserList,boolean appRoleIdUsed) throws Exception {
  
 -              EPUser client = searchService.searchUserByUserId(userId);
 +              EPUser client  = null;
 +                      client = searchService.searchUserByUserId(userId);
  
 -              mapper.configure(DeserializationFeature.FAIL_ON_UNKNOWN_PROPERTIES, false);
 -
 -              if (client == null) {
 -                      String msg = "cannot create user " + userId + ", because he/she cannot be found in phonebook.";
 -                      logger.error(EELFLoggerDelegate.errorLogger, msg);
 -                      throw new Exception(msg);
 -              }
 +                      mapper.configure(DeserializationFeature.FAIL_ON_UNKNOWN_PROPERTIES, false);
 +                      if (client == null) {
 +                              String msg = "cannot create user " + userId + ", because he/she cannot be found in directory.";
 +                              logger.error(EELFLoggerDelegate.errorLogger, msg);
 +                              // throw new Exception(msg);
 +                              final Map<String, String> loginIdParams = new HashMap<>();
 +                              loginIdParams.put("orgUserIdValue", userId);
 +                              List<EPUser> userList = new ArrayList<>();
 +                              userList = dataAccessService.executeNamedQuery("epUserAppId", loginIdParams, null);
 +                              if (userList.size() > 0) {
 +                                      logger.debug(EELFLoggerDelegate.debugLogger,
 +                                                      userList.get(0).getOrgUserId() + " User was found in Portal");
 +                                      client = userList.get(0);
 +                                      SortedSet<EPUserApp> userApps = new TreeSet<>();
 +                                      client.setEPUserApps(userApps);
 +                                      client.setSystemUser(false);
 +                              } else {
 +                                      logger.error(EELFLoggerDelegate.errorLogger, "user cannot be found be in directory or in portal");
 +                                      throw new Exception(msg);
 +                              }
  
 +                      }
 +              
                client.setLoginId(userId);
 -              client.setActive(true);
 +          client.setActive(true);
 +          client.setOrgUserId(userId);
 +      
 +              
                roleInAppForUserList.removeIf(role -> role.isApplied.equals(false));
                SortedSet<Role> roles = new TreeSet<>();
  
                boolean epRequestValue = false;
                String userId = "";
                String reqMessage = "";
 -              if (newAppRolesForUser != null && newAppRolesForUser.orgUserId != null) {
 -                      userId = newAppRolesForUser.orgUserId.trim();
 +              if (newAppRolesForUser != null && newAppRolesForUser.getOrgUserId() != null) {
 +                      userId = newAppRolesForUser.getOrgUserId().trim();
                }
 -              Long appId = newAppRolesForUser.appId;
 -              List<RoleInAppForUser> roleInAppForUserList = newAppRolesForUser.appRoles;
 -              if (userId.length() > 0) {
 +              Long appId = newAppRolesForUser.getAppId();
 +              List<RoleInAppForUser> roleInAppForUserList = newAppRolesForUser.getAppRoles();
 +              if (userId.length() > 0 ) {
                        ObjectMapper mapper = new ObjectMapper();
                        mapper.configure(DeserializationFeature.FAIL_ON_UNKNOWN_PROPERTIES, false);
  
                                EPApp app = appsService.getApp(appId);
                                applyChangesToUserAppRolesForMyLoginsRequest(user, appId);
  
 -                              // if centralized app
 +                              boolean systemUser = newAppRolesForUser.isSystemUser();
 +                              if ((app.getCentralAuth() || app.getId().equals(PortalConstants.PORTAL_APP_ID)) && systemUser) {
 +
 +                                      Set<EcompRole> userRolesInLocalApp = postUsersRolesToLocalApp(roleInAppForUserList, mapper,
 +                                                      applicationsRestClientService, appId, userId);
 +                                      RolesInAppForUser rolesInAppForUser = constructRolesInAppForUserUpdate(userId, appId,
 +                                                      userRolesInLocalApp);
 +                                      List<RoleInAppForUser> roleAppUserList = rolesInAppForUser.roles;
 +                                      if (EcompPortalUtils.checkIfRemoteCentralAccessAllowed()) {
 +                                              // Apply changes in external Access system
 +                                              updateUserRolesInExternalSystem(app, rolesInAppForUser.orgUserId, roleAppUserList,
 +                                                              epRequestValue, systemUser);
 +                                      }
 +                                      result = applyChangesInUserRolesForAppToEcompDB(rolesInAppForUser, epRequestValue, "Portal",
 +                                                      systemUser);
 +
 +                              }else if (!app.getCentralAuth() && systemUser)
 +                              {
 +                                      throw new Exception("For non-centralized application we cannot add systemUser");
 +                              }
 +                              else{   // if centralized app
                                if (app.getCentralAuth()) {
                                        if (!app.getId().equals(PortalConstants.PORTAL_APP_ID)) {
                                                pushRemoteUser(roleInAppForUserList, userId, app, mapper, searchService,
                                        if (EcompPortalUtils.checkIfRemoteCentralAccessAllowed()) {
                                                // Apply changes in external Access system
                                                updateUserRolesInExternalSystem(app, rolesInAppForUser.orgUserId, roleAppUserList,
 -                                                              epRequestValue);
 +                                                              epRequestValue,false);
                                        }
 -                                      result = applyChangesInUserRolesForAppToEcompDB(rolesInAppForUser, epRequestValue, "Portal");
 +                                      result = applyChangesInUserRolesForAppToEcompDB(rolesInAppForUser, epRequestValue, "Portal", systemUser);
                                } 
                                // In case if portal is not centralized then follow existing approach
                                else if(!app.getCentralAuth() && app.getId().equals(PortalConstants.PORTAL_APP_ID)){
                                                        applicationsRestClientService, appId, userId);  
                                        RolesInAppForUser rolesInAppForUser = constructRolesInAppForUserUpdate(userId, appId,
                                                        userRolesInLocalApp);
 -                                      result = applyChangesInUserRolesForAppToEcompDB(rolesInAppForUser, epRequestValue, "Portal");
 +                                      result = applyChangesInUserRolesForAppToEcompDB(rolesInAppForUser, epRequestValue, "Portal",false);
                                } else{// remote app
                                        EPUser remoteAppUser = null;
                                        if(!app.getCentralAuth() && !app.getId().equals(PortalConstants.PORTAL_APP_ID)){
                                                        remoteAppUser = addRemoteUser(roleInAppForUserList, userId, app, mapper, searchService, applicationsRestClientService);
                                                }
                                                Set<EcompRole> userRolesInRemoteApp = postUsersRolesToRemoteApp(roleInAppForUserList, mapper,
 -                                                                      applicationsRestClientService, appId, userId);
 +                                                                      applicationsRestClientService, appId, userId,systemUser);
                                                        RolesInAppForUser rolesInAppForUser = constructRolesInAppForUserUpdate(userId, appId,
                                                                        userRolesInRemoteApp);
 -                                                      result = applyChangesInUserRolesForAppToEcompDB(rolesInAppForUser, epRequestValue, null);
 +                                                      result = applyChangesInUserRolesForAppToEcompDB(rolesInAppForUser, epRequestValue, null,false);
  
                                                        // If no roles remain, request app to set user inactive.
                                                        if (userRolesInRemoteApp.size() == 0) {
                                                }
                                        }
                                }
 +                      }
                        } catch (Exception e) {
                                /*String message = String.format(
                                                "Failed to create user or update user roles for User %s, AppId %s",
         * @param roleInAppUser Contains list of active roles 
         */
        @SuppressWarnings("unchecked")
 -      private void updateUserRolesInExternalSystem(EPApp app, String orgUserId, List<RoleInAppForUser> roleInAppUser, boolean isPortalRequest) throws Exception
 +      private void updateUserRolesInExternalSystem(EPApp app, String orgUserId, List<RoleInAppForUser> roleInAppUser, boolean isPortalRequest,boolean isSystemUser) throws Exception
        {
                try {
                        // check if user exists
                        userParams.put("orgUserIdValue", orgUserId);
                        List<EPUser> userInfo = checkIfUserExists(userParams);
                        if (userInfo.isEmpty()) {
 -                              createLocalUserIfNecessary(orgUserId);
 +                              createLocalUserIfNecessary(orgUserId, isSystemUser);
                        }
                        String name = "";
                        if (EPCommonSystemProperties
 -                                      .containsProperty(EPCommonSystemProperties.EXTERNAL_CENTRAL_ACCESS_USER_DOMAIN)) {
 +                                      .containsProperty(EPCommonSystemProperties.EXTERNAL_CENTRAL_ACCESS_USER_DOMAIN) && !isSystemUser) {
                                name = orgUserId
                                                + SystemProperties.getProperty(EPCommonSystemProperties.EXTERNAL_CENTRAL_ACCESS_USER_DOMAIN);
 +                      } else {
 +                              name = orgUserId;
                        }
                        ObjectMapper mapper = new ObjectMapper();
                        HttpHeaders headers = EcompPortalUtils.base64encodeKeyForAAFBasicAuth();
                        for (RoleInAppForUser addUserRole : roleInAppUserNonDupls) {
                                if (!(currentUserRolesInExternalSystem
                                                .containsKey(app.getNameSpace() + "." + addUserRole.getRoleName().replaceAll(EcompPortalUtils.EXTERNAL_CENTRAL_AUTH_ROLE_HANDLE_SPECIAL_CHARACTERS, "_")))) {
 -                                      ExternalAccessUser extUser = new ExternalAccessUser(name,
 +                                      ExternalAccessUser extUser = new ExternalAccessUser(name,
                                                        app.getNameSpace() + "." + addUserRole.getRoleName().replaceAll(EcompPortalUtils.EXTERNAL_CENTRAL_AUTH_ROLE_HANDLE_SPECIAL_CHARACTERS, "_"));
                                        String formattedUserRole = mapper.writeValueAsString(extUser);
                                        HttpEntity<String> entity = new HttpEntity<>(formattedUserRole, headers);
                                        }
                                }
                        }
 -              } catch (Exception e) {
 +              } catch (HttpClientErrorException e) {
 +                      logger.error(EELFLoggerDelegate.errorLogger,
 +                                      "updateUserRolesInExternalSystem: Failed to add user role for application {} due to {}",
 +                                      app.getId(), e);
 +                      if (e.getStatusCode() == HttpStatus.FORBIDDEN) {
 +                              logger.error(EELFLoggerDelegate.errorLogger, "Please enter the valid systemUser", orgUserId);
 +                              throw new HttpClientErrorException(HttpStatus.FORBIDDEN, "Please enter the valid systemUser");
 +                      }
 +                      if (e.getStatusCode() == HttpStatus.NOT_FOUND) {
 +                              logger.error(EELFLoggerDelegate.errorLogger, "Please enter the valid role");
 +                              throw new HttpClientErrorException(HttpStatus.NOT_FOUND, "Please enter the valid role");
 +                      }
 +                      EPLogUtil.logExternalAuthAccessAlarm(logger, HttpStatus.BAD_REQUEST);
 +                      throw e;
 +              }
 +
 +              catch (Exception e) {
                        logger.error(EELFLoggerDelegate.errorLogger, "updateUserRolesInExternalSystem: Failed to add user role for application {} due to {}", app.getId(), e);
                        EPLogUtil.logExternalAuthAccessAlarm(logger, HttpStatus.BAD_REQUEST);
                        throw e;
                                        List<RoleInAppForUser> roleAppUserList = rolesInAppForUser.roles;
                                        if(EcompPortalUtils.checkIfRemoteCentralAccessAllowed()) {
                                        // Apply changes in external Access system
 -                                      updateUserRolesInExternalSystem(app, rolesInAppForUser.orgUserId, roleAppUserList, externalSystemRequest);
 +                                      updateUserRolesInExternalSystem(app, rolesInAppForUser.orgUserId, roleAppUserList, externalSystemRequest,false);
                                        }
                                        logger.info(EELFLoggerDelegate.debugLogger, "setExternalRequestUserAppRole: {} user app roles: for app {}, user {}", logMessage,
                                                        newAppRolesForUser.getApplicationName(), newAppRolesForUser.getLoginId());
 -                                      result = applyChangesInUserRolesForAppToEcompDB(rolesInAppForUser, externalSystemRequest, reqType);
 +                                      result = applyChangesInUserRolesForAppToEcompDB(rolesInAppForUser, externalSystemRequest, reqType,false);
                                } 
                                // If local application is not centralized 
                                else if(!app.getCentralAuth() && app.getId().equals(PortalConstants.PORTAL_APP_ID)){
                                                        applicationsRestClientService, app.getId(), orgUserId); 
                                        RolesInAppForUser rolesInAppForUser = constructRolesInAppForUserUpdate(orgUserId, app.getId(),
                                                        userRolesInLocalApp);
 -                                      result = applyChangesInUserRolesForAppToEcompDB(rolesInAppForUser, externalSystemRequest, reqType);
 +                                      result = applyChangesInUserRolesForAppToEcompDB(rolesInAppForUser, externalSystemRequest, reqType,false);
                                } else {// remote app
                                        // If adding just account admin role don't do remote application user call
                                        if (!((roleInAppForUserList.size() == 1 || reqType.equals("DELETE")) && checkIfAdminRoleExists)) {
                                                }
  
                                                Set<EcompRole> userRolesInRemoteApp = postUsersRolesToRemoteApp(roleInAppForUserList, mapper,
 -                                                              applicationsRestClientService, app.getId(), orgUserId);
 +                                                              applicationsRestClientService, app.getId(), orgUserId,false);
  
                                                RolesInAppForUser rolesInAppForUser = constructRolesInAppForUserUpdate(orgUserId, app.getId(),
                                                                userRolesInRemoteApp);
                                                                "setExternalRequestUserAppRole: {} user app roles: for app {}, user {}", logMessage,
                                                                newAppRolesForUser.getApplicationName(), newAppRolesForUser.getLoginId());
                                                result = applyChangesInUserRolesForAppToEcompDB(rolesInAppForUser, externalSystemRequest,
 -                                                              reqType);
 +                                                              reqType,false);
                                                // If no roles remain, request app to set user inactive.
                                                /*
                                                 * if (userRolesInRemoteApp.size() == 0) {
                                                logger.info(EELFLoggerDelegate.debugLogger, "setExternalRequestUserAppRole: {} user app roles: for app {}, user {}",
                                                                logMessage, newAppRolesForUser.getApplicationName(), newAppRolesForUser.getLoginId());
                                                result = applyChangesInUserRolesForAppToEcompDB(rolesInAppForUser, externalSystemRequest,
 -                                                              reqType);
 +                                                              reqType,false);
                                        }
                                        if(!result){
                                                reqMessage = "Failed to save the user app role(s)";
                List<EPUserAppRoles>  appRole= null;
                try {
                        logger.error(EELFLoggerDelegate.errorLogger,"Should not be reached here, still the endpoint is yet to be defined");
 -                      boolean result = postUserRolesToMylogins(userAppRolesData, applicationsRestClientService, userAppRolesData.appId, user.getId());
 +                      boolean result = postUserRolesToMylogins(userAppRolesData, applicationsRestClientService,
 +                              userAppRolesData.getAppId(), user.getId());
                        logger.debug(EELFLoggerDelegate.debugLogger,"putUserAppRolesRequest: result {}", result);
                                                
 -                      params.put("appId", userAppRolesData.appId);
 +                      params.put("appId", userAppRolesData.getAppId());
                        EPUserAppRolesRequest epAppRolesRequestData = new EPUserAppRolesRequest();
                        epAppRolesRequestData.setCreatedDate(new Date());
                        epAppRolesRequestData.setUpdatedDate(new Date());
                        epAppRolesRequestData.setUserId(user.getId());
 -                      epAppRolesRequestData.setAppId(userAppRolesData.appId);
 +                      epAppRolesRequestData.setAppId(userAppRolesData.getAppId());
                        epAppRolesRequestData.setRequestStatus("P");
 -                      List<RoleInAppForUser> appRoleIdList = userAppRolesData.appRoles;
 +                      List<RoleInAppForUser> appRoleIdList = userAppRolesData.getAppRoles();
                        Set<EPUserAppRolesRequestDetail> appRoleDetails = new LinkedHashSet<EPUserAppRolesRequestDetail>();
                        dataAccessService.saveDomainObject(epAppRolesRequestData, null);
                        for (RoleInAppForUser userAppRoles : appRoleIdList) {
@@@ -2,7 -2,7 +2,7 @@@
   * ============LICENSE_START==========================================
   * ONAP Portal
   * ===================================================================
 - * Copyright (C) 2017 AT&T Intellectual Property. All rights reserved.
 + * Copyright (C) 2019 AT&T Intellectual Property. All rights reserved.
   * ===================================================================
   *
   * Unless otherwise specified, all software contained herein is licensed
@@@ -55,7 -55,6 +55,7 @@@ import java.util.TreeSet
  import javax.servlet.http.HttpServletResponse;
  
  import org.apache.cxf.transport.http.HTTPException;
 +import org.drools.core.command.assertion.AssertEquals;
  import org.hibernate.Query;
  import org.hibernate.SQLQuery;
  import org.hibernate.Session;
@@@ -238,27 -237,6 +238,27 @@@ public class UserRolesCommonServiceImpl
                return mockRoleInAppForUserList;
        }
  
 +      @SuppressWarnings("unchecked")
 +      @Test
 +      public void checkTheProtectionAgainstSQLInjection() throws Exception {
 +              EPUser user = mockUser.mockEPUser();
 +              user.setId(1l);
 +              user.setOrgId(2l);
 +              Query epUserQuery = Mockito.mock(Query.class);
 +              List<EPUser> mockEPUserList = new ArrayList<>();
 +              mockEPUserList.add(user);
 +
 +              Mockito.when(session.createQuery("from :name where orgUserId=:userId")).thenReturn(epUserQuery);
 +              Mockito.when(epUserQuery.setParameter("name",EPUser.class.getName())).thenReturn(epUserQuery);
 +              Mockito.when(epUserQuery.setParameter("userId",user.getOrgUserId() + "; select * from " + EPUser.class.getName() +";")).thenReturn(epUserQuery);
 +              userRolesCommonServiceImpl.createLocalUserIfNecessary(user.getOrgUserId(),true);
 +
 +              Mockito.when(session.createQuery("from :name where orgUserId=:userId")).thenReturn(epUserQuery);
 +              Mockito.when(epUserQuery.setParameter("name",EPUser.class.getName())).thenReturn(epUserQuery);
 +              Mockito.when(epUserQuery.setParameter("userId",user.getOrgUserId())).thenReturn(epUserQuery);
 +              userRolesCommonServiceImpl.createLocalUserIfNecessary(user.getOrgUserId(),true);
 +      }
 +
        @SuppressWarnings("unchecked")
        @Test
        public void getAppRolesForUserNonCentralizedForPortal() throws Exception {
                Mockito.when(applicationsRestClientService.get(EcompRole[].class, mockApp.getId(), "/roles"))
                                .thenReturn(mockEcompRoleArray);
                // syncAppRolesTest
 -              Mockito.when(session.createQuery("from " + EPRole.class.getName() + " where appId=" + mockApp.getId()))
 +
 +              Mockito.when(session.createQuery("from :name where appId = :appId"))
                                .thenReturn(epRoleQuery);
 +
 +              Mockito.when(epRoleQuery.setParameter("name",EPRole.class.getName())).thenReturn(epRoleQuery);
 +              Mockito.when(epRoleQuery.setParameter("appId",mockApp.getId())).thenReturn(epRoleQuery);
 +
                Mockito.doReturn(mockEPRoleList).when(epRoleQuery).list();
 -              Mockito.when(session.createQuery(
 -                              "from " + EPUserApp.class.getName() + " where app.id=" + mockApp.getId() + " and role_id=" + 15l))
 +              Mockito.when(session.createQuery("from :name where app.id=:appId and role_id=:roleId"))
                                .thenReturn(epUserAppsQuery);
 +              Mockito.when(epUserAppsQuery.setParameter("name",EPUserApp.class.getName())).thenReturn(epUserAppsQuery);
 +              Mockito.when(epUserAppsQuery.setParameter("appId",mockApp.getId())).thenReturn(epUserAppsQuery);
 +              Mockito.when(epUserAppsQuery.setParameter("roleId",15l)).thenReturn(epUserAppsQuery);
                Mockito.doReturn(mockUserRolesList).when(epUserAppsQuery).list();
  
-               Mockito.when(session.createQuery("from " + FunctionalMenuRole.class.getName() + " where roleId=" + 15l))
+               Mockito.when(session.createQuery("from :name where roleId=:roleId"))
                                .thenReturn(epFunctionalMenuQuery);
+               Mockito.when(epFunctionalMenuQuery.setParameter("name",FunctionalMenuRole.class.getName())).thenReturn(epFunctionalMenuQuery);
+               Mockito.when(epFunctionalMenuQuery.setParameter("roleId",15l)).thenReturn(epFunctionalMenuQuery);
                Mockito.doReturn(mockFunctionalMenuRolesList).when(epFunctionalMenuQuery).list();
  
-               Mockito.when(session.createQuery("from " + FunctionalMenuRole.class.getName() + " where menuId=" + 10l))
+               Mockito.when(session.createQuery("from :name where menuId=:menuId"))
                                .thenReturn(epFunctionalMenuQuery2);
+               Mockito.when(epFunctionalMenuQuery2.setParameter("name",FunctionalMenuRole.class.getName())).thenReturn(epFunctionalMenuQuery2);
+               Mockito.when(epFunctionalMenuQuery2.setParameter("menuId",10l)).thenReturn(epFunctionalMenuQuery2);
                Mockito.doReturn(mockFunctionalMenuRolesList).when(epFunctionalMenuQuery2).list();
  
                Mockito.when(session.createQuery("from " + FunctionalMenuItem.class.getName() + " where menuId=" + 10l))
                EPUserAppRolesRequest mockEpAppRolesRequestData = new EPUserAppRolesRequest();
                Mockito.doNothing().when(dataAccessService).saveDomainObject(mockEpAppRolesRequestData, null);
                final Map<String, Long> params = new HashMap<>();
 -              params.put("appId", appWithRolesForUser.appId);
 +              params.put("appId", appWithRolesForUser.getAppId());
                params.put("appRoleId", roleInAppForUser.roleId);
                Mockito.when((List<EPUserAppRoles>) dataAccessService.executeNamedQuery("appRoles", params, null))
                                .thenReturn(epUserAppRolesList);