Source code

001/**
002 * Copyright 2014 Tampere University of Technology, Pori Department
003 * 
004 * Licensed under the Apache License, Version 2.0 (the "License");
005 * you may not use this file except in compliance with the License.
006 * You may obtain a copy of the License at
007 * 
008 *   http://www.apache.org/licenses/LICENSE-2.0
009 * 
010 * Unless required by applicable law or agreed to in writing, software
011 * distributed under the License is distributed on an "AS IS" BASIS,
012 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
013 * See the License for the specific language governing permissions and
014 * limitations under the License.
015 */
016package service.tut.pori.users.google;
017
018import java.util.Date;
019import java.util.Map;
020import java.util.Map.Entry;
021
022import org.apache.commons.lang3.StringUtils;
023import org.apache.log4j.Logger;
024import org.springframework.beans.factory.annotation.Autowired;
025import org.springframework.jdbc.core.JdbcTemplate;
026import org.springframework.transaction.TransactionStatus;
027import org.springframework.transaction.support.TransactionCallback;
028
029import service.tut.pori.users.UserDAO;
030import core.tut.pori.dao.clause.SQLClause.SQLType;
031import core.tut.pori.dao.SQLDAO;
032import core.tut.pori.users.ExternalAccountConnection;
033import core.tut.pori.users.UserIdentity;
034import core.tut.pori.users.ExternalAccountConnection.UserServiceType;
035import core.tut.pori.utils.NonceUtils;
036import core.tut.pori.utils.NonceUtils.Nonce;
037
038
039/**
040 * DAO for handling authorization tokens for Google User Service.
041 */
042public class GoogleUserDAO extends SQLDAO{
043  private static final Logger LOGGER = Logger.getLogger(GoogleUserDAO.class);
044  /* tables */
045  private static final String TABLE_USERS_GOOGLE = DATABASE+".users_google";
046  /* columns */
047  private static final String COLUMN_ACCESS_TOKEN = "access_token";
048  private static final String COLUMN_ACCESS_TOKEN_EXPIRES = "access_token_expires";
049  private static final String COLUMN_NONCE = "nonce";
050  private static final String COLUMN_NONCE_EXPIRES = "nonce_expires";
051  private static final String COLUMN_REFRESH_TOKEN = "refresh_token";
052  private static final String COLUMN_TOKEN_TYPE = "token_type";
053  /* sql scripts */
054  private static final String SQL_COUNT_USER_ID = "SELECT "+COLUMN_COUNT+" FROM "+TABLE_USERS_GOOGLE+" WHERE "+COLUMN_USER_ID+"=?";
055  private static final int[] SQL_COUNT_USER_ID_SQL_TYPES = new int[]{SQLType.LONG.toInt()};
056  
057  private static final String SQL_INSERT_NONCE = "INSERT INTO "+TABLE_USERS_GOOGLE+" ("+COLUMN_USER_ID+","+COLUMN_NONCE+","+COLUMN_NONCE_EXPIRES+","+COLUMN_ROW_CREATED+") VALUES (?,?,?,NOW())";
058  private static final int[] SQL_INSERT_NONCE_SQL_TYPES = new int[]{SQLType.LONG.toInt(),SQLType.STRING.toInt(),SQLType.LONG.toInt()};
059  
060  private static final String SQL_REMOVE_NONCE = "UPDATE "+TABLE_USERS_GOOGLE+" SET "+COLUMN_NONCE+"=NULL WHERE "+COLUMN_NONCE+"=? LIMIT 1";
061  private static final int[] SQL_REMOVE_NONCE_SQL_TYPES = new int[]{SQLType.STRING.toInt()};
062  
063  private static final String SQL_REMOVE_TOKEN = "DELETE FROM "+TABLE_USERS_GOOGLE+" WHERE "+COLUMN_USER_ID+"=?";
064  private static final int[] SQL_REMOVE_TOKEN_SQL_TYPES = new int[]{SQLType.LONG.toInt()};
065  
066  private static final String SQL_SELECT_USER_ID = "SELECT "+COLUMN_COUNT+", "+COLUMN_USER_ID+" FROM "+TABLE_USERS_GOOGLE+" WHERE "+COLUMN_NONCE+"=? AND "+COLUMN_NONCE_EXPIRES+">? LIMIT 1";
067  private static final int[] SQL_SELECT_USER_ID_SQL_TYPES = new int[]{SQLType.STRING.toInt(),SQLType.LONG.toInt()};
068  
069  private static final String SQL_SELECT_TOKEN = "SELECT "+COLUMN_COUNT+", "+COLUMN_ACCESS_TOKEN+", "+COLUMN_ACCESS_TOKEN_EXPIRES+","+COLUMN_REFRESH_TOKEN+","+COLUMN_TOKEN_TYPE+" FROM "+TABLE_USERS_GOOGLE+" WHERE "+COLUMN_USER_ID+"=? AND ("+COLUMN_ACCESS_TOKEN_EXPIRES+">? OR "+COLUMN_REFRESH_TOKEN+" IS NOT NULL) LIMIT 1";
070  private static final int[] SQL_SELECT_TOKEN_SQL_TYPES = new int[]{SQLType.LONG.toInt(),SQLType.LONG.toInt()}; 
071  
072  /** tokens generated by google should be unique, so the unique column constraint on access token should never match on ON DUPLICATE KEY UPDATE for two different users, the match will be solely done on user id */
073  private static final String SQL_SET_TOKEN = "INSERT INTO "+TABLE_USERS_GOOGLE+" ("+COLUMN_USER_ID+","+COLUMN_ACCESS_TOKEN+","+COLUMN_ACCESS_TOKEN_EXPIRES+","+COLUMN_REFRESH_TOKEN+","+COLUMN_TOKEN_TYPE+","+COLUMN_ROW_CREATED+") VALUES (?,?,?,?,?,NOW()) ON DUPLICATE KEY UPDATE "+COLUMN_ACCESS_TOKEN+"=VALUES("+COLUMN_ACCESS_TOKEN+"), "+COLUMN_ACCESS_TOKEN_EXPIRES+"=VALUES("+COLUMN_ACCESS_TOKEN_EXPIRES+"), "+COLUMN_REFRESH_TOKEN+"=VALUES("+COLUMN_REFRESH_TOKEN+"), "+COLUMN_TOKEN_TYPE+"=VALUES("+COLUMN_TOKEN_TYPE+")";
074  private static final int[] SQL_SET_TOKEN_SQL_TYPES = new int[]{SQLType.LONG.toInt(),SQLType.STRING.toInt(),SQLType.LONG.toInt(),SQLType.STRING.toInt(),SQLType.STRING.toInt()};
075  
076  private static final String SQL_UPDATE_NONCE = "UPDATE "+TABLE_USERS_GOOGLE+" SET "+COLUMN_NONCE+"=?,"+COLUMN_NONCE_EXPIRES+"=? WHERE "+COLUMN_USER_ID+"=?";
077  private static final int[] SQL_UPDATE_NONCE_SQL_TYPES = new int[]{SQLType.STRING.toInt(),SQLType.LONG.toInt(),SQLType.LONG.toInt()};
078  
079  @Autowired
080  private UserDAO _userDAO = null;
081  
082  /**
083   * 
084   * @param googleUserId
085   * @param token
086   * @param userId
087   * @return true on success
088   */
089  public boolean setToken(final String googleUserId, final OAuth2Token token, final UserIdentity userId){
090    if(!UserIdentity.isValid(userId) || !token.isValid() || StringUtils.isBlank(googleUserId)){
091      LOGGER.debug("Invalid userId or token.");
092      return false;
093    }
094
095    return getTransactionTemplate().execute(new TransactionCallback<Boolean>() {
096
097      @Override
098      public Boolean doInTransaction(TransactionStatus status) {
099        JdbcTemplate t = getJdbcTemplate();
100        
101        String oldGoogleUserId = getGoogleUserId(userId);
102        if(oldGoogleUserId == null){
103          LOGGER.debug("Creating a new external account connection for user, id: "+userId.getUserId()+", google user id: "+googleUserId);
104          _userDAO.insertExternalAccountConnection(new ExternalAccountConnection(googleUserId, UserServiceType.GOOGLE), userId);
105        }else if(!oldGoogleUserId.equals(googleUserId)){  // do not allow changing the google user, this may cause undefined behavior on other parts of the system
106          LOGGER.warn("The given google user Id "+googleUserId+" did not match the old google user id: "+oldGoogleUserId);
107          return false;
108        }
109        
110        t.update(SQL_SET_TOKEN, new Object[]{userId.getUserId(),token.getAccessToken(),token.getExpires().getTime(),token.getRefreshToken(),token.getType()}, SQL_SET_TOKEN_SQL_TYPES);
111        return true;
112      }
113    });
114  }
115  
116  /**
117   * 
118   * @param userId non-null, valid userid
119   * @return google user id for the given userId, if known by the system, otherwise returns null
120   */
121  public String getGoogleUserId(UserIdentity userId){
122    ExternalAccountConnection connection = _userDAO.getExternalAccountConnection(userId, UserServiceType.GOOGLE);
123    return (connection == null ? null : connection.getExternalId());
124  }
125  
126  /**
127   * Removes the given nonce if such exists
128   * 
129   * @param nonce
130   */
131  public void removeNonce(String nonce){
132    if(getJdbcTemplate().update(SQL_REMOVE_NONCE, new Object[]{nonce}, SQL_REMOVE_NONCE_SQL_TYPES) < 1){
133      LOGGER.debug("No nonce was cleared.");
134    }else{
135      LOGGER.debug("Nonce cleared.");
136    }
137  }
138  
139  /**
140   * 
141   * @param userId
142   * @return token for the given user or null if none available
143   */
144  public OAuth2Token getToken(UserIdentity userId){
145    if(!UserIdentity.isValid(userId)){
146      LOGGER.debug("Invalid userId.");
147      return null;
148    }
149  
150    OAuth2Token token = new OAuth2Token();
151    Map<String, Object> row = getJdbcTemplate().queryForMap(SQL_SELECT_TOKEN, new Object[]{userId.getUserId(), System.currentTimeMillis()}, SQL_SELECT_TOKEN_SQL_TYPES);
152    for(Entry<String,Object> entry : row.entrySet()){
153      String column = entry.getKey();
154      switch(column){
155        case COLUMN_ACCESS_TOKEN:
156          token.setAccessToken((String) entry.getValue());
157          break;
158        case COLUMN_ACCESS_TOKEN_EXPIRES:
159          token.setExpires(new Date((Long) entry.getValue()));
160          break;
161        case COLUMN_REFRESH_TOKEN:
162          token.setRefreshToken((String) entry.getValue());
163          break;
164        case COLUMN_TOKEN_TYPE:
165          token.setType((String) entry.getValue());
166          break;
167        default:
168          if(checkCountColumn(column, entry.getValue()) < 1){ // if we have only one column, check what it is (should be count)
169            LOGGER.debug("Unknown column name, or no results.");
170            return null;
171          }
172          break;
173      }
174    }
175    return token;
176  }
177  
178  /**
179   * This generates a new nonce from the given UserId, replacing the old one if one was present
180   * 
181   * @param userId
182   * @return new nonce generated for the given userId
183   */
184  public String generateNonce(UserIdentity userId){
185    if(!UserIdentity.isValid(userId)){
186      LOGGER.debug("Invalid userId.");
187      return null;
188    }
189    Nonce nonce = NonceUtils.generateNonce(); // in theory, it is extremely unlikely that the generated nonce will collide with previously generated one, but...
190    if(recordExists(userId.getUserId())){ // ...just in case do not use INSERT ... ON DUPLICATE KEY UPDATE for updating the nonce (as nonce itself is an unique column)
191      getJdbcTemplate().update(SQL_UPDATE_NONCE, new Object[]{nonce.getNonce(),nonce.getExpires(),userId.getUserId()}, SQL_UPDATE_NONCE_SQL_TYPES); // this throw an exception on duplicate nonce if one ever occurs
192    }else{
193      getJdbcTemplate().update(SQL_INSERT_NONCE, new Object[]{userId.getUserId(),nonce.getNonce(),nonce.getExpires()}, SQL_INSERT_NONCE_SQL_TYPES); // this throw an exception on duplicate nonce if one ever occurs
194    }
195    return nonce.getNonce();
196  }
197  
198  /**
199   * remove token from the given userIdentity if one exists
200   * 
201   * @param userId
202   */
203  public void removeToken(UserIdentity userId){
204    if(!UserIdentity.isValid(userId)){
205      LOGGER.debug("Invalid userId.");
206      return;
207    }
208    Long id = userId.getUserId();
209    if(getJdbcTemplate().update(SQL_REMOVE_TOKEN,new Object[]{id},SQL_REMOVE_TOKEN_SQL_TYPES) < 1){
210      LOGGER.debug("No tokens removed for userId: "+id);
211    }else{
212      LOGGER.debug("Token removed for userId: "+id);
213    }
214  }
215  
216  /**
217   * 
218   * @param nonce
219   * @return owner of the given nonce, or null if no such nonce exists or the nonce has expired
220   */
221  public UserIdentity getUser(String nonce){
222    Long userId = (Long) getJdbcTemplate().queryForMap(SQL_SELECT_USER_ID, new Object[]{nonce,System.currentTimeMillis()}, SQL_SELECT_USER_ID_SQL_TYPES).get(COLUMN_USER_ID);
223    return (userId == null ? null : new UserIdentity(userId));
224  }
225  
226  /**
227   * 
228   * @param userId
229   * @return true if a record exists for the given user id
230   */
231  private boolean recordExists(Long userId){
232    return (getJdbcTemplate().queryForObject(SQL_COUNT_USER_ID, new Object[]{userId}, SQL_COUNT_USER_ID_SQL_TYPES, Long.class) > 0);
233  }
234}