Implementing Data Security In Canned Reports Based On Logged In User Name

Open Source BI Helical Insight has reintroduced canned reporting module from version 6.0 onwards. We have already covered how canned reports can be created. Once a report is created, it is very important that people get to see their own dataset only.

Introduction: In this blog we are going to explain how to implement data security based on the logged-in user’s name. We have created a report having the data of employee details.

In this example, we want to filter the data based on the logged-in user. If the user logs in as hiadmin, they can see all the data. Other users can see only their own data. This ensures proper data security, allowing admin full access while restricting regular users to their own data. You can read about the user role management here. User roles creation can happen via the user role module of Helical Insight, or it can also be setup automatically via SSO as well.

Please make sure you have gone through the blog “Introduction to Canned Reporting Interface“.

In Canned Report 6.0, data security can be implemented using two approaches: Groovy Managed Connection and Groovy Plain JDBC Connection.
In this documentation, we will focus on Groovy Managed Connection, as it is the recommended approach.

Steps to implement Data Security

1. Create a data source connection using the data sources module. Once you have created a datasource, it will dynamically create a connectionId (like show in the below image). Read more about creating datasource here.
   https://www.helicalinsight.com/adhoc-datasource/

   In our use case, we created a Derby connection, and the connection ID is 1



2. Next we need to create a Groovy Managed Connection. For that, go to the Data Source page, open the Advanced section, and choose Groovy Managed JDBC DataSource. Refer to the screenshot below

   

   Click on Create. This opens the UI displaying the following fields: Datasource Name, Location, and a Groovy placeholder. The Groovy placeholder comes with some default code provided as a reference

   

   We created a Groovy connection with the datasource name Canned Report DS, provided the code below, and then saved the connection

   import net.sf.json.JSONObject;
   public JSONObject evalCondition() {
       JSONObject responseJson = new JSONObject();
       responseJson.put("globalId", 1);   // Put the connection ID which we get from step1
       responseJson.put("type", "global.jdbc");
       return responseJson;
   }
   



3. Open the Canned Report module. By default, it opens as shown below.

   

   Click on Add Connection at the top right, select Groovy Managed connection. Here, we selected Canned ReportDS. Canned ReportDS is the name of the connection which we had created on Step 2.

   

   Once we select the Groovy connection, the SQL type changes to Groovy. Now write the code below in the Groovy SQL placeholder.

   

   import com.helicalinsight.efw.utility.GroovyUsersSession
   public String evalCondition() {
   
       String userName = GroovyUsersSession.getValue('${user}.name')
       userName = userName.replaceAll("'", "")
   
       String responseJson
       String selectClause = '''SELECT * FROM "employee_details"'''
       String whereClause = """ WHERE "employee_name"='${userName}'"""
   
       if (!userName.equals("hiadmin")) {
           responseJson = selectClause + whereClause
       } else {
           responseJson = selectClause;
       }
   
       return responseJson
   }
   

   Code Explanation: We are fetching the name of the user and storing it in a variable. There is a base query. If the user name is hiadmin, only the base query runs with full data. Whereas if the username is anything except hiadmin, then a whereclause also gets added and restricting the user to see only the data based on his user name.

   After entering the Groovy SQL, click Save and then Run. This will return the query response. This Groovy code is very similar to Java. More and more complex conditions can also be put which can check multiple conditions and values like user name, organization name, role, profiles etc.

4. Now go to the Canvas, create the required canned report, and then save it.



5. We have saved the report and the Groovy connection (created on step 2) in the same folder which makes it easy to share.



6. Now share the data source connection, Groovy Managed Connection, and report folder with the user you want to provide access to, with appropriate permissions. In this example, we shared them with the user name “Mitchell Harper”. To read more about sharing you can read here
   https://www.helicalinsight.com/sharing-reports/

   If you have saved the Groovy Connection in a different folder, please make sure that you share all the respective resources and folders properly.

Report view test case 1 :

Report view when we logged in with USER : hiadmin

Note: This user can view all the employees data.

Report view test case 2 :

We created a user Mitchell Harper

Report view when we logged in with USER : Mitchell Harper

Note: This user can view only the entries with employee as Mitchell Harper, according to the condition specified in the SQL query for the report.

Please reach out to support@helicalinsight.com in case of any more questions.