Metadata Security Conditions

In this article, we will be covering how to implement data security (hiding or allowing access) to certain table/column/row level data from any organization/roles/users/profiles.

In order to implement metadata security, you need to open metadata security tab.  To know more, Click Here.

Now. let us understand with an example. Suppose we have different users belonging to the same organization as shown in below image. Image shows the users information such as his / her role, department, region and country.

metadata-security-user-example

Following are the steps :

  1. Create a Metadata. In this case, User 1 creates a metadata.
  2. Share the metadata with the intended users with the required permission level. In this case, User 1 shares metadata with User A, User B, User C and User D. Similarly, you can share a metadata based on Users, Roles and Profile.
    (Note: If a metadata is saved in a folder then you have share its folder, sub-folder )
  3. On successful sharing of metadata, all users can access the metadata for reports creation.
  4. Here, all users have access to all tables, columns, data-set present in the metadata.
  5. Now, let’s say User1 (owner of metadata) wants to control the access of metadata with the users.
  6. User1 can provide access/restrict specific table data or column data or row level data from a users role, profile, name or organization conditions. A single metadata can have multiple security conditions.

 

Execution Type : ConditionIf

 

Table Level Condition

 

    1. Restrict a Table data from a User : Here we will learn how you can restrict certain table from user. 
      Expression Name : Hiding table from a user  //can be any name
Entity Name : Table1                    //this is selected table. 1 or more tables can be added
Expression Type: table                  //since we are selecting table
Access Type: deny                       //restricting the data
Execution Type: conditionIf                 
Condition :
${user}.name eq 'User A'          //this condition to be used for single user. User name is Case sensitive

 

    1. Restrict a Table from a Role : Here we will learn how you can restrict certain table from Roles. 
      Expression Name : Hiding table from a Role  //can be any name
Entity Name : Table1                        //this is selected table. 1 or more tables can be added
Expression Type: table                      //since we are selecting table
Access Type: deny                           //restricting the data
Execution Type: conditionIf                 
Condition :
check("${role}.name" , "ROLE_USER")  //this condition to be used for single role. Role name is case sensitive
check("${role}.name" , "ROLE_USER1, ROLE_USER2,....") //this condition to be used for multiple roles. Role name is case sensitive

 

  1. Hiding a Table from a Profile : Here we will learn how you can restrict certain table from profile. 
    Expression Name : Hiding table from a Profile  //can be any name
Entity Name : Table1                        //this is selected table. 1 or more tables can be added
Expression Type: table                      //since we are selecting table
Access Type: deny                           //restricting the data
Execution Type: conditionIf                 
Condition :
check("${profile['ProfileName']}" , "ProfileValue") //this condition to be used for single profile. Profile name is Case sensitive
check("${profile['ProfileName']}" , "ProfileValue1, ProfileValue2,....") //this condition to be used for multiple profiles. Profile name is Case sensitive

 

Column Level Condition

 

    1. Restrict a column data from a User : Here we will learn how you can restrict certain column from user. 
      Expression Name : Hiding column from a user  //can be any name
Entity Name : Column1                        //this is selected column. 1 or more tables can be added
Expression Type: column                      //since we are selecting column
Access Type: deny                           //restricting the data
Execution Type: conditionIf                 
Condition :
${user}.name eq 'User A'             //this condition to be used for single user. User name is Case sensitive

 

    1. Restrict a column from a Role : Here we will learn how you can restrict certain column from Roles. 
      Expression Name : Hiding column from a Role  //can be any name
Entity Name : Column1                        //this is selected column. 1 or more tables can be added
Expression Type: column                      //since we are selecting column
Access Type: deny                           //restricting the data
Execution Type: conditionIf                 
Condition :
check("${role}.name" , "ROLE_USER")  //this condition to be used for single role. Role name is case sensitive
check("${role}.name" , "ROLE_USER1, ROLE_USER2,......")  //this condition to be used for multiple roles. Role name is case sensitive

 

  1. Hiding a column from a Profile : Here we will learn how you can restrict certain column from profile. 
    Expression Name : Hiding column from a Profile  //can be any name
