/**
* Add a new user to MedSavant.
*
* @param sessID the session we're logged in as
* @param user the user to add
* @param pass the password
* @param level the user's level
* @throws SQLException
*/
@Override
public synchronized void addUser(String sessID, String user, char[] pass, UserLevel level)
throws SQLException, SessionExpiredException {
PooledConnection conn = ConnectionController.connectPooled(sessID);
try {
if (user.startsWith(DATABASE_USER_KEY_PREFIX)) {
throw new SQLException("Can't create user " + user + " -- illegal username");
}
// TODO: Transactions aren't supported for MyISAM, so this has no effect.
conn.setAutoCommit(false);
conn.executePreparedUpdate("CREATE USER ?@'%' IDENTIFIED BY ?", user, new String(pass));
grantPrivileges(sessID, user, level);
conn.commit();
} catch (SQLException sqlx) {
conn.rollback();
throw sqlx;
} finally {
for (int i = 0; i < pass.length; i++) {
pass[i] = 0;
}
conn.setAutoCommit(true);
conn.close();
}
}
@Override
public void removeUser(String sid, String name)
throws SQLException, SessionExpiredException, RemoteException {
PooledConnection conn = ConnectionController.connectPooled(sid);
conn.executePreparedUpdate("DROP USER ?", name);
conn.executeQuery("FLUSH PRIVILEGES");
SettingsManager.getInstance().removeSetting(sid, DATABASE_USER_KEY_PREFIX + name);
}
@Override
public synchronized void changePassword(
String sessID, String userName, char[] oldPass, char[] newPass)
throws SQLException, RemoteException, SessionExpiredException {
PooledConnection conn = ConnectionController.connectPooled(sessID);
try {
conn.setAutoCommit(true);
// Check that old password is valid.
ConnectionController.revalidate(userName, new String(oldPass), sessID);
// TODO: Check the new password against the current mysql password policy.
// Change the password
conn.executePreparedUpdate("SET PASSWORD FOR ? = PASSWORD(?)", userName, new String(newPass));
} finally {
for (int i = 0; i < oldPass.length; ++i) {
oldPass[i] = 0;
}
for (int i = 0; i < newPass.length; ++i) {
newPass[i] = 0;
}
conn.close();
}
}
/**
* Grant the user the privileges appropriate to their level
*
* @param name user name from <code>mysql.user</code> table
* @param level ADMIN, USER, or GUEST
* @throws SQLException
*/
@Override
public void grantPrivileges(String sessID, String name, UserLevel level)
throws SQLException, SessionExpiredException {
PooledConnection conn = ConnectionController.connectPooled(sessID);
try {
String dbName = ConnectionController.getDBName(sessID);
LOG.info("Granting " + level + " privileges to " + name + " on " + dbName + "...");
switch (level) {
case ADMIN:
conn.executePreparedUpdate(
"GRANT ALTER, RELOAD, CREATE, CREATE VIEW, CREATE TEMPORARY TABLES, CREATE USER, DELETE, DROP, FILE, GRANT OPTION, INSERT, SELECT, UPDATE ON *.* TO ?",
name);
conn.executePreparedUpdate(
String.format("GRANT GRANT OPTION ON %s.* TO ?", dbName), name);
conn.executePreparedUpdate(
String.format(
"GRANT ALTER, CREATE, CREATE VIEW, CREATE TEMPORARY TABLES, DELETE, DROP, INSERT, SELECT, UPDATE ON %s.* TO ?",
dbName),
name);
conn.executePreparedUpdate("GRANT SELECT ON mysql.user TO ?", name);
conn.executePreparedUpdate("GRANT SELECT ON mysql.db TO ?", name);
break;
case USER:
conn.executePreparedUpdate(
String.format("GRANT CREATE TEMPORARY TABLES, SELECT ON %s.* TO ?", dbName), name);
// grant read/write/delete on region sets.
conn.executePreparedUpdate(
String.format("GRANT SELECT,INSERT,UPDATE,DELETE ON %s.region_set TO ?", dbName),
name);
conn.executePreparedUpdate(
String.format(
"GRANT SELECT,INSERT,UPDATE,DELETE ON %s.region_set_membership TO ?", dbName),
name);
// Grant read/write/delete on cohorts.
conn.executePreparedUpdate(
String.format("GRANT INSERT,SELECT,UPDATE,DELETE ON %s.cohort TO ?", dbName), name);
conn.executePreparedUpdate(
String.format(
"GRANT INSERT,SELECT,UPDATE,DELETE ON %s.cohort_membership TO ?", dbName),
name);
conn.executePreparedUpdate(
"GRANT SELECT (user, Create_user_priv) ON mysql.user TO ?", name);
conn.executePreparedUpdate(
"GRANT SELECT (user, Create_tmp_table_priv) ON mysql.db TO ?", name);
conn.executePreparedUpdate("GRANT FILE ON *.* TO ?", name);
break;
case GUEST:
conn.executePreparedUpdate(String.format("GRANT SELECT ON %s.* TO ?", dbName), name);
conn.executePreparedUpdate(
"GRANT SELECT (user, Create_user_priv) ON mysql.user TO ?", name);
conn.executePreparedUpdate(
"GRANT SELECT (user, Create_tmp_table_priv) ON mysql.db TO ?", name);
conn.executePreparedUpdate(
String.format("GRANT INSERT ON %s.server_log TO ?", dbName), name);
// Grant permissions to write comments
conn.executePreparedUpdate(
String.format("GRANT INSERT ON %s.variant_starred TO ?", dbName), name);
conn.executePreparedUpdate("GRANT FILE ON *.* TO ?", name);
break;
}
SettingsManager.getInstance()
.addSetting(sessID, DATABASE_USER_KEY_PREFIX + name, level.name());
LOG.info("... granted.");
} catch (Exception ex) {
LOG.error("Problem creating user", ex);
throw new SQLException("Can't setup privileges for user " + name, ex);
} finally {
conn.executeQuery("FLUSH PRIVILEGES");
conn.close();
}
}