Implementing Data Security In Canned Reports Based On Logged In Organization 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 organization. We have created a report having the data of client wise meeting details.

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.

In the example below, we want to filter the data based on the organization to which the user belongs. The organization for the default super admin is Null. When creating a user, if no organization is assigned, that user’s organization is set to Null. If the logged-in user belongs to an organization other than Null, they will see data only for their specific organizations. 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.

Before reading further, please make sure you have gone through the blog “Introduction to Canned Reporting Interface”. Also please note that organization (multi-tenancy) is only available in enterprise edition and not community edition.

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

   

   NOTE: You will also have to specify the “Location” where this Groovy Managed connection should get saved. If this is left blank it will give an error saying.
   “Error: IllegalArgumentException: No value for property ‘directory’ of `ObjectNode`”



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 orgName = GroovyUsersSession.getValue('${org}.name')
       orgName = orgName.replaceAll("'", "")
   
       String responseJson
   
       String selectClause = '''SELECT "client_name", "meeting_date","meeting_purpose" FROM "meeting_details"'''
   
       String whereClause = """ WHERE "client_name"='${orgName}' ORDER BY "meeting_date" """
   
       if (!orgName.equals("Null")) {
           responseJson = selectClause + whereClause
       } else {
           responseJson = selectClause + ''' ORDER BY "meeting_date"'''
       }
   
       return responseJson
   }
   

   Code Explanation: This logic controls data access based on the user’s organization. We are fetching the name of the organization of the current loggedin user and saving it in a variable. Then we are appending a where clause and limiting the data of that organization only. If the organization is Null, the user can see all data. Otherwise, the user sees only the data for their own organization.

   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.

   After entering the Groovy SQL, click Save and then Run. This will return the query response.

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



5. We 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 organization you want to provide access to, with appropriate permissions. In this example, we shared them with the organization “Bitach”. 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:

First testing is by logging in with a user who has got no organization assigned to him, if no organization is assigned then by default Null gets assigned. In this case the user should see full data.

Report view when we logged in with USER: hiadmin , Organization: Null

Note This user can view all the clients’ data.

Report view test case 2 :

We created a user user_bitach with Organization as Bitach

Report view when we logged in with USER : user_bitach , Organization : Bitach

Note: This user can view only the entries with client name as Bitach according to the condition that we specified in the SQL query for the report.

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