Entity Name : Column1                        //this is selected column. 1 or more tables can be added
Expression Type: column                      //since we are selecting column
Access Type: deny                           //restricting the data
Execution Type: conditionIf                 
Condition :
check("${profile['ProfileName']}" , "ProfileValue")  //this condition to be used for single profile. Profile name is Case sensitive
check("${profile['ProfileName']}" , "ProfileValue1, ProfileValue2,....") //this condition to be used for multiple profiles. Profile name is Case sensitive

 

Data Level / Row Level Condition

 

    1. Limiting Data from a User : Here we will learn how to show user specific data to a user. 
      Expression Name : Limit Data Display from User   //can be any name
Entity Name : Column1   //this is selected column. 1 or more tables can be added
Expression Type: column   //since we are selecting column
Access Type: grant        // granting specified column mentioned in the entity name
Execution Type: conditionIf 
Condition :
${user}.name eq 'User A'  //this condition to be used for single user. User name is Case sensitive
Filter : Table_Name.column_name = filtercondition 
         meeting_details.meeting_impact = 'Finalized' //it filters the meeting_impact data only having value 'Finalized' which is visible by User A

NOTE: Above in filter we have specified a simple filtering condition wherein the value is hardcoded i.e. meeting_details.meeting_impact = ‘Finalized’. Instead of that it is also possible to use a SQLstatement as well (which can futher contian filters, joins, subqueries etc) to fetch the result and then pass it to the filtering condition. In a similar way it can be used in any condition for any kind of filtering (row level, table levle, column level, userwise security, row wise security, column wise security etc)

For example :

meeting_details.meeting_impact = select meeting_impact from meeting_details where emp_id=1;

 

    1. Limiting Data from a Role : Here we will learn how to show specific data based on the roles assigned to a user. 
      Expression Name : Limit Data Display from a Role   //can be any name
Entity Name : Column1   //this is selected column. 1 or more tables can be added
Expression Type: column   //since we are selecting column
Access Type: grant        // granting specified column mentioned in the entity name
Execution Type: conditionIf 
Condition :
check("${role}.name" , "ROLE_USER")  //this condition to be used for single role. Role name is case sensitive
check("${role}.name" , "ROLE_USER1, ROLE_USER2,......")  //this condition to be used for multiple roles. Role name is case sensitive
Filter : Table_Name.column_name = filtercondition 
         employee_details.employee_id between 1 and 12   //it filters employee_id having value 1 to 12 which is visible to a Role specified in the condition.

 

    1. Limiting Data from a Profile : Here we will learn how to show specific data to a user based on the profile it is assigned. 
      Expression Name : Limit Data Display from a Profile   //can be any name
Entity Name : Column1   //this is selected column. 1 or more tables can be added
Expression Type: column   //since we are selecting column
Access Type: grant        // granting specified column mentioned in the entity name
Execution Type: conditionIf 
Condition :
not check("${profile['ProfileName']}", "ALL")  //this condition to be used for single and also same for multiple profiles. Profile name is Case sensitive
Filter : Table_Name.column_name = filtercondition 
         traveldetails.destination = ${profile['Destination']}   //it filters destination having value similar to value of profile attribute.
		 traveldetails.destination in (${profile['Destination']})   //it filters destination having multiple values of profile attribute.

 

  1. Limiting Data from Organization : Here we will learn how to show specific data to a user based on the Organization it belongs. 
    Expression Name : Organization Name Expression	   //can be any name
Entity Name : Column1   //this is selected column. 1 or more tables can be added
Expression Type: column   //since we are selecting column
Access Type: grant        // granting specified column mentioned in the entity name
Execution Type: conditionIf 
Condition :
${org}.name != 'Null'  //this condition will check if user belongs to any organization or not
Filter : Table_Name.column_name = filtercondition 
         meeting_details.client_name = ${org}.name   //it filters client_name having value similar to the logged in organization.

 

Execution Type : Groovy

 

Table Level Condition

 

    1. Restrict a Table data from a User : Here we will learn how you can restrict certain table from user. 
      Expression Name : Hiding table from a user  //can be any name
Entity Name : Table1                    //this is selected table. 1 or more tables can be added
Expression Type: table                  //since we are selecting table
Access Type: deny                       //restricting the data
Execution Type: groovy                 
Condition :
                import com.helicalinsight.adhoc.metadata.GroovyUsersSession;
                def evalCondition() {
                        String userName = GroovyUsersSession.getValue('${user}.name');
                        if (userName.equalsIgnoreCase("'User A'")) {
                            return true
                        } else {
                            return false
                        }        
                }

 

    1. Restrict a Table data from an organization : Here we will learn how you can restrict certain table from organization. 
      Expression Name : Hiding table from an organization  //can be any name
