@Override
public UserLevel getUserLevel(String sessID, String name)
throws SQLException, SessionExpiredException {
if (userExists(sessID, name)) {
// If the user can create other users, they're assumed to be admin.
PooledConnection conn = ConnectionController.connectPooled(sessID);
try {
ResultSet rs =
conn.executePreparedQuery("SELECT Create_user_priv FROM mysql.user WHERE user=?", name);
if (rs.next()) {
if (rs.getString(1).equals("Y")) {
return UserLevel.ADMIN;
}
}
rs =
conn.executePreparedQuery(
"SELECT Create_tmp_table_priv FROM mysql.db WHERE user=?", name);
if (rs.next()) {
if (rs.getString(1).equals("Y")) {
return UserLevel.USER;
}
}
} finally {
conn.close();
}
return UserLevel.GUEST;
}
return UserLevel.NONE;
}
@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 UserRole addRole(String sessID, String roleName, String roleDescription)
throws RemoteException, SessionExpiredException, SQLException, SecurityException {
String thisDatabase = SessionManager.getInstance().getDatabaseForSession(sessID);
checkAdmin(sessID);
// Check if role already exists, and if so, return it.
Set<UserRole> roles = getAllRoles(sessID);
for (UserRole r : roles) {
if (r.getDatabase().equals(thisDatabase) && r.getRoleName().equals(roleName)) {
return r;
}
}
TableSchema roleTable = MedSavantDatabase.UserRoleTableSchema;
InsertQuery iq = new InsertQuery(roleTable.getTableName());
iq.addColumn(
roleTable.getDBColumn(MedSavantDatabase.UserRoleTableSchema.COLUMNNAME_OF_ROLENAME),
roleName);
iq.addColumn(
roleTable.getDBColumn(MedSavantDatabase.UserRoleTableSchema.COLUMNNAME_OF_ROLE_DESCRIPTION),
roleDescription);
PooledConnection conn = ConnectionController.connectPooled(sessID);
PreparedStatement stmt = null;
ResultSet res = null;
int roleId = -1;
try {
stmt = conn.prepareStatement(iq.toString(), Statement.RETURN_GENERATED_KEYS);
stmt.execute();
res = stmt.getGeneratedKeys();
res.next();
roleId = res.getInt(1);
return new UserRole(roleId, roleName, roleDescription, thisDatabase);
} finally {
if (stmt != null) {
stmt.close();
}
if (res != null) {
res.close();
}
if (conn != null) {
conn.close();
}
}
}
/**
* 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 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();
}
}
