Beispiel #1
0
  /**
   * returns script base on id
   *
   * @param con DB connection
   * @param scriptId script id
   * @param userId user id
   * @return script object
   */
  public static Script getScript(Connection con, Long scriptId, Long userId) {

    Script script = null;
    try {
      PreparedStatement stmt =
          con.prepareStatement("select * from  scripts where id=? and user_id=?");
      stmt.setLong(1, scriptId);
      stmt.setLong(2, userId);
      ResultSet rs = stmt.executeQuery();

      while (rs.next()) {
        script = new Script();
        script.setId(rs.getLong("id"));
        script.setDisplayNm(rs.getString("display_nm"));
        script.setScript(rs.getString("script"));
      }
      DBUtils.closeRs(rs);
      DBUtils.closeStmt(stmt);

    } catch (Exception e) {
      log.error(e.toString(), e);
    }

    return script;
  }
  /**
   * 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);
  }
  /**
   * 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);
  }
  /**
   * returns public key base on id
   *
   * @param con DB connection
   * @param publicKeyId key id
   * @return script object
   */
  public static PublicKey getPublicKey(Connection con, Long publicKeyId) {

    PublicKey publicKey = null;
    try {
      PreparedStatement stmt = con.prepareStatement("select * from  public_keys where id=?");
      stmt.setLong(1, publicKeyId);
      ResultSet rs = stmt.executeQuery();

      while (rs.next()) {
        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(rs.getString("type"));
        publicKey.setFingerprint(rs.getString("fingerprint"));
        publicKey.setCreateDt(rs.getTimestamp("create_dt"));
      }
      DBUtils.closeRs(rs);
      DBUtils.closeStmt(stmt);

    } catch (Exception e) {
      log.error(e.toString(), e);
    }

    return publicKey;
  }
  /**
   * checks fingerprint to determine if key is disabled
   *
   * @param fingerprint public key fingerprint
   * @return true if disabled
   */
  public static boolean isKeyDisabled(String fingerprint) {
    boolean isDisabled = false;

    Connection con = null;
    try {
      con = DBUtils.getConn();
      PreparedStatement stmt =
          con.prepareStatement(
              "select * from  public_keys where fingerprint like ? and enabled=false");
      stmt.setString(1, fingerprint);
      ResultSet rs = stmt.executeQuery();

      if (rs.next()) {
        isDisabled = true;
      }

      DBUtils.closeRs(rs);
      DBUtils.closeStmt(stmt);
    } catch (Exception e) {
      log.error(e.toString(), e);
    }
    DBUtils.closeConn(con);

    return isDisabled;
  }
Beispiel #7
0
  /**
   * returns script base on id
   *
   * @param scriptId script id
   * @param userId user id
   * @return script object
   */
  public static Script getScript(Long scriptId, Long userId) {

    Script script = null;
    Connection con = null;
    try {
      con = DBUtils.getConn();
      script = getScript(con, scriptId, userId);

    } catch (Exception e) {
      log.error(e.toString(), e);
    }
    DBUtils.closeConn(con);

    return script;
  }
  /**
   * deletes all public keys for a profile
   *
   * @param profileId profile id
   */
  public static void deleteProfilePublicKeys(Long profileId) {

    Connection con = null;
    try {
      con = DBUtils.getConn();
      PreparedStatement stmt = con.prepareStatement("delete from public_keys where profile_id=?");
      stmt.setLong(1, profileId);
      stmt.execute();
      DBUtils.closeStmt(stmt);

    } catch (Exception e) {
      log.error(e.toString(), e);
    }
    DBUtils.closeConn(con);
  }
  /**
   * returns public key base on id
   *
   * @param publicKeyId key id
   * @return script object
   */
  public static PublicKey getPublicKey(Long publicKeyId) {

    PublicKey publicKey = null;
    Connection con = null;
    try {
      con = DBUtils.getConn();
      publicKey = getPublicKey(con, publicKeyId);

    } catch (Exception e) {
      log.error(e.toString(), e);
    }
    DBUtils.closeConn(con);

    return publicKey;
  }
Beispiel #10
0
  /**
   * disables SSH key
   *
   * @param id key id
   */
  public static void disableKey(Long id) {

    Connection con = null;
    try {
      con = DBUtils.getConn();
      PreparedStatement stmt =
          con.prepareStatement("update public_keys set enabled=false where id=?");
      stmt.setLong(1, id);
      stmt.execute();
      DBUtils.closeStmt(stmt);

    } catch (Exception e) {
      log.error(e.toString(), e);
    }
    DBUtils.closeConn(con);
  }