Entity Name : Table1                    //this is selected table. 1 or more tables can be added
Expression Type: table                  //since we are selecting table
Access Type: deny                       //restricting the data
Execution Type: groovy                 
Condition :
                
                import com.helicalinsight.adhoc.metadata.GroovyUsersSession;
                def  evalCondition() {
                    String orgName = GroovyUsersSession.getValue('${org}.name');
                    if (!orgName.equals("'Null'")) {
                        return true
                    } else {
                        return false
                    }  
                }

 

  1. Restrict a Table data from Email Expression : Here we will learn how you can restrict certain table using Email. 
    Expression Name : Hiding table from using Email Expression  //can be any name
Entity Name : Table1                    //this is selected table. 1 or more tables can be added
Expression Type: table                  //since we are selecting table
Access Type: deny                       //restricting the data
Execution Type: groovy                 
Condition :
                
                
                import com.helicalinsight.adhoc.metadata.GroovyUsersSession;
                def  evalCondition() {
                        String userEmail = GroovyUsersSession.getValue('${user}.email');
                        if (userEmail.equals("'bitach@helicaltech.com'")) {
                            return true
                        } else {
                             return false
                        }        
                }

 

Column Level Condition

 

    1. Restrict a column data from a User : Here we will learn how you can restrict certain column from user. 
      Expression Name : Hiding column from a user  //can be any name
Entity Name : Column1                        //this is selected column. 1 or more tables can be added
Expression Type: column                      //since we are selecting column
Access Type: deny                           //restricting the data
Execution Type: groovy                 
Condition :
			
                import com.helicalinsight.adhoc.metadata.GroovyUsersSession;
                def evalCondition() {
                        String userName = GroovyUsersSession.getValue('${user}.name');
                        if (userName.equalsIgnoreCase("'User A'")) {
                            return true
                        } else {
                            return false
                        }        
                }

 

    1. Restrict a column from a Role : Here we will learn how you can restrict certain column from Roles. 
      Expression Name : Hiding column from a Role  //can be any name
Entity Name : Column1                        //this is selected column. 1 or more tables can be added
Expression Type: column                      //since we are selecting column
Access Type: deny                           //restricting the data
Execution Type: groovy                 
Condition :
				
                import com.helicalinsight.adhoc.metadata.GroovyUsersSession;
                def evalCondition() {
                    String userName = GroovyUsersSession.getValue('${role}.name');
                    if (userName. contains( 'ROLE_USER')) {
                        return true        
                    } else {
                        return false
                    }        
                }

 

  1. Hiding a column from a Profile : Here we will learn how you can restrict certain column from profile. 
    Expression Name : Hiding column from a Profile  //can be any name
Entity Name : Column1                        //this is selected column. 1 or more tables can be added
Expression Type: column                      //since we are selecting column
Access Type: deny                           //restricting the data
Execution Type: groovy                 
Condition :
			
                import  com.helicalinsight.adhoc.metadata.GroovyUsersSession;
                def  evalCondition() {
                    String profileName = GroovyUsersSession.getValue('${profile[\'ProfileName\']}');
                    if (profileName.equalsIgnoreCase("'ProfileValue'")) {
                        return true
                    }
			else {
                        return false
                    } 
                }

 

Data Level / Row Level Condition

 

    1. Limiting Data from a User : Here we will learn how to show user specific data to a user. 
      Expression Name : Limit Data Display from User   //can be any name
Entity Name : Column1   //this is selected column. 1 or more tables can be added
Expression Type: column   //since we are selecting column
Access Type: grant        // granting specified column mentioned in the entity name
Execution Type: groovy
Condition :
				
                import com.helicalinsight.adhoc.metadata.GroovyUsersSession;
                def evalCondition() {
                        String userName = GroovyUsersSession.getValue('${user}.name');
                        if (userName.equalsIgnoreCase("'User A'")) {
                            return true
                        } else {
                            return false
                        }        
                }


