public boolean isExistingRole(String roleName) throws UserStoreException {
Connection dbConnection = null;
PreparedStatement prepStmt = null;
ResultSet rs = null;
boolean isExisting = false;
try {
dbConnection = DatabaseUtil.getDBConnection(dataSource);
prepStmt = dbConnection.prepareStatement(SystemJDBCConstants.GET_ROLE_ID);
prepStmt.setString(1, roleName);
prepStmt.setInt(2, tenantId);
rs = prepStmt.executeQuery();
if (rs.next()) {
int value = rs.getInt(1);
if (value > -1) {
isExisting = true;
}
}
return isExisting;
} catch (SQLException e) {
String errorMessage = "Error occurred while checking is existing role : " + roleName;
// if (log.isDebugEnabled()) {
// log.debug(errorMessage, e);
// }
throw new UserStoreException(errorMessage, e);
} finally {
DatabaseUtil.closeAllConnections(dbConnection, rs, prepStmt);
}
}
private void updateStringValuesToDatabase(
Connection dbConnection, String sqlStmt, Object... params) throws UserStoreException {
PreparedStatement prepStmt = null;
boolean localConnection = false;
try {
if (dbConnection == null) {
localConnection = true;
dbConnection = DatabaseUtil.getDBConnection(dataSource);
}
prepStmt = dbConnection.prepareStatement(sqlStmt);
if (params != null && params.length > 0) {
for (int i = 0; i < params.length; i++) {
Object param = params[i];
if (param == null) {
throw new UserStoreException("Invalid data provided");
} else if (param instanceof String) {
prepStmt.setString(i + 1, (String) param);
} else if (param instanceof Integer) {
prepStmt.setInt(i + 1, (Integer) param);
} else if (param instanceof Date) {
// Timestamp timestamp = new Timestamp(((Date) param).getTime());
// prepStmt.setTimestamp(i + 1, timestamp);
prepStmt.setTimestamp(i + 1, new Timestamp(System.currentTimeMillis()));
} else if (param instanceof Boolean) {
prepStmt.setBoolean(i + 1, (Boolean) param);
}
}
}
int count = prepStmt.executeUpdate();
// if (count == 0) {
// log.info("No rows were updated");
// }
// if (log.isDebugEnabled()) {
// log.debug("Executed querry is " + sqlStmt + " and number of updated rows ::
// "
// + count);
// }
if (localConnection) {
dbConnection.commit();
}
} catch (SQLException e) {
// if (log.isDebugEnabled()) {
// log.debug(e.getMessage(), e);
// }
throw new UserStoreException(e.getMessage(), e);
} finally {
if (localConnection) {
DatabaseUtil.closeAllConnections(dbConnection);
}
DatabaseUtil.closeAllConnections(null, prepStmt);
}
}
public static void udpateUserRoleMappingInBatchMode(
Connection dbConnection, String sqlStmt, Object... params) throws UserStoreException {
PreparedStatement prepStmt = null;
boolean localConnection = false;
try {
prepStmt = dbConnection.prepareStatement(sqlStmt);
int batchParamIndex = -1;
if (params != null && params.length > 0) {
for (int i = 0; i < params.length; i++) {
Object param = params[i];
if (param == null) {
throw new UserStoreException("Null data provided.");
} else if (param instanceof String[]) {
batchParamIndex = i;
} else if (param instanceof String) {
prepStmt.setString(i + 1, (String) param);
} else if (param instanceof Integer) {
prepStmt.setInt(i + 1, (Integer) param);
}
}
}
if (batchParamIndex != -1) {
String[] values = (String[]) params[batchParamIndex];
for (String value : values) {
prepStmt.setString(batchParamIndex + 1, value);
prepStmt.addBatch();
}
}
int[] count = prepStmt.executeBatch();
if (log.isDebugEnabled()) {
log.debug(
"Executed a batch update. Query is : "
+ sqlStmt
+ ": and result is"
+ Arrays.toString(count));
}
if (localConnection) {
dbConnection.commit();
}
} catch (SQLException e) {
log.error(e.getMessage(), e);
log.error("Using sql : " + sqlStmt);
throw new UserStoreException(e.getMessage(), e);
} finally {
if (localConnection) {
DatabaseUtil.closeAllConnections(dbConnection);
}
DatabaseUtil.closeAllConnections(null, prepStmt);
}
}
public static void udpateUserRoleMappingWithExactParams(
Connection dbConnection,
String sqlStmt,
String[] roles,
String userName,
Integer[] tenantIds,
int currentTenantId)
throws UserStoreException {
PreparedStatement ps = null;
boolean localConnection = false;
try {
ps = dbConnection.prepareStatement(sqlStmt);
byte count = 0;
byte index = 0;
for (String role : roles) {
count = 0;
ps.setString(++count, role);
ps.setInt(++count, tenantIds[index]);
ps.setString(++count, userName);
ps.setInt(++count, currentTenantId);
ps.setInt(++count, currentTenantId);
ps.setInt(++count, tenantIds[index]);
ps.addBatch();
++index;
}
int[] cnt = ps.executeBatch();
if (log.isDebugEnabled()) {
log.debug(
"Executed a batch update. Query is : "
+ sqlStmt
+ ": and result is"
+ Arrays.toString(cnt));
}
if (localConnection) {
dbConnection.commit();
}
} catch (SQLException e) {
log.error(e.getMessage(), e);
log.error("Using sql : " + sqlStmt);
throw new UserStoreException(e.getMessage(), e);
} finally {
if (localConnection) {
DatabaseUtil.closeAllConnections(dbConnection);
}
DatabaseUtil.closeAllConnections(null, ps);
}
}
public static int getIntegerValueFromDatabase(
Connection dbConnection, String sqlStmt, Object... params) throws UserStoreException {
PreparedStatement prepStmt = null;
ResultSet rs = null;
int value = -1;
try {
prepStmt = dbConnection.prepareStatement(sqlStmt);
if (params != null && params.length > 0) {
for (int i = 0; i < params.length; i++) {
Object param = params[i];
if (param == null) {
throw new UserStoreException("Null data provided.");
} else if (param instanceof String) {
prepStmt.setString(i + 1, (String) param);
} else if (param instanceof Integer) {
prepStmt.setInt(i + 1, (Integer) param);
}
}
}
rs = prepStmt.executeQuery();
if (rs.next()) {
value = rs.getInt(1);
}
return value;
} catch (SQLException e) {
log.error(e.getMessage(), e);
log.error("Using sql : " + sqlStmt);
throw new UserStoreException(e.getMessage(), e);
} finally {
DatabaseUtil.closeAllConnections(null, rs, prepStmt);
}
}
public void testStuff() throws Exception {
DatabaseUtil.closeDatabasePoolConnection();
initRealmStuff();
doUserStuff();
doUserRoleStuff();
doAuthorizationStuff();
doClaimStuff();
}
public String[] getSystemRoles() throws UserStoreException {
String sqlStmt = SystemJDBCConstants.GET_ROLES;
Connection dbConnection = null;
try {
dbConnection = DatabaseUtil.getDBConnection(dataSource);
String[] roles = DatabaseUtil.getStringValuesFromDatabase(dbConnection, sqlStmt, tenantId);
return UserCoreUtil.addDomainToNames(roles, UserCoreConstants.SYSTEM_DOMAIN_NAME);
} catch (SQLException e) {
String errorMessage = "Error occurred while getting system roles";
// if (log.isDebugEnabled()) {
// log.debug(errorMessage, e);
// }
throw new UserStoreException(errorMessage, e);
} finally {
DatabaseUtil.closeAllConnections(dbConnection);
}
}
public void addSystemUser(String userName, Object credential, String[] roleList)
throws UserStoreException {
Connection dbConnection = null;
String password = (String) credential;
try {
dbConnection = DatabaseUtil.getDBConnection(dataSource);
String sqlStmt1 = SystemJDBCConstants.ADD_USER_SQL;
String saltValue = null;
try {
SecureRandom secureRandom = SecureRandom.getInstance(SHA_1_PRNG);
byte[] bytes = new byte[16];
// secureRandom is automatically seeded by calling nextBytes
secureRandom.nextBytes(bytes);
// saltValue = Base64.encode(bytes);
} catch (NoSuchAlgorithmException e) {
throw new RuntimeException("SHA1PRNG algorithm could not be found.");
}
password = this.preparePassword(password, saltValue);
this.updateStringValuesToDatabase(
dbConnection, sqlStmt1, userName, password, saltValue, false, new Date(), tenantId);
// add user to role.
updateSystemRoleListOfUser(userName, null, roleList);
dbConnection.commit();
} catch (Throwable e) {
try {
if (dbConnection != null) {
dbConnection.rollback();
}
} catch (SQLException e1) {
// log.error("Error while rollbacking add system user operation", e1);
}
// if (log.isDebugEnabled()) {
// log.debug(e.getMessage(), e);
// }
throw new UserStoreException(e.getMessage(), e);
} finally {
DatabaseUtil.closeAllConnections(dbConnection);
}
}
public void testStuff() throws Exception {
DatabaseUtil.closeDatabasePoolConnection();
initRealmStuff();
doRoleStuff();
/*commenting out following since
1. earlier cached stuff by other test cases causes test failure.
2. there is no way to clear authorization cache from the test case*/
// doAuthorizationStuff();
}
public void updateSystemRoleListOfUser(String user, String[] deletedRoles, String[] addRoles)
throws UserStoreException {
String sqlStmt1 = SystemJDBCConstants.REMOVE_ROLE_FROM_USER_SQL;
String sqlStmt2 = SystemJDBCConstants.ADD_ROLE_TO_USER_SQL;
Connection dbConnection = null;
try {
dbConnection = DatabaseUtil.getDBConnection(dataSource);
String type = null;
if (UserCoreConstants.MSSQL_TYPE.equals(type)) {
sqlStmt2 = SystemJDBCConstants.ADD_ROLE_TO_USER_SQL_MSSQL;
}
if (deletedRoles != null && deletedRoles.length > 0) {
DatabaseUtil.udpateUserRoleMappingInBatchMode(
dbConnection, sqlStmt1, deletedRoles, tenantId, user, tenantId);
}
if (addRoles != null && addRoles.length > 0) {
if (UserCoreConstants.OPENEDGE_TYPE.equals(type)) {
sqlStmt2 = SystemJDBCConstants.ADD_ROLE_TO_USER_SQL_OPENEDGE;
DatabaseUtil.udpateUserRoleMappingInBatchMode(
dbConnection, sqlStmt2, user, tenantId, addRoles, tenantId);
} else {
DatabaseUtil.udpateUserRoleMappingInBatchMode(
dbConnection, sqlStmt2, addRoles, tenantId, user, tenantId);
}
}
dbConnection.commit();
} catch (SQLException e) {
String errorMessage = "Error occurred while getting system role list of user : "******"Error occurred while getting database type from DB connection";
// if (log.isDebugEnabled()) {
// log.debug(errorMessage, e);
// }
throw new UserStoreException(errorMessage, e);
} finally {
DatabaseUtil.closeAllConnections(dbConnection);
}
}
public String[] getUserListOfSystemRole(String roleName) throws UserStoreException {
String sqlStmt = SystemJDBCConstants.GET_USER_LIST_OF_ROLE_SQL;
Connection dbConnection = null;
try {
dbConnection = DatabaseUtil.getDBConnection(dataSource);
String[] users =
DatabaseUtil.getStringValuesFromDatabase(
dbConnection, sqlStmt, roleName, tenantId, tenantId);
return UserCoreUtil.addDomainToNames(users, UserCoreConstants.SYSTEM_DOMAIN_NAME);
} catch (SQLException e) {
String errorMessage = "Error occurred while getting user list of system role : " + roleName;
// if (log.isDebugEnabled()) {
// log.debug(errorMessage, e);
// }
throw new UserStoreException(errorMessage, e);
} finally {
DatabaseUtil.closeAllConnections(dbConnection);
}
}
public void addSystemRole(String roleName, String[] userList) throws UserStoreException {
Connection dbConnection = null;
try {
dbConnection = DatabaseUtil.getDBConnection(dataSource);
if (!this.isExistingRole(roleName)) {
DatabaseUtil.updateDatabase(
dbConnection, SystemJDBCConstants.ADD_ROLE_SQL, roleName, tenantId);
}
if (userList != null) {
String sql = SystemJDBCConstants.ADD_USER_TO_ROLE_SQL;
String type = null;
if (UserCoreConstants.MSSQL_TYPE.equals(type)) {
sql = SystemJDBCConstants.ADD_USER_TO_ROLE_SQL_MSSQL;
}
if (UserCoreConstants.OPENEDGE_TYPE.equals(type)) {
sql = SystemJDBCConstants.ADD_USER_TO_ROLE_SQL_OPENEDGE;
DatabaseUtil.udpateUserRoleMappingInBatchMode(
dbConnection, sql, userList, tenantId, roleName, tenantId);
} else {
DatabaseUtil.udpateUserRoleMappingInBatchMode(
dbConnection, sql, userList, roleName, tenantId, tenantId);
}
}
dbConnection.commit();
} catch (SQLException e) {
String errorMessage = "Error occurred while adding system role : " + roleName;
// if (log.isDebugEnabled()) {
// log.debug(errorMessage, e);
// }
throw new UserStoreException(errorMessage, e);
} catch (Exception e) {
String errorMessage = "Error occurred while getting database type from DB connection";
// if (log.isDebugEnabled()) {
// log.debug(errorMessage, e);
// }
throw new UserStoreException(errorMessage, e);
} finally {
DatabaseUtil.closeAllConnections(dbConnection);
}
}
/*This retrieves two parameters, combines them and send back*/
public static String[] getStringValuesFromDatabaseForInternalRoles(
Connection dbConnection, String sqlStmt, Object... params) throws UserStoreException {
String[] values = new String[0];
PreparedStatement prepStmt = null;
ResultSet rs = null;
try {
prepStmt = dbConnection.prepareStatement(sqlStmt);
if (params != null && params.length > 0) {
for (int i = 0; i < params.length; i++) {
Object param = params[i];
if (param == null) {
throw new UserStoreException("Null data provided.");
} else if (param instanceof String) {
prepStmt.setString(i + 1, (String) param);
} else if (param instanceof Integer) {
prepStmt.setInt(i + 1, (Integer) param);
}
}
}
rs = prepStmt.executeQuery();
List<String> lst = new ArrayList<String>();
while (rs.next()) {
String name = rs.getString(1);
String domain = rs.getString(2);
if (domain != null) {
UserCoreUtil.addDomainToName(name, domain);
}
lst.add(name);
}
if (lst.size() > 0) {
values = lst.toArray(new String[lst.size()]);
}
return values;
} catch (SQLException e) {
log.error(e.getMessage(), e);
log.error("Using sql : " + sqlStmt);
throw new UserStoreException(e.getMessage(), e);
} finally {
DatabaseUtil.closeAllConnections(null, rs, prepStmt);
}
}
public static void updateDatabase(Connection dbConnection, String sqlStmt, Object... params)
throws UserStoreException {
PreparedStatement prepStmt = null;
try {
prepStmt = dbConnection.prepareStatement(sqlStmt);
if (params != null && params.length > 0) {
for (int i = 0; i < params.length; i++) {
Object param = params[i];
if (param == null) {
// allow to send null data since null allowed values can be in the table. eg: domain
// name
prepStmt.setString(i + 1, null);
// throw new UserStoreException("Null data provided.");
} else if (param instanceof String) {
prepStmt.setString(i + 1, (String) param);
} else if (param instanceof Integer) {
prepStmt.setInt(i + 1, (Integer) param);
} else if (param instanceof Short) {
prepStmt.setShort(i + 1, (Short) param);
} else if (param instanceof Date) {
Date date = (Date) param;
Timestamp time = new Timestamp(date.getTime());
prepStmt.setTimestamp(i + 1, time);
}
}
}
int count = prepStmt.executeUpdate();
/*if (log.isDebugEnabled()) {
log.debug("Executed Query is " + sqlStmt + " and number of updated rows :: "
+ count);
}*/
} catch (SQLException e) {
log.error("Error! " + e.getMessage(), e);
log.error("Using sql : " + sqlStmt);
throw new UserStoreException("Error! " + e.getMessage(), e);
} finally {
DatabaseUtil.closeAllConnections(null, prepStmt);
}
}
public boolean validate(String userName, String password) {
userName = userName.replace("@", "@");
Connection dbConnection = null;
ResultSet rs = null;
PreparedStatement prepStmt = null;
String sqlstmt = null;
boolean isAuthed = false;
try {
dbConnection = this.getConnection();
dbConnection.setAutoCommit(false);
sqlstmt = this.getSelectUser();
if (log.isDebugEnabled()) {
log.debug(sqlstmt);
}
prepStmt = dbConnection.prepareStatement(sqlstmt);
prepStmt.setString(1, userName);
prepStmt.setString(2, this.preparePassword(password));
rs = prepStmt.executeQuery();
if (rs.next() == true) {
int count = rs.getInt("COUNT(*)");
if (count > 0) {
isAuthed = true;
}
}
} catch (SQLException e) {
log.error(e.getMessage(), e);
log.error("Using sql : " + sqlstmt);
} finally {
DatabaseUtil.closeAllConnections(dbConnection, rs, prepStmt);
}
if (log.isDebugEnabled()) {
log.debug("User " + userName + " login attempt. Login success :: " + isAuthed);
}
return isAuthed;
}
public String[] getSystemUsers() throws UserStoreException {
Connection dbConnection = null;
String sqlStmt = null;
PreparedStatement prepStmt = null;
ResultSet rs = null;
String filter = "*";
int maxItemLimit = 100;
String[] systemsUsers = new String[0];
try {
if (filter != null && filter.trim().length() != 0) {
filter = filter.trim();
filter = filter.replace("*", "%");
filter = filter.replace("?", "_");
} else {
filter = "%";
}
List<String> lst = new LinkedList<String>();
dbConnection = DatabaseUtil.getDBConnection(dataSource);
if (dbConnection == null) {
throw new UserStoreException("null connection");
}
dbConnection.setAutoCommit(false);
dbConnection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
sqlStmt = SystemJDBCConstants.GET_SYSTEM_USER_FILTER_SQL;
prepStmt = dbConnection.prepareStatement(sqlStmt);
prepStmt.setString(1, filter);
if (sqlStmt.contains(UserCoreConstants.UM_TENANT_COLUMN)) {
prepStmt.setInt(2, tenantId);
}
rs = prepStmt.executeQuery();
int i = 0;
while (rs.next()) {
if (i < maxItemLimit) {
String name = rs.getString(1);
lst.add(name);
} else {
break;
}
i++;
}
rs.close();
if (lst.size() > 0) {
systemsUsers = lst.toArray(new String[lst.size()]);
}
Arrays.sort(systemsUsers);
systemsUsers =
UserCoreUtil.addDomainToNames(systemsUsers, UserCoreConstants.SYSTEM_DOMAIN_NAME);
} catch (SQLException e) {
String errorMessage = "Error occurred while getting system users";
// if (log.isDebugEnabled()) {
// log.debug(errorMessage, e);
// }
throw new UserStoreException(errorMessage, e);
} finally {
DatabaseUtil.closeAllConnections(dbConnection, rs, prepStmt);
}
return systemsUsers;
}
public static void udpateUserRoleMappingInBatchModeForInternalRoles(
Connection dbConnection, String sqlStmt, String primaryDomain, Object... params)
throws UserStoreException {
PreparedStatement prepStmt = null;
boolean localConnection = false;
try {
prepStmt = dbConnection.prepareStatement(sqlStmt);
int batchParamIndex = -1;
if (params != null && params.length > 0) {
for (int i = 0; i < params.length; i++) {
Object param = params[i];
if (param == null) {
throw new UserStoreException("Null data provided.");
} else if (param instanceof String[]) {
batchParamIndex = i;
} else if (param instanceof String) {
prepStmt.setString(i + 1, (String) param);
} else if (param instanceof Integer) {
prepStmt.setInt(i + 1, (Integer) param);
}
}
}
if (batchParamIndex != -1) {
String[] values = (String[]) params[batchParamIndex];
for (String value : values) {
String strParam = (String) value;
// add domain if not set
strParam = UserCoreUtil.addDomainToName(strParam, primaryDomain);
// get domain from name
String domainParam = UserCoreUtil.extractDomainFromName(strParam);
if (domainParam != null) {
domainParam = domainParam.toUpperCase();
}
// set domain to sql
prepStmt.setString(params.length + 1, domainParam);
// remove domain before persisting
String nameWithoutDomain = UserCoreUtil.removeDomainFromName(strParam);
// set name in sql
prepStmt.setString(batchParamIndex + 1, nameWithoutDomain);
prepStmt.addBatch();
}
}
int[] count = prepStmt.executeBatch();
if (log.isDebugEnabled()) {
log.debug(
"Executed a batch update. Query is : "
+ sqlStmt
+ ": and result is"
+ Arrays.toString(count));
}
if (localConnection) {
dbConnection.commit();
}
} catch (SQLException e) {
log.error(e.getMessage(), e);
log.error("Using sql : " + sqlStmt);
throw new UserStoreException(e.getMessage(), e);
} finally {
if (localConnection) {
DatabaseUtil.closeAllConnections(dbConnection);
}
DatabaseUtil.closeAllConnections(null, prepStmt);
}
}
