External Security Code Explanation
Introduction
This Document contains explanations of External Security code developed by opnbi. In this document, we have explained each method of External Security help user to understand External Security.
This method of external security used when users data resides in database server. along with the resources you have downlodaded from SSO & External security document you will have externalsecurity_db.properties file in files folder of project OPNBI external Security. All the database connection information is driven from this properties file.
Following are the changes to be made to connect with your database;
logPath: Change the log file path here and provide your directory path here
databasename: provide database name of your opnbi application.
info
database name should be opnbi by default if you have installed fresh opnbi with postgres database. in case you have used existing database for installation, check database name in you database and provide name accordingly.
- driver: provide driver class name here.
info
If opnbi installed with PostgreSQL then driver calss name will be org.postgresql.Driver and if it is installed with MySQL, driver class name will be com.mysql.jdbc.Driver.
- databaseurl: provide database connection URL of opnbi
info
You can find database URL in context.xml file available at your_directory\OPNBI\tomcat\webapps\opnbi\META-INF\ folder. open this file and you will find url tag in first Resource tag. in url tag you will find connection URL.
- username & password: User name & password of opnbi database
info
You can find default user name and password in context.xml file available at your_directory\OPNBI\tomcat\webapps\opnbi\META-INF\ folder. open this file and you will find username & password tag in first Resource tag. you can take credential from here
external_security_dbname: provide name of database where your user list is lying
external_security_driver: provide driever class name for users list database
external_security_url: provide database connection URL to connect with database where user list is available
external_security_username: user name to connect with external database
external_security_password: password to connect with external database
external_security_log_path: provide path for log file
Once Mentioned properties updated in externalsecurity_db.properties file, save the changes and check below steps for modification,
- The code starts with importing required dependencies as shown in below code snippet.
package com.opnbi.externalsecurity;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.logging.FileHandler;
import java.util.logging.Handler;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.logging.SimpleFormatter;
import com.activeintelligence.external.auth.ExternalSchema;
- Below code is to initialization of java logger. In Java, logging is an important feature that helps developers to trace out the errors. Java is the programming language that comes with the logging approach. It provides a Logging API that was introduced in Java 1.4 version. It provides the ability to capture the log file.
- Here we have initialized logger and provided path where this log file will be stored
info
Change the BASE_PATH & LOG_FOLDER as per your directory.
public class ExternalSecurityDBImpl implements ExternalSchema { //initialization of logger
private static final Logger logger = Logger.getLogger(ExternalSecurityDBImpl.class.getName());
private static final String BASE_PATH="C:/Users/aivhu/Downloads/OPNBI External Security & SSO/OPNBI External Security/files/";
private static final String LOG_FOLDER="C:/OPNBI4/logs/";
private Properties properties = null;
private Connection connection = null;
- Below code is used to get external settings if any like database properties, logs or Database table names etc.
info
Change path of externalsecurity_db.properties file as per your directory.
public ExternalSecurityDBImpl() { ////to read external settings if any
try {
properties = new Properties();
InputStream input = new FileInputStream(BASE_PATH+"externalsecurity_db.properties");
properties.load(input);
Handler fileHandler = new FileHandler(LOG_FOLDER+"external_security.log",true);
fileHandler.setLevel(Level.ALL);
fileHandler.setFormatter(new SimpleFormatter());
logger.setUseParentHandlers(false);
logger.addHandler(fileHandler);
} catch (IOException e) {
logger.severe(e.getMessage());
e.printStackTrace();
}
}
- Below code is to establish connection with database. Database Properties required to connect with database is fetched from external property file defined in above step.
private void getConnection() { //to open Database connection
if (connection == null) {
logger.info("Opening connection");
System.out.println("Opening connection");
try {
Class.forName(properties.getProperty("external_security_driver"));
String url = properties.getProperty("external_security_url")
+ properties.getProperty("external_security_dbname")+"?"+"useSSL=false";
System.out.println(url);
connection = DriverManager.getConnection(url, properties.getProperty("external_security_username"),
properties.getProperty("external_security_password"));
System.out.println(connection);
} catch (Exception e) {
logger.severe(e.getMessage());
}
}
}
- This Method is used to fetch all users from client database which is defined in property file.
info
Change database name in this query at FROM external_security.users. here we have external_security database
public List<Map<String, Object>> getAllUsers() { //another way to fetch desired column data from database essential for OPNBI
getConnection();
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
PreparedStatement mosl;
try {
String sqlScript = "SELECT firstName as \"firstName\", lastName as \"lastName\",userName as \"userName\", status, password, email,homeFolder as \"homeFolder\", backupUserId as \"backupUserId\",managerUserId as \"managerUserId\",dashboardOption as \"dashboardOption\",alertsOption as \"alertsOption\",reportOption as \"reportOption\",mergeReportOption as \"mergeReportOption\",resourceOption as \"resourceOption\",quickRunOption as \"quickRunOption\",mappingOption as \"mappingOption\",messageOption as \"messageOption\",datasetOption as \"datasetOption\",parameterOption as \"parameterOption\",annotationOption as \"annotationOption\",notificationOption as \"notificationOption\",requestOption as \"requestOption\",adminOption as \"adminOption\",scheduleOption as \"scheduleOption\",userType as \"userType\", default_dashboard, landing_page, locale, timezone,theme, notification, department FROM external_security.users";
mosl = connection.prepareStatement(sqlScript);
ResultSet rs = mosl.executeQuery();
while (rs.next()) {
Map<String, Object> row = new HashMap<String, Object>();
row.put("firstName", rs.getString("firstName"));
row.put("lastName", rs.getString("lastName"));
row.put("userName", rs.getString("userName"));
row.put("status", rs.getString("status"));
row.put("password", rs.getString("password"));
row.put("email", rs.getString("email"));
row.put("homeFolder", rs.getString("homeFolder"));
row.put("backupUserId", 1);
row.put("managerUserId", 1);
row.put("dashboardOption", "0");
row.put("alertsOption", "0");
row.put("olapAnalyticsOption", "0");
row.put("reportOption", "0");
row.put("mergeReportOption", "0");
// row.put("flyReportOption", "0");
row.put("resourceOption", "0");
row.put("quickRunOption", 0);
row.put("mappingOption", "0");
row.put("messageOption", "0");
row.put("datasetOption", "0");
// row.put("listOfValuesOption", "0");
row.put("parameterOption", "0");
row.put("annotationOption", "0");
row.put("notificationOption", "0");
row.put("requestOption", "0");
// row.put("recurringOption", "0");
row.put("adminOption", "0");
row.put("scheduleOption", "0");
row.put("userType", "INT");
row.put("default_Dashboard", null);
row.put("landing_Page", "");
row.put("locale", "en");
row.put("timezone", "SYSTEM");
row.put("notification", "0");
row.put("theme", "Default");
row.put("department", "Default");
list.add(row);
}
} catch (SQLException e) {
logger.severe(e.getMessage());
}
// list.stream().forEach(s->System.out.println(s));
return list;
}
In above method, we fetch all columns from the table, in this method we fetch just required field of data from user table. Both method works the same way, it fetches users data from client database and store in to OPNBI database.
GetAllRoles method fetch all roles available in the client database. Which will be used to assign hierarchical access to a particular user.
info
Change database name in this query at FROM external_security.roles. here we have external_security database
@Override
public List<Map<String, Object>> getAllRoles() { //to fetch user roles from client database
getConnection();
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
PreparedStatement moslrole;
String sqlScript = "SELECT name, description, email,dashboardOption as \"dashboardOption\",alertsOption as \"alertsOption\",reportOption as \"reportOption\",mergeReportOption as \"mergeReportOption\",adhocOption as \"adhocOption\",resourceOption as \"resourceOption\", quickRunOption as \"quickRunOption\",mappingOption as \"mappingOption\",messageOption as \"messageOption\",datasetOption as \"datasetOption\",parameterOption as \"parameterOption\",annotationOption as \"annotationOption\", notificationOption as \"notificationOption\",requestOption as \"requestOption\",adminOption as \"adminOption\",scheduleOption as \"scheduleOption\", department FROM external_security.roles";
try {
moslrole = connection.prepareStatement(sqlScript);
ResultSet rsa = moslrole.executeQuery();
while (rsa.next()) {
Map<String, Object> row = new HashMap<String, Object>();
row.put("name", rsa.getString("name"));
row.put("description", rsa.getString("description"));
row.put("email", rsa.getString("email"));
row.put("dashboardOption", rsa.getString("dashboardOption"));
row.put("alertsOption", rsa.getString("alertsOption"));
// row.put("olapAnalyticsOption", rsa.getString("olapAnalyticsOption"));
row.put("reportOption", rsa.getString("reportOption"));
row.put("mergeReportOption", rsa.getString("mergeReportOption"));
row.put("adhocOption", rsa.getString("adhocOption"));
// row.put("flyReportOption", rsa.getString("flyReportOption"));
row.put("resourceOption", rsa.getString("resourceOption"));
row.put("quickRunOption", rsa.getString("quickRunOption"));
row.put("mappingOption", rsa.getString("mappingOption"));
row.put("messageOption", rsa.getString("messageOption"));
row.put("datasetOption", rsa.getString("datasetOption"));
// row.put("listOfValuesOption", rsa.getString("listOfValuesOption"));
row.put("parameterOption", rsa.getString("parameterOption"));
row.put("annotationOption", rsa.getString("annotationOption"));
row.put("notificationOption", rsa.getString("notificationOption"));
row.put("requestOption", rsa.getString("requestOption"));
// row.put("recurringOption", rsa.getString("recurringOption"));
row.put("adminOption", rsa.getString("adminOption"));
row.put("scheduleOption", rsa.getString("scheduleOption"));
row.put("department", "Default");
list.add(row);
}
rsa.close();
moslrole.close();
} catch (SQLException e) {
logger.severe(e.getMessage());
}
return list;
}
- This Method find the users associated with specific role, for each role.
info
Change database name in this query at FROM external_security.user_role_mapping. here we have external_security database
@Override
public String[] selectUsersOfRole(String role, String department) { //used to fetch users corresponds to roles
getConnection();
PreparedStatement aisStats;
try {
aisStats = connection.prepareStatement("SELECT \"userName\" FROM external_security.user_role_mapping where \"roleName\" = ?");
aisStats.setString(1, role);
ResultSet rs = aisStats.executeQuery();
List<String> s = new ArrayList<String>();
while (rs.next()) {
s.add(rs.getString(1));
}
String[] array = s.toArray(new String[0]);
return array;
} catch (SQLException e) {
logger.severe(e.getMessage());
}
return null;
}
- This Method find the Roles associated with specific user, for each user.
info
Change database name in this query at FROM external_security.user_role_mapping. here we have external_security database
@Override
public String[] selectRolesOfUser(String user, String department) { //used to fetch roles corresponds to users
getConnection();
try {
PreparedStatement aisStats = connection.prepareStatement("SELECT \"roleName\" FROM external_security.user_role_mapping where \"userName\" = ?");
aisStats.setString(1, user);
ResultSet rs = aisStats.executeQuery();
List<String> s = new ArrayList<String>();
while (rs.next()) {
s.add(rs.getString(1));
}
String[] array = s.toArray(new String[0]);
return array;
} catch (SQLException e) {
logger.severe(e.getMessage());
}
return null;
}
- This method has business logic for user authentication.
@Override
public boolean authenticate(String user, String password, byte[] extendedCredentials, //business logic for user authentication
boolean userSetting, String orgId, String deptCode) {
return true;
}
- This is main method from where all methods are being called. this is optional code. This code is only written to check methods working correctly or not.
warning
Before making a Jar file of project, call all methods once from main method provided below. by default this code is commented in order to use this code, un-comment this code snippet and run it as java application. if all methods returning correct data then go for export project as jar file to avoid rework in code.
public static void main(String[] args) { //main method where all methods are being called
ExternalSecurityDBImpl ext = new ExternalSecurityDBImpl();
List<Map<String, Object>> users= ext.getAllUsers();
Iterator<Map<String,Object>> iter = users.iterator();
while(iter.hasNext())
{
System.out.println("Users:"+iter.next());
}
List<Map<String, Object>> roles= ext.getAllRoles();
Iterator<Map<String,Object>> iterroles = roles.iterator();
while(iterroles.hasNext())
{
System.out.println("Roles:"+iterroles.next());
}
}
- This method returns list of columns of roles table need to update when refreshing external security. Options provided here only will be updated as given in the client data base. The rest of the fields will be updated with default value.
@Override
public String rolesUpdatableOptions() { //return list of columns of roles table need to update when refresh external security
return "description,email,dashboardOption,department";
}
- This method returns a list of columns of the user table that need to update when refreshing external securityOptions provided here only will be updated as given in the client database. The rest of the fields will be updated with a default value.
@Override
public String usersUpdatableOptions() { //return list of columns of user table need to update when refresh external security
return "firstName,lastName,status,password,email,department";
}
Save this file and export whole project as a jar file and save it in local storage.
Paste External Security JAR File into location [C:\opnbi\apache-tomcat\webapps\opnbi\WEB-INF\lib]
In order to use it, you need enable SSO and External security from OPNBI Configuration page.
Click on Hamburger menu and Go to Administration > OPNBI Configuration section, Click the checkbox of External Security, as shown in figure.
Select the Secure Radio Button of Editable User Properties and Editable Roles Properties.
Enter in Security class name:
com.opnbi.externalsecurity.ExternalSecurityCSVImplEnter in Admin Users: Admin
Enter in Admin Roles: Administrator
info
If you are fetching user data from database, then class name should be the com.opnbi.externalsecurity.ExternalSecurityDBImpl and in case of LDAP class name should be com.opnbi.externalsecurity.ExternalSchemaLDAPImpl
Click on save button to save the changes.
Restart the tomcat server
Once server started, Login into OPNBI, Go to Administration > User section you will find some new users added into OPNBI.
info
If you are not allowed to login using admin user, check in ai_user table admin user is in Active state or Inactive state. Make it Active if InActive and try to login now with admin user.
In case if user or roles not displayed in user or role section, check Logs in Logs folder