Beispiel #11
0
  /**
   * deletes script
   *
   * @param scriptId script id
   * @param userId user id
   */
  public static void deleteScript(Long scriptId, Long userId) {

    Connection con = null;
    try {
      con = DBUtils.getConn();
      PreparedStatement stmt = con.prepareStatement("delete from scripts where id=? and user_id=?");
      stmt.setLong(1, scriptId);
      stmt.setLong(2, userId);
      stmt.execute();
      DBUtils.closeStmt(stmt);

    } catch (Exception e) {
      log.error(e.toString(), e);
    }
    DBUtils.closeConn(con);
  }
Beispiel #12
0
  public static List<String> getPublicKeysForSystem(Long systemId) {

    Connection con = null;
    List<String> publicKeyList = new ArrayList<String>();
    try {
      con = DBUtils.getConn();

      publicKeyList = getPublicKeysForSystem(con, systemId);

    } catch (Exception e) {
      log.error(e.toString(), e);
    }
    DBUtils.closeConn(con);

    return publicKeyList;
  }
Beispiel #13
0
  /**
   * deletes all public keys for user
   *
   * @param userId user id
   */
  public static void deleteUserPublicKeys(Long userId) {

    Connection con = null;
    try {
      con = DBUtils.getConn();
      PreparedStatement stmt =
          con.prepareStatement("delete from public_keys where user_id=? and enabled=true");
      stmt.setLong(1, userId);
      stmt.execute();
      DBUtils.closeStmt(stmt);

    } catch (Exception e) {
      log.error(e.toString(), e);
    }
    DBUtils.closeConn(con);
  }
Beispiel #14
0
  /**
   * inserts new script
   *
   * @param script script object
   * @param userId user id
   */
  public static void insertScript(Script script, Long userId) {

    Connection con = null;
    try {
      con = DBUtils.getConn();
      PreparedStatement stmt =
          con.prepareStatement("insert into scripts (display_nm, script, user_id) values (?,?,?)");
      stmt.setString(1, script.getDisplayNm());
      stmt.setString(2, script.getScript());
      stmt.setLong(3, userId);
      stmt.execute();
      DBUtils.closeStmt(stmt);

    } catch (Exception e) {
      log.error(e.toString(), e);
    }
    DBUtils.closeConn(con);
  }
Beispiel #15
0
  /**
   * updates existing script
   *
   * @param script script object
   * @param userId user id
   */
  public static void updateScript(Script script, Long userId) {

    Connection con = null;
    try {
      con = DBUtils.getConn();
      PreparedStatement stmt =
          con.prepareStatement(
              "update scripts set display_nm=?, script=? where id=? and user_id=?");
      stmt.setString(1, script.getDisplayNm());
      stmt.setString(2, script.getScript());
      stmt.setLong(3, script.getId());
      stmt.setLong(4, userId);
      stmt.execute();
      DBUtils.closeStmt(stmt);

    } catch (Exception e) {
      log.error(e.toString(), e);
    }
    DBUtils.closeConn(con);
  }
Beispiel #16
0
  /**
   * 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;
  }
Beispiel #17
0
  /**
   * 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;
  }
Beispiel #18
0
  /**
   * returns scripts based on sort order defined
   *
   * @param sortedSet object that defines sort order
   * @param userId user id
   * @return sorted script list
   */
  public static SortedSet getScriptSet(SortedSet sortedSet, Long userId) {

    ArrayList<Script> scriptList = new ArrayList<Script>();

    String orderBy = "";
    if (sortedSet.getOrderByField() != null && !sortedSet.getOrderByField().trim().equals("")) {
      orderBy = "order by " + sortedSet.getOrderByField() + " " + sortedSet.getOrderByDirection();
    }
    String sql = "select * from scripts where user_id=? " + orderBy;

    Connection con = null;
    try {
      con = DBUtils.getConn();
      PreparedStatement stmt = con.prepareStatement(sql);
      stmt.setLong(1, userId);
      ResultSet rs = stmt.executeQuery();

      while (rs.next()) {
        Script script = new Script();
        script.setId(rs.getLong("id"));
        script.setDisplayNm(rs.getString("display_nm"));
        script.setScript(rs.getString("script"));

        scriptList.add(script);
      }
      DBUtils.closeRs(rs);
      DBUtils.closeStmt(stmt);

    } catch (Exception e) {
      log.error(e.toString(), e);
    }
    DBUtils.closeConn(con);

    sortedSet.setItemList(scriptList);
    return sortedSet;
  }
