Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
Henric_Cronström

I recently wrote a blog post about authorization using Section Access and data reduction. In the example, a person was associated with a country and this entry point in the data model determined whether a record was visible or not: Records associated with the country were visible. “Country” was the reducing field.

 

Selection.png

 

The data reduction was made using row-level security. But there are other ways of limiting access to data. This post is about how you limit access to the data:

 

Row-level access: You have a reducing field that determines whether a user can see a specific piece of data. If you use Country as reducing field and the user is allowed to see ‘Spain’, this will mean that only rows associated with Spain will be visible: E.g. sales transactions to customers in other countries will not be visible.

 

Aggregation-level access: This is similar to the above, however with the difference that all data are in principle visible but the aggregation level changes depending on country: A user that is allowed to see ‘Spain’ will see the detailed information about Spain, but only high-level aggregated information about other countries. For other countries detailed information will be hidden.

 

Column based access: Instead of limiting per row, you can limit per column. Here you can define that only some users are allowed to see specific fields, typically fields like Salary or Bonus.

 

Object based access: You can also limit access to a specific sheet, graph or pivot table depending on which user it is.

 

An application can use a combination of the four different methods.

 

Both Section Access and the loop-and-reduce in publisher use row-level access to allow one single (master) file to be used in different security scopes. It is by far the best way to limit access to data, and should be the one you normally aim for.

 

It is difficult to achieve aggregation-level access within one single application, so it is better to solve this problem using two applications: One with detailed data that you reduce using a reducing field, and a second unreduced with aggregated data for all countries.

 

The column-based access can be achieved using two applications, one that includes the sensitive fields and the other that doesn’t. It can also be achieved in one single application using the OMIT field in Section Access.

 

Finally, the object based access: This method has in my mind very little to do with security: If a chart is hidden for a specific user, he can still see the same data through other objects. Or even worse – if you allow collaboration, he can create an object that shows the same thing. A show condition could be convenient to use anyway, but it is a poor tool for security.

 

Bottom line: If you want security, you should use Section Access or the loop-and-reduce of the Publisher. You should also consider having your data in several applications. But you should not use show conditions for security purposes.

 

HIC

 

Further reading related to this topic:

A Primer on Section Access

Tips and tricks for section access in Qlik Sense (2.0+)

29 Comments
chriscammers
Partner
Partner

Thanks!

That confirms what we suspected. We're going to create the authorization field. which I think will solve the problem.

Chris

0 Likes
3,888 Views
chitemerere
Specialist
Specialist

Hi HC

How does one implement Aggregation-level access?  Would give an example on how to do it.  Will need someone to have access to their own territory information in addition to the whole market in order to determine their performance relative to the market.

Regards.

Chris

0 Likes
3,888 Views
chriscammers
Partner
Partner

I was thinking about this and I know there are a ton of details to work out but basically I would create a concatenated fact table with separate rows for the detail and the summary. Then you have to build your user table so the user has access to the summary rows for the territories he does not belong to and the detail for the territories he does belong to. I'd suggest a field that concatenates the fact type and the territory value.

0 Likes
3,888 Views
chitemerere
Specialist
Specialist

Many thanks Chris for your response, most appreciated.  I am a beginner in QV so implementation of your suggestion would be difficult as i can not comprehend your recommendation above.

Regards

0 Likes
3,888 Views
chriscammers
Partner
Partner

I baked up a quick sample, I did not implement the section access but selection of a user Id will simulate the effect of data reduction. I cannot seem to add a file here so here is a link to my dropbox where you can download it.

Dropbox - AggregateSecurity.qvw

I fear you will not be able to download it so here is the load script.

UserSecurity:

Load * Inline [

USERID,REGION_FACT

NORTH_USER,NORTH_DETAIL

NORTH_USER,SOUTH_AGGREGATE

SOUTHUSER,SOUTH_DETAIL

SOUTHUSER,NORTH_AGGREGATE

MANAGER,NORTH_DETAIL

MANAGER,SOUTH_DETAIL

];

//Build the fact table

//Add the aggregate data

FactTable:

Load * Inline [

REGION_FACT, Region, Fact Type, Sales Amount

NORTH_AGGREGATE, North, Aggregate, 10000

SOUTH_AGGREGATE, South, Aggregate, 15000

];

//Add the Detail Data

Concatenate(FactTable)

Load * Inline [

REGION_FACT, Region, Fact Type, Invoice, Sales Amount

NORTH_DETAIL, North, Detail, 1234, 2500

NORTH_DETAIL, North, Detail, 1235, 2500

NORTH_DETAIL, North, Detail, 1236, 2500

NORTH_DETAIL, North, Detail, 1237, 2500

SOUTH_DETAIL, South, Detail, 1238, 2500

SOUTH_DETAIL, South, Detail, 1239, 2500

SOUTH_DETAIL, South, Detail, 1230, 2500

SOUTH_DETAIL, South, Detail, 1231, 2500

SOUTH_DETAIL, South, Detail, 1232, 5000

];

0 Likes
3,907 Views
chitemerere
Specialist
Specialist

Hi Chris

Thanks for the input.  i have managed to download the QVW.  I will work on it and advise accordingly.  We are on different time zones so there will be delays in response.

Once again, thank you very much and most appreciated.

Regards.

0 Likes
3,907 Views
kashjaniqlik
Contributor III
Contributor III

Hi Henric

Thanks for the useful article.

We are currently developing an MI dashboard for our clients and the requirement is that high level numbers are shown globally to all users although the detailed data is restricted based on the user countries. So if I'm part of UK, I will see other countries high level data but can only see detailed data for UK.

Our client requires both sheets (high level and detailed) to be part of one solution i.e. one QVW.

How would you go about applying section access in this situation?

Many Thanks for your help

Kash

0 Likes
3,907 Views
arethaking
Creator II
Creator II

Hi HIC,

Please give an example to

Aggregation-level access: This is similar to the above, however with the difference that all data are in principle visible but the aggregation level changes depending on country: A user that is allowed to see ‘Spain’ will see the detailed information about Spain, but only high-level aggregated information about other countries. For other countries detailed information will be hidden.

0 Likes
3,907 Views
tanelry
Partner
Partner

Aggregation-level access:

One way to achieve this is to create two data sets in the fact table: Aggregated and Detailed.

Then create a link table to map Aggregated data to all users, but Detailed data to country-specific user.

As a result, data reduction should only apply to Detailed data set.

For example, data model:

SA_Datamodel.png

And data in the tables:

SA_Data.PNG

Both CountryLink and Facts table contain two data sets: Aggregated and Detailed data.

The LinkType field is technically useless, but I brought it here for better understanding.

SalesAmount field has been aggregated by Country and Date, while SalesAmount_Detail field is also available by SalesPerson and Customer.

So, for output you also need to create two measures:

  • sum(SalesAmount) - for country level comparisons with all countries
  • sum(SalesAmount_Detail) - for detailed analysis with user specific country

Regards,

Tanel

3,907 Views