Filter : 
				def evalFilter() {
                    return "meeting_details.meeting_impact = 'Finalized'"
}

 

    1. Limiting Data from a Role : Here we will learn how to show specific data based on the roles assigned to a user. 
      Expression Name : Limit Data Display from a Role   //can be any name
Entity Name : Column1   //this is selected column. 1 or more tables can be added
Expression Type: column   //since we are selecting column
Access Type: grant        // granting specified column mentioned in the entity name
Execution Type: groovy
Condition :
			
                import com.helicalinsight.adhoc.metadata.GroovyUsersSession;
                def evalCondition() {
                    String userName = GroovyUsersSession.getValue('${role}.name');
                    if (userName. contains( 'ROLE_USER')) {
                        return true        
                    } else {
                        return false
                    }        
                }

Filter : 
				def evalFilter() {
                    return "employee_details.address = 'Bhubaneshwar'"
}

 

    1. Limiting Data from a Profile : Here we will learn how to show specific data to a user based on the profile it is assigned. 
      Expression Name : Limit Data Display from a Profile   //can be any name
Entity Name : Column1   //this is selected column. 1 or more tables can be added
Expression Type: column   //since we are selecting column
Access Type: grant        // granting specified column mentioned in the entity name
Execution Type: groovy 
Condition :
				
                import  com.helicalinsight.adhoc.metadata.GroovyUsersSession;
                def  evalCondition() {
                    String profileName = GroovyUsersSession.getValue('${profile[\'ProfileName\']}');
                    if (profileName.equalsIgnoreCase("'ProfileValue'")) {
                        return true
                    }
			else {
                        return false
                    } 
                }
            
Filter : 
			def evalFilter(){
                    return "employee_details.employee_id between 1 and 12";
}

 

  1. Limiting Data from Organization : Here we will learn how to show specific data to a user based on the Organization it belongs. 
    Expression Name : Organization Name Expression	   //can be any name
Entity Name : Column1   //this is selected column. 1 or more tables can be added
Expression Type: column   //since we are selecting column
Access Type: grant        // granting specified column mentioned in the entity name
Execution Type: groovy 
Condition :
				
                import com.helicalinsight.adhoc.metadata.GroovyUsersSession;
                def  evalCondition() {
                    String orgName = GroovyUsersSession.getValue('${org}.name');
                    if (!orgName.equals("'Null'")) {
                        return true
                    } else {
                        return false
                    }  
                }
            
Filter : 
			
            import com.helicalinsight.adhoc.metadata.GroovyUsersSession;
            def evalFilter() {
               String orgName = GroovyUsersSession.getValue('${org}.name');
               return "meeting_details.client_name=" + orgName;
            }

You can refer to the various kind of expressions which we can use while defining the metadata security expressions in this blog. Metadata Security Expressions

Note: Applying metadata security provides DATA Security. However, to make the reports, dashboards, and the metadata itself available to other users for viewing or editing purposes, the said resource needs to be shared with designated users. For more information on how to share resources (reports, dashboards, metadata and datasource) Refer this blog

Post your related queries on our Forum

You May Also Like

