/**
* select all unique public keys for user
*
* @param userId user id
* @return public key list for user
*/
public static List<PublicKey> getUniquePublicKeysForUser(Long userId) {
Connection con = null;
Map<String, PublicKey> keyMap = new LinkedHashMap();
try {
con = DBUtils.getConn();
PreparedStatement stmt =
con.prepareStatement(
"select * from public_keys where user_id=? and enabled=true order by key_nm asc");
stmt.setLong(1, userId);
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
PublicKey publicKey = new PublicKey();
publicKey.setId(rs.getLong("id"));
publicKey.setKeyNm(rs.getString("key_nm"));
publicKey.setPublicKey(rs.getString("public_key"));
publicKey.setProfile(ProfileDB.getProfile(con, rs.getLong("profile_id")));
publicKey.setType(SSHUtil.getKeyType(publicKey.getPublicKey()));
publicKey.setFingerprint(SSHUtil.getFingerprint(publicKey.getPublicKey()));
publicKey.setCreateDt(rs.getTimestamp("create_dt"));
keyMap.put(publicKey.getKeyNm() + " (" + publicKey.getFingerprint() + ")", publicKey);
}
DBUtils.closeRs(rs);
DBUtils.closeStmt(stmt);
} catch (Exception e) {
log.error(e.toString(), e);
}
DBUtils.closeConn(con);
return new ArrayList(keyMap.values());
}
/**
* updates existing public key
*
* @param publicKey key object
*/
public static void updatePublicKey(PublicKey publicKey) {
Connection con = null;
try {
con = DBUtils.getConn();
PreparedStatement stmt =
con.prepareStatement(
"update public_keys set key_nm=?, type=?, fingerprint=?, public_key=?, profile_id=? where id=? and user_id=? and enabled=true");
stmt.setString(1, publicKey.getKeyNm());
stmt.setString(2, SSHUtil.getKeyType(publicKey.getPublicKey()));
stmt.setString(3, SSHUtil.getFingerprint(publicKey.getPublicKey()));
stmt.setString(4, publicKey.getPublicKey().trim());
if (publicKey.getProfile() == null || publicKey.getProfile().getId() == null) {
stmt.setNull(5, Types.NULL);
} else {
stmt.setLong(5, publicKey.getProfile().getId());
}
stmt.setLong(6, publicKey.getId());
stmt.setLong(7, publicKey.getUserId());
stmt.execute();
DBUtils.closeStmt(stmt);
} catch (Exception e) {
log.error(e.toString(), e);
}
DBUtils.closeConn(con);
}
/**
* inserts new public key
*
* @param publicKey key object
*/
public static void insertPublicKey(PublicKey publicKey) {
Connection con = null;
try {
con = DBUtils.getConn();
PreparedStatement stmt =
con.prepareStatement(
"insert into public_keys(key_nm, type, fingerprint, public_key, profile_id, user_id) values (?,?,?,?,?,?)");
stmt.setString(1, publicKey.getKeyNm());
stmt.setString(2, SSHUtil.getKeyType(publicKey.getPublicKey()));
stmt.setString(3, SSHUtil.getFingerprint(publicKey.getPublicKey()));
stmt.setString(4, publicKey.getPublicKey().trim());
if (publicKey.getProfile() == null || publicKey.getProfile().getId() == null) {
stmt.setNull(5, Types.NULL);
} else {
stmt.setLong(5, publicKey.getProfile().getId());
}
stmt.setLong(6, publicKey.getUserId());
stmt.execute();
DBUtils.closeStmt(stmt);
} catch (Exception e) {
log.error(e.toString(), e);
}
DBUtils.closeConn(con);
}
/**
* returns public keys based on sort order defined
*
* @param sortedSet object that defines sort order
* @param userId user id
* @return sorted script list
*/
public static SortedSet getPublicKeySet(SortedSet sortedSet, Long userId) {
ArrayList<PublicKey> publicKeysList = new ArrayList<PublicKey>();
String orderBy = "";
if (sortedSet.getOrderByField() != null && !sortedSet.getOrderByField().trim().equals("")) {
orderBy = "order by " + sortedSet.getOrderByField() + " " + sortedSet.getOrderByDirection();
}
String sql = "select * from public_keys where user_id = ? and enabled=true " + orderBy;
Connection con = null;
try {
con = DBUtils.getConn();
PreparedStatement stmt = con.prepareStatement(sql);
stmt.setLong(1, userId);
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
PublicKey publicKey = new PublicKey();
publicKey.setId(rs.getLong("id"));
publicKey.setKeyNm(rs.getString("key_nm"));
publicKey.setPublicKey(rs.getString("public_key"));
publicKey.setProfile(ProfileDB.getProfile(con, rs.getLong("profile_id")));
publicKey.setType(SSHUtil.getKeyType(publicKey.getPublicKey()));
publicKey.setFingerprint(SSHUtil.getFingerprint(publicKey.getPublicKey()));
publicKey.setCreateDt(rs.getTimestamp("create_dt"));
publicKeysList.add(publicKey);
}
DBUtils.closeRs(rs);
DBUtils.closeStmt(stmt);
} catch (Exception e) {
log.error(e.toString(), e);
}
DBUtils.closeConn(con);
sortedSet.setItemList(publicKeysList);
return sortedSet;
}
/**
* checks if key has already been registered under user's profile
*
* @param userId user id
* @param publicKey public key
* @return true if duplicate
*/
public static boolean isKeyRegistered(Long userId, PublicKey publicKey) {
boolean isDuplicate = false;
PreparedStatement stmt;
Connection con = null;
try {
con = DBUtils.getConn();
stmt =
con.prepareStatement(
"select * from public_keys where user_id=? and fingerprint like ? and profile_id is ? and id is not ?");
stmt.setLong(1, userId);
stmt.setString(2, SSHUtil.getFingerprint(publicKey.getPublicKey()));
if (publicKey.getProfile() != null && publicKey.getProfile().getId() != null) {
stmt.setLong(3, publicKey.getProfile().getId());
} else {
stmt.setNull(3, Types.NULL);
}
if (publicKey.getId() != null) {
stmt.setLong(4, publicKey.getId());
} else {
stmt.setNull(4, Types.NULL);
}
ResultSet rs = stmt.executeQuery();
if (rs.next()) {
isDuplicate = true;
}
DBUtils.closeRs(rs);
DBUtils.closeStmt(stmt);
} catch (Exception ex) {
log.error(ex.toString(), ex);
}
DBUtils.closeConn(con);
return isDuplicate;
}
/**
* returns public keys based on sort order defined
*
* @param sortedSet object that defines sort order
* @return sorted script list
*/
public static SortedSet getPublicKeySet(SortedSet sortedSet) {
ArrayList<PublicKey> publicKeysList = new ArrayList<PublicKey>();
String orderBy = "";
if (sortedSet.getOrderByField() != null && !sortedSet.getOrderByField().trim().equals("")) {
orderBy = " order by " + sortedSet.getOrderByField() + " " + sortedSet.getOrderByDirection();
}
String sql = "select p.*, u.username from public_keys p, users u where u.id=p.user_id ";
sql +=
StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_USER_ID))
? " and p.user_id=? "
: "";
sql +=
StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_PROFILE_ID))
? " and p.profile_id=? "
: "";
sql +=
StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_ENABLED))
? " and p.enabled=? "
: " and p.enabled=true";
sql = sql + orderBy;
Connection con = null;
try {
con = DBUtils.getConn();
PreparedStatement stmt = con.prepareStatement(sql);
int i = 1;
// set filters in prepared statement
if (StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_USER_ID))) {
stmt.setLong(i++, Long.valueOf(sortedSet.getFilterMap().get(FILTER_BY_USER_ID)));
}
if (StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_PROFILE_ID))) {
stmt.setLong(i++, Long.valueOf(sortedSet.getFilterMap().get(FILTER_BY_PROFILE_ID)));
}
if (StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_ENABLED))) {
stmt.setBoolean(i++, Boolean.valueOf(sortedSet.getFilterMap().get(FILTER_BY_ENABLED)));
}
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
PublicKey publicKey = new PublicKey();
publicKey.setId(rs.getLong("id"));
publicKey.setKeyNm(rs.getString("key_nm"));
publicKey.setPublicKey(rs.getString("public_key"));
publicKey.setProfile(ProfileDB.getProfile(con, rs.getLong("profile_id")));
publicKey.setType(SSHUtil.getKeyType(publicKey.getPublicKey()));
publicKey.setFingerprint(SSHUtil.getFingerprint(publicKey.getPublicKey()));
publicKey.setCreateDt(rs.getTimestamp("create_dt"));
publicKey.setUsername(rs.getString("username"));
publicKey.setUserId(rs.getLong("user_id"));
publicKey.setEnabled(rs.getBoolean("enabled"));
publicKeysList.add(publicKey);
}
DBUtils.closeRs(rs);
DBUtils.closeStmt(stmt);
} catch (Exception e) {
log.error(e.toString(), e);
}
DBUtils.closeConn(con);
sortedSet.setItemList(publicKeysList);
return sortedSet;
}