Esempio n. 1
0
  @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;
  }
Esempio n. 2
0
  /**
   * 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();
    }
  }
Esempio n. 3
0
  @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();
      }
    }
  }
Esempio n. 4
0
  @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();
    }
  }
Esempio n. 5
0
  /**
   * 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();
    }
  }