External Security Code explanation

Introduction

• This document contains explanations of External Security code developed by AIV. In this document, we have explained each method of External Security to help users understand its functionality

• This method of external security is utilized when user data resides on a database server. In addition to the resources you have downloaded from the SSO & External security document, you will find an externalsecurity_db.properties file in the files folder of the AIV External Security project. All the database connection information is derived 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 aiv application.

• driver: Provide driver class name here.

• databaseurl: Provide database connection URL of aiv.

• username & password: User name & password of aiv database.

• external_security_dbname: provide name of database where your user list is lying.

• external_security_driver: provide the driver 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 the mentioned properties updated in externalsecurity_db.properties file, save the changes and proceed to check the steps below for any modifications.

1. The code starts with importing required dependencies as shown in below code snippet.

package com.aiv.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;

2. 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.

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/aiv External Security & SSO/aiv External Security/files/";
	private static final String LOG_FOLDER="C:/aiv/logs/";
	private Properties properties = null;
	private Connection connection = null;
	}

3. Below code is used to get external settings if any like database properties, logs or database table names etc.

	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();
		}
	}

4. Below code is to establish connection with database. database properties required to connect with the database are fetched from external property file defined in the 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());
				
			}

		}

	}

5. This method is used to fetch all users from client database which is defined in property file.

	public List<Map<String, Object>> getAllUsers() { //another way to fetch desired column data from database essential for aiv

		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;
	}

6. In above method, we fetch all columns from the table, in this method we fetch just required field of data from user table. Both methods work the same way; they retrieve users data from client database and store in to aiv database.

7. GetAllRoles method fetch all roles available in the client database. Which will be used to assign hierarchical access to a particular user.

@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;
	}

8. This method find the users associated with specific role for each role.

	@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;
	}

9. This method find the Roles associated with specific user, for each user.

	@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;
	}

10. 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;
	}

11. 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.

	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());
			
		}
		
	}

12. 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 database. 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";
	}

13. 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";
	}

14. Save this file and export the entire project as a JAR file, then save it in local storage.

15. Paste External Security JAR File into location [C:\aiv\apache-tomcat\webapps\aiv\WEB-INF\lib]

16. To use it, you need to enable SSO and External Security from the AIV Configuration page.

17. Click on Hamburger menu and Go to Administration > AIV Configuration section, Click on the External Security from left vertical menu, as shown in figure.

    

18. Select the Secure radio button for both Editable User Properties and Editable Roles Properties.

    

19. Enter in Security class name: com.aiv.externalsecurity.ExternalSecurityCSVImpl

20. Enter in Admin Users: Admin

21. Enter in Admin Roles: Administrator

22. Click on submit button to save the changes.

23. Restart the tomcat server.

24. Once server has started, log in to aiv. Go to Administration > User section, and you will find some new users added to AIV.