/**
* inserts new user
*
* @param user user object
*/
public static void insertUser(User user) {
Connection con = null;
try {
con = DBUtils.getConn();
String salt = EncryptionUtil.generateSalt();
PreparedStatement stmt =
con.prepareStatement(
"insert into users (first_nm, last_nm, email, username, user_type, password, salt) values (?,?,?,?,?,?,?)");
stmt.setString(1, user.getFirstNm());
stmt.setString(2, user.getLastNm());
stmt.setString(3, user.getEmail());
stmt.setString(4, user.getUsername());
stmt.setString(5, user.getUserType());
stmt.setString(6, EncryptionUtil.hash(user.getPassword() + salt));
stmt.setString(7, salt);
stmt.execute();
DBUtils.closeStmt(stmt);
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtils.closeConn(con);
}
}
/**
* checks to see if username is unique while ignoring current user
*
* @param userId user id
* @param username username
* @return true false indicator
*/
public static boolean isUnique(Long userId, String username) {
boolean isUnique = true;
if (userId == null) {
userId = -99L;
}
Connection con = null;
try {
con = DBUtils.getConn();
PreparedStatement stmt =
con.prepareStatement(
"select * from users where enabled=true and lower(username) like lower(?) and id != ?");
stmt.setString(1, username);
stmt.setLong(2, userId);
ResultSet rs = stmt.executeQuery();
if (rs.next()) {
isUnique = false;
}
DBUtils.closeRs(rs);
DBUtils.closeStmt(stmt);
} catch (Exception ex) {
log.error(ex.toString(), ex);
} finally {
DBUtils.closeConn(con);
}
return isUnique;
}
/**
* returns user base on id
*
* @param con DB connection
* @param userId user id
* @return user object
*/
public static User getUser(Connection con, Long userId) {
User user = null;
try {
PreparedStatement stmt = con.prepareStatement("select * from users where id=?");
stmt.setLong(1, userId);
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
user = new User();
user.setId(rs.getLong("id"));
user.setFirstNm(rs.getString(FIRST_NM));
user.setLastNm(rs.getString(LAST_NM));
user.setEmail(rs.getString(EMAIL));
user.setUsername(rs.getString(USERNAME));
user.setPassword(rs.getString("password"));
user.setUserType(rs.getString(USER_TYPE));
user.setSalt(rs.getString("salt"));
user.setProfileList(UserProfileDB.getProfilesByUser(con, userId));
}
DBUtils.closeRs(rs);
DBUtils.closeStmt(stmt);
} catch (Exception e) {
e.printStackTrace();
}
return user;
}
/**
* resets shared secret for user
*
* @param userId user id
*/
public static void resetSharedSecret(Long userId) {
Connection con = null;
try {
con = DBUtils.getConn();
PreparedStatement stmt = con.prepareStatement("update users set otp_secret=null where id=?");
stmt.setLong(1, userId);
stmt.execute();
DBUtils.closeStmt(stmt);
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtils.closeConn(con);
}
}
/**
* task init method that created DB
*
* @param config task config
* @throws ServletException
*/
public void init(ServletConfig config) throws ServletException {
super.init(config);
try {
Connection connection = DBUtils.getConn();
Statement statement = connection.createStatement();
ResultSet rs =
statement.executeQuery(
"select * from information_schema.tables where upper(table_name) = 'USERS' and table_schema='PUBLIC'");
if (rs == null || !rs.next()) {
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, user_type varchar not null default '"
+ Auth.ADMINISTRATOR
+ "')");
statement.executeUpdate(
"create table if not exists aws_credentials (access_key varchar not null, secret_key varchar not null)");
statement.executeUpdate(
"create table if not exists ec2_keys (id INTEGER PRIMARY KEY AUTO_INCREMENT, key_nm varchar not null, ec2_region varchar not null)");
statement.executeUpdate(
"create table if not exists system (id INTEGER PRIMARY KEY AUTO_INCREMENT, display_nm varchar, instance_id varchar not null, user varchar not null, host varchar not null, port INTEGER not null, key_nm varchar, region varchar not null, state 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)");
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 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, 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
statement.executeUpdate(
"insert into users (username, password, user_type) values('admin', '"
+ EncryptionUtil.hash("changeme")
+ "','"
+ Auth.MANAGER
+ "')");
}
DBUtils.closeRs(rs);
DBUtils.closeStmt(statement);
DBUtils.closeConn(connection);
} catch (Exception ex) {
ex.printStackTrace();
}
}
/**
* returns user base on id
*
* @param userId user id
* @return user object
*/
public static User getUser(Long userId) {
User user = null;
Connection con = null;
try {
con = DBUtils.getConn();
user = getUser(con, userId);
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtils.closeConn(con);
}
return user;
}
/**
* updates existing user
*
* @param user user object
*/
public static void updateUserNoCredentials(User user) {
Connection con = null;
try {
con = DBUtils.getConn();
PreparedStatement stmt =
con.prepareStatement(
"update users set first_nm=?, last_nm=?, email=?, username=?, user_type=? where id=?");
stmt.setString(1, user.getFirstNm());
stmt.setString(2, user.getLastNm());
stmt.setString(3, user.getEmail());
stmt.setString(4, user.getUsername());
stmt.setString(5, user.getUserType());
stmt.setLong(6, user.getId());
stmt.execute();
DBUtils.closeStmt(stmt);
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtils.closeConn(con);
}
}
/**
* returns users based on sort order defined
*
* @param sortedSet object that defines sort order
* @return sorted user list
*/
public static SortedSet getUserSet(SortedSet sortedSet) {
ArrayList<User> userList = new ArrayList<>();
String orderBy = "";
if (sortedSet.getOrderByField() != null && !sortedSet.getOrderByField().trim().equals("")) {
orderBy = "order by " + sortedSet.getOrderByField() + " " + sortedSet.getOrderByDirection();
}
String sql = "select * from users where enabled=true " + orderBy;
Connection con = null;
try {
con = DBUtils.getConn();
PreparedStatement stmt = con.prepareStatement(sql);
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
User user = new User();
user.setId(rs.getLong("id"));
user.setFirstNm(rs.getString(FIRST_NM));
user.setLastNm(rs.getString(LAST_NM));
user.setEmail(rs.getString(EMAIL));
user.setUsername(rs.getString(USERNAME));
user.setPassword(rs.getString("password"));
user.setUserType(rs.getString(USER_TYPE));
userList.add(user);
}
DBUtils.closeRs(rs);
DBUtils.closeStmt(stmt);
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtils.closeConn(con);
}
sortedSet.setItemList(userList);
return sortedSet;
}
/** returns terminal output as a json string */
@Action(value = "/terms/getOutputJSON")
public String getOutputJSON() {
Connection con = DBUtils.getConn();
// this checks to see if session is valid
Long userId =
AuthDB.getUserIdByAuthToken(con, AuthUtil.getAuthToken(servletRequest.getSession()));
if (userId != null) {
// update timeout
AuthUtil.setTimeout(servletRequest.getSession());
List<SessionOutput> outputList = SessionOutputUtil.getOutput(con, userId);
String json = new Gson().toJson(outputList);
try {
servletResponse.getOutputStream().write(json.getBytes());
} catch (Exception ex) {
ex.printStackTrace();
}
} else {
AuthUtil.deleteAllSession(servletRequest.getSession());
}
DBUtils.closeConn(con);
return null;
}
