Implementing Data Security in Community Edition Reports based on logged in user Roles

Introduction : In this blog we are going to explain how to implement data security based on the logged in user roles. We are creating a sample tabular report which shows travel data. If the user is having roles ROLE_ADMIN and ROLE_VIEWER then the user can see only Domestic travel data. The users having other then above roles can see only International travel data. A user can have one or multiple roles.

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

Steps to implement Role wise Data Security in CE Tabular report

Data Source
Dashboard Layout
Parameter
Report

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

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://localhost: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 uploaded from the frontend. If you are using other Db make sure that specific DB driver is present in the application.

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 : testReport

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>
<link rel="stylesheet" href="https://cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css">
<div id="testReport"></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.

Parameters :

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

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 :

We should place configuration, SQL, Visualization in the respective place holders , then click on “Apply”.

By default the report name is created with “report1”.In the SQL section : choose the connection as “connection1”(As created earlier in the data source section)

Select SQL type as “sql.groovy”

Report Configuration :

	var report1 = {
    name: "report1",
    type:"chart",
    vf : {
        id: "1",
        file: '__efwvf_name__'
    },
    efwd : {
        file: '__efwd_file_name__'
    },
    htmlElementId : "#testReport", // provide report id here
    executeAtStart: true  // it can be true or false
};

SQL :

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


String roleName = GroovyUsersSession.getValue('${role}.name');
roleName = roleName.replaceAll("'","");

String responseJson;

String selectClause1 = """select travel_type,travel_medium,travel_cost from travel_details where travel_type='Domestic'""";
String selectClause2 = """select travel_type,travel_medium,travel_cost from travel_details where travel_type='International'""";

List<String> roles = Arrays.asList(roleName.split(","));

String value="";

if(roles.contains("ROLE_VIEWER") || roles.contains("ROLE_ADMIN")){  

responseJson=selectClause1;
}

else {
responseJson=selectClause2;
}

return responseJson;
}

Note : We can give filter conditions based on our requirement in the query

Visualization :

travel_type,travel_medium
travel_cost

Note: After placing the Configuration, SQL , Visualization then click on apply or (control+s). After completing all the steps save the report.

In the back end server location the following files gets created:

Efw (Report View file which can also be accessed from the file browser from the frotnend)
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)
Efwvf :: This file contains the information about the visualization being used.
Html :: This file contains HTML layout, CSS, parameters divs etc.
Efwd :: This file has got all the datasource related details

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

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.

Test Case1 :

Report view when we logged in with user : hiadmin and role : ROLE_ADMIN