/**
* 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());
}
/**
* 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;
}
/**
* 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;
}
/**
* 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 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;
}
/**
* 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();
}
/**
* 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;
}