Implementing Data Security in Community Edition Reports based on Profile Attributes

Introduction: In this blog we are going to explain how to implement data security based on profile attributes of currently logged in user in community edition reports.

In this sample we have a tabular report showing the source wise travel cost and filtering the data based on the source profile. If the profile attribute is defined for a user, then data is filtered based on profile; otherwise, data will not be filtered.

Please make sure you have gone through the blog “Introduction to Community Reporting Interface”

.

Steps to implement Data Security in community report

1. Data Source
2. Dashboard Layout
3. Parameter
4. Report

1. Data source:

Create data source connection.The connection is created with default name “connection1”. We can rename the parameter using pencil icon.We should select the connection type: Groovy Plan Jdbc. Please note that if you want to apply data security you have to use Groovy Plain JDBC only.



If you click on configure icon, the data source configuration place holder will be opened







Configuration code :

<driver>com.mysql.jdbc.Driver</driver> //driver
<url>jdbc:mysql://localhost:3306/SampleTravelData</url>//jdbc database url
<user>hiuser</user>//db username
<pass>hiuser</pass>//db password
<condition>
    import net.sf.json.JSONObject;
				public JSONObject evalCondition() {
					JSONObject responseJson = new JSONObject();
					 responseJson.put("driver","com.mysql.jdbc.Driver");// driver name
					 responseJson.put("url","jdbc:mysql://192.168.2.51:3306/SampleTravelData"); //jdbc database url
					 responseJson.put("user","hiuser"); //db username
					 responseJson.put("password","hiuser"); //db password
					return responseJson;
				}
				</condition>

Note : In the above configuration we are providing mySQL database details, make sure that the mySQL driver has been uploaded from the frontend ( or the relevant driver is present in the drivers folder at the backend.

Refer to this link to understand How to Add a New Driver


2. Dashboard Layout :

In the dashboard layout we basically specify the layout of the report, dashboards, input parameters which we are creating. All the divs are specified here within which they get rendered.

We are creating div with name : emp_details

When you click on dashboard layout a layout similar to below will appear:



In the above screen shot we find two options (left side)

HTML and CSS .

If you click on HTML/CSS the place holder for respective component will be displayed and highlighted in the Dashboard layout panel.

We can place the code related to HTML, CSS for dashboard layout and styling.

Dashboard layout:

HTML Code:

<script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.js"></script>
<div class = "col-sm-12 col-md-12 col-xs-12" id="emp_details"></div>

CSS:

In CSS place holder we can add the CSS related to Tabular customization as well as report customization like back-ground color, report border , border radius, color etc.

3. Parameters :

In this report we are not creating parameters. You can skip this section.

Note : Since we are not creating parameters , we should include the below lines of code in the report configuration.

var dashboard = Dashboard;
Dashboard.resetAll();

4. Report :

We can configure different visualizations to render in different divs in the dashboard layout. When click on add report button, the layout will appear as shown below :

By default the report name is created with “report1”. Here we have renamed as “Tabular” and select chart type as “Table”

.

Report Configuration :

var dashboard = Dashboard;
Dashboard.resetAll();

var Table = {
    name: "Table",
	type:  "chart",
	listeners:[],
	requestParameters : {
	
	},
	vf : {
		id : 1,
		file : "__efwvf_name__"
	},
    htmlElementId : "#emp_details",
    executeAtStart: true
};

In the SQL section : choose the connection as “connection1”(As earlier created in the data source section)

Select SQL type as  “sql.groovy”

In this section, we specify the query to be executed when the report is generated. In the where clause, we provide the filter based on the profile name of the user who is logging in. The profile name is present in the session object, GroovyUsersSession.

SQL :

import com.helicalinsight.efw.utility.GroovyUsersSession;
public String evalCondition() {


String responseJson;

String selectClause = "select travel_type,travel_medium,source,travel_cost from travel_details";


String profileValues = GroovyUsersSession.getValue('${profile[\'source\']}');
if(profileValues==""){
whereClause ="";
}

else{
 whereClause = "where source in (${profileValues}) "

}
responseJson = selectClause+ "" +whereClause;

return responseJson;

}

Note : Here we can get the profile values dynamically based on logged in user . The user can have single profile or multiple profiles. We should provide the filter condition in SQL Query on which profile the data should be filtered.

Visualization :

In this section, we specify which columns are dimensions and which measures so that the report is visualized accordingly.

<Dimensions>travel_type,travel_medium,source</Dimensions>
<Measures>travel_cost</Measures>	

Note: After placing the Configuration , SQL , Visualization then click on apply or (control+s)

After completing all the steps save the report by using the Save button on the top ribbon.

At the back end server location, within hi-repository, the following files gets created:

1. Efw (Report View file which can also be accessed from the file browser from the frotnend)
2. Efwce (Report edit file which can also be accessed from the file browser from the frontend, this file can be used to edit the EFWCE report being created)
3. Efwvf :: This file contains the information about the visualization being used.
4. Html :: This file contains HTML layout, CSS, parameters divs etc.
5. Efwd :: This file has got all the datasource related details

Creating profile attributes in Helical Insight Application :

We should login with admin credentials, In Home—>User Management

Select the user and click on pencil icon > +add a profile attribute and its value.

We have created profile attribute: source

A user can have multiple profile attributes and multiple values for each profile attribute. In order to have multiple values for a particular profile, 2 consecutive values must be separated by a comma.

In the front end file browser we can see the below file types of saved report:

When we double click on the report view file (with the extension efw) report opens like below. The other file with the extension EFWCE can be used to edit the created report/dashboard again.