Helical Insight’s self-service capabilities is one to reckon with. It allows you to simply drag and drop columns, add filters, apply aggregate functions if required, and create reports and dashboards on the fly. For advanced users, the self-service component has ability to add javascript, HTML, HTML5, CSS, CSS3 and AJAX. These customizations allow you to create dynamic reports and dashboards. You can also add new charts inside the self-service component, add new kind of aggregate functions and customize it using our APIs.
Helical Insight’s self-service capabilities is one to reckon with. It allows you to simply drag and drop columns, add filters, apply aggregate functions if required, and create reports and dashboards on the fly. For advanced users, the self-service component has ability to add javascript, HTML, HTML5, CSS, CSS3 and AJAX. These customizations allow you to create dynamic reports and dashboards. You can also add new charts inside the self-service component, add new kind of aggregate functions and customize it using our APIs.
Helical Insight, via simple browser based interface of Canned Reporting module, also allows to create pixel perfect printer friendly document kind of reports also like Invoice, P&L Statement, Balance sheet etc.
Helical Insight, via simple browser based interface of Canned Reporting module, also allows to create pixel perfect printer friendly document kind of reports also like Invoice, P&L Statement, Balance sheet etc.
If you have a product, built on any platform like Dot Net or Java or PHP or Ruby, you can easily embed Helical Insight within it using iFrames or webservices, for quick value add through instant visualization of data.
If you have a product, built on any platform like Dot Net or Java or PHP or Ruby, you can easily embed Helical Insight within it using iFrames or webservices, for quick value add through instant visualization of data.
Being a 100% browser-based BI tool, you can connect with your database and analyse across any location and device. There is no need to download or install heavy memory-consuming developer tools – All you need is a Browser application! We are battle-tested on most of the commonly used browsers.
Being a 100% browser-based BI tool, you can connect with your database and analyse across any location and device. There is no need to download or install heavy memory-consuming developer tools – All you need is a Browser application! We are battle-tested on most of the commonly used browsers.
We have organization level security where the Superadmin can create, delete and modify roles. Dashboards and reports can be added to that organization. This ensures multitenancy.
We have organization level security where the Superadmin can create, delete and modify roles. Dashboards and reports can be added to that organization. This ensures multitenancy.
We have organization level security where the Superadmin can create, delete and modify roles. Dashboards and reports can be added to that organization. This ensures multitenancy.
We have organization level security where the Superadmin can create, delete and modify roles. Dashboards and reports can be added to that organization. This ensures multitenancy.
A first-of-its-kind Open-Source BI framework, Helical Insight is completely API-driven. This allows you to add functionalities, including but not limited to adding a new exporting type, new datasource type, core functionality expansion, new charting in adhoc etc., at any place whenever you wish, using your own in-house developers.
A first-of-its-kind Open-Source BI framework, Helical Insight is completely API-driven. This allows you to add functionalities, including but not limited to adding a new exporting type, new datasource type, core functionality expansion, new charting in adhoc etc., at any place whenever you wish, using your own in-house developers.
It handles huge volumes of data effectively. Caching, Pagination, Load-Balancing and In-Memory not only provides you with amazing experience, but also and does not burden the database server more than required. Further effective use of computing power gives best performance and complex calculations even on the big data even with smaller machines for your personal use. Filtering, Sorting, Cube Analysis, Inter Panel Communication on the dashboards all at lightning speed. Thereby, making best open-source Business Intelligence solution in the market.
It handles huge volumes of data effectively. Caching, Pagination, Load-Balancing and In-Memory not only provides you with amazing experience, but also and does not burden the database server more than required. Further effective use of computing power gives best performance and complex calculations even on the big data even with smaller machines for your personal use. Filtering, Sorting, Cube Analysis, Inter Panel Communication on the dashboards all at lightning speed. Thereby, making best open-source Business Intelligence solution in the market.
With advance NLP algorithm, business users simply ask questions like, “show me sales of last quarter”, “average monthly sales of my products”. Let the application give the power to users without knowledge of query language or underlying data architecture
With advance NLP algorithm, business users simply ask questions like, “show me sales of last quarter”, “average monthly sales of my products”. Let the application give the power to users without knowledge of query language or underlying data architecture
Our application is compatible with almost all databases, be it RDBMS, or columnar database, or even flat files like spreadsheets or csv files. You can even connect to your own custom database via JDBC connection. Further, our database connection can be switched dynamically based on logged in users or its organization or other parameters. So, all your clients can use the same reports and dashboards without worrying about any data security breech.
Our application is compatible with almost all databases, be it RDBMS, or columnar database, or even flat files like spreadsheets or csv files. You can even connect to your own custom database via JDBC connection. Further, our database connection can be switched dynamically based on logged in users or its organization or other parameters. So, all your clients can use the same reports and dashboards without worrying about any data security breech.
Our application can be installed on an in-house server where you have full control of your data and its security. Or on cloud where it is accessible to larger audience without overheads and maintenance of the servers. One solution that works for all.
Our application can be installed on an in-house server where you have full control of your data and its security. Or on cloud where it is accessible to larger audience without overheads and maintenance of the servers. One solution that works for all.
Different companies have different business processes that the existing BI tools do not encompass. Helical Insight permits you to design your own workflows and specify what functional module of BI gets triggered
Different companies have different business processes that the existing BI tools do not encompass. Helical Insight permits you to design your own workflows and specify what functional module of BI gets triggered