Beispiel #19
0
  /**
   * Deletes all SSH keys for users that are not assigned in a profile
   *
   * @param con DB connection
   * @param profileId profile id
   */
  public static void deleteUnassignedKeysByProfile(Connection con, Long profileId) {

    try {
      PreparedStatement stmt =
          con.prepareStatement(
              "delete from public_keys where profile_id=? and user_id not in (select user_id from user_map where profile_id=?)");
      stmt.setLong(1, profileId);
      stmt.setLong(2, profileId);
      stmt.execute();
      DBUtils.closeStmt(stmt);

    } catch (Exception e) {
      log.error(e.toString(), e);
    }
  }
Beispiel #20
0
  public static List<String> getPublicKeysForSystem(Connection con, Long systemId) {
    List<String> publicKeyList = new ArrayList<String>();

    if (systemId == null) {
      systemId = -99L;
    }
    try {
      PreparedStatement stmt =
          con.prepareStatement(
              "select * from public_keys where (profile_id is null or profile_id in (select profile_id from system_map where system_id=?)) and enabled=true");
      stmt.setLong(1, systemId);
      ResultSet rs = stmt.executeQuery();
      while (rs.next()) {
        publicKeyList.add(rs.getString("public_key"));
      }
      DBUtils.closeStmt(stmt);

    } catch (Exception e) {
      log.error(e.toString(), e);
    }

    return publicKeyList;
  }
Beispiel #21
0
  /**
   * task init method that created DB and generated public/private keys
   *
   * @param config task config
   * @throws ServletException
   */
  public void init(ServletConfig config) throws ServletException {

    super.init(config);

    Connection connection = null;
    Statement statement = null;
    // check if reset ssh application key is set
    boolean resetSSHKey = "true".equals(AppConfig.getProperty("resetApplicationSSHKey"));

    // if DB password is empty generate a random
    if (StringUtils.isEmpty(AppConfig.getProperty("dbPassword"))) {
      String dbPassword = null;
      String dbPasswordConfirm = null;
      // prompt for password and confirmation
      while (dbPassword == null || !dbPassword.equals(dbPasswordConfirm)) {
        dbPassword = new String(System.console().readPassword("Please enter database password: "******"Please confirm database password: "******"Passwords do not match");
        }
      }
      // set password
      if (StringUtils.isNotEmpty(dbPassword)) {
        AppConfig.encryptProperty("dbPassword", dbPassword);
        // if password not set generate a random
      } else {
        System.out.println("Generating random database password");
        AppConfig.encryptProperty("dbPassword", RandomStringUtils.randomAscii(32));
      }
      // else encrypt password if plain-text
    } else if (!AppConfig.isPropertyEncrypted("dbPassword")) {
      AppConfig.encryptProperty("dbPassword", AppConfig.getProperty("dbPassword"));
    }

    try {
      connection = DBUtils.getConn();
      statement = connection.createStatement();

      ResultSet rs =
          statement.executeQuery(
              "select * from information_schema.tables where upper(table_name) = 'USERS' and table_schema='PUBLIC'");
      if (!rs.next()) {
        resetSSHKey = true;

        // create DB objects
        statement.executeUpdate(
            "create table if not exists users (id INTEGER PRIMARY KEY AUTO_INCREMENT, first_nm varchar, last_nm varchar, email varchar, username varchar not null, password varchar, auth_token varchar, enabled boolean not null default true, auth_type varchar not null default '"
                + Auth.AUTH_BASIC
                + "', user_type varchar not null default '"
                + Auth.ADMINISTRATOR
                + "', salt varchar, otp_secret varchar)");
        statement.executeUpdate(
            "create table if not exists user_theme (user_id INTEGER PRIMARY KEY, bg varchar(7), fg varchar(7), d1 varchar(7), d2 varchar(7), d3 varchar(7), d4 varchar(7), d5 varchar(7), d6 varchar(7), d7 varchar(7), d8 varchar(7), b1 varchar(7), b2 varchar(7), b3 varchar(7), b4 varchar(7), b5 varchar(7), b6 varchar(7), b7 varchar(7), b8 varchar(7), foreign key (user_id) references users(id) on delete cascade) ");
        statement.executeUpdate(
            "create table if not exists system (id INTEGER PRIMARY KEY AUTO_INCREMENT, display_nm varchar not null, user varchar not null, host varchar not null, port INTEGER not null, authorized_keys varchar not null, status_cd varchar not null default 'INITIAL')");
        statement.executeUpdate(
            "create table if not exists profiles (id INTEGER PRIMARY KEY AUTO_INCREMENT, nm varchar not null, desc varchar not null)");
        statement.executeUpdate(
            "create table if not exists system_map (profile_id INTEGER, system_id INTEGER, foreign key (profile_id) references profiles(id) on delete cascade , foreign key (system_id) references system(id) on delete cascade, primary key (profile_id, system_id))");
        statement.executeUpdate(
            "create table if not exists user_map (user_id INTEGER, profile_id INTEGER, foreign key (user_id) references users(id) on delete cascade, foreign key (profile_id) references profiles(id) on delete cascade, primary key (user_id, profile_id))");
        statement.executeUpdate(
            "create table if not exists application_key (id INTEGER PRIMARY KEY AUTO_INCREMENT, public_key varchar not null, private_key varchar not null, passphrase varchar)");

        statement.executeUpdate(
            "create table if not exists status (id INTEGER, user_id INTEGER, status_cd varchar not null default 'INITIAL', foreign key (id) references system(id) on delete cascade, foreign key (user_id) references users(id) on delete cascade, primary key(id, user_id))");
        statement.executeUpdate(
            "create table if not exists scripts (id INTEGER PRIMARY KEY AUTO_INCREMENT, user_id INTEGER, display_nm varchar not null, script varchar not null, foreign key (user_id) references users(id) on delete cascade)");

        statement.executeUpdate(
            "create table if not exists public_keys (id INTEGER PRIMARY KEY AUTO_INCREMENT, key_nm varchar not null, type varchar, fingerprint varchar, public_key varchar, enabled boolean not null default true, create_dt timestamp not null default CURRENT_TIMESTAMP(),  user_id INTEGER, profile_id INTEGER, foreign key (profile_id) references profiles(id) on delete cascade, foreign key (user_id) references users(id) on delete cascade)");

        statement.executeUpdate(
            "create table if not exists session_log (id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id INTEGER, session_tm timestamp default CURRENT_TIMESTAMP, foreign key (user_id) references users(id) on delete cascade )");
        statement.executeUpdate(
            "create table if not exists terminal_log (session_id BIGINT, instance_id INTEGER, system_id INTEGER, output varchar not null, log_tm timestamp default CURRENT_TIMESTAMP, foreign key (session_id) references session_log(id) on delete cascade, foreign key (system_id) references system(id) on delete cascade)");

        // insert default admin user
        String salt = EncryptionUtil.generateSalt();
        PreparedStatement pStmt =
            connection.prepareStatement(
                "insert into users (username, password, user_type, salt) values(?,?,?,?)");
        pStmt.setString(1, "admin");
        pStmt.setString(2, EncryptionUtil.hash("changeme" + salt));
        pStmt.setString(3, Auth.MANAGER);
        pStmt.setString(4, salt);
        pStmt.execute();
        DBUtils.closeStmt(pStmt);
      }
      DBUtils.closeRs(rs);

      // if reset ssh application key then generate new key
      if (resetSSHKey) {

        // delete old key entry
        PreparedStatement pStmt = connection.prepareStatement("delete from application_key");
        pStmt.execute();
        DBUtils.closeStmt(pStmt);

        // generate new key and insert passphrase
        System.out.println("Setting KeyBox SSH public/private key pair");

        // generate application pub/pvt key and get values
        String passphrase = SSHUtil.keyGen();
        String publicKey = SSHUtil.getPublicKey();
        String privateKey = SSHUtil.getPrivateKey();

        // insert new keys
        pStmt =
            connection.prepareStatement(
                "insert into application_key (public_key, private_key, passphrase) values(?,?,?)");
        pStmt.setString(1, publicKey);
        pStmt.setString(2, EncryptionUtil.encrypt(privateKey));
        pStmt.setString(3, EncryptionUtil.encrypt(passphrase));
        pStmt.execute();
        DBUtils.closeStmt(pStmt);

        System.out.println("KeyBox Generated Global Public Key:");
        System.out.println(publicKey);

        // set config to default
        AppConfig.updateProperty("publicKey", "");
        AppConfig.updateProperty("privateKey", "");
        AppConfig.updateProperty("defaultSSHPassphrase", "${randomPassphrase}");

        // set to false
        AppConfig.updateProperty("resetApplicationSSHKey", "false");
      }

      // delete ssh keys
      SSHUtil.deletePvtGenSSHKey();

    } catch (Exception ex) {
      log.error(ex.toString(), ex);
    } finally {
      DBUtils.closeStmt(statement);
      DBUtils.closeConn(connection);
    }

    RefreshAuthKeyUtil.startRefreshAllSystemsTimerTask();
  }
Beispiel #22
0
  /**
   * 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;
  }