Data Reduction – Yes, but How?

[hic]

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.

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+)

[#2]

Hello HIC,

This was fantastic blog.. I like very Much.

Thank you for Posting.

Regards,

Ramana

[Marcio_Campestrini]

Hi Henric

Thanks for clarify this point!

[Anonymous]

Thanks very Useful !!

Anant

[#5]

Henric,

Very well said.  "Security by obscurity" is never any good.  Similar to putting a padlock on a paper bag!

'Show conditions' have their place in Qlikview - for clarifying screens, removing extraneous objects etc until a condition dictates they should be visible, but not security.

[#6]

Thanks HIC,

What is the workaround for Object level security?

[hic]

There is no security in "object-level security", so what you should do instead is to use row-level security.

HIC

[#8]

Great Post Henric.  You touched on an area that I'm very interested in learning more about which is Aggregation Level Security.

A company I did work for wanted functionality that allowed Market Managers see how they compare to other Market Managers without having the ability to drill down into details.  I successfully implemented complex authorization but couldn't figure out what that next step would be for Aggregation level.  I can see this being handy for applications across multiple fields for comparison purposes.

I see you explained to achieve this by using two different applications but what would I do to link the high level data application with the reduced data application without having security compromise what I'm trying to accomplish?  Store the aggregated data in variables or QVDs and pull them in?

Thanks,

Ethan

[hic]

One straightforward way to link the two documents is to open document 2 from document 1 and transfer the selection.

But optimally you would of course want to have both detailed and aggregated numbers in the same app so that you can compare them. I need to investigate a little before I can give good answer, but my gut feeling is that you should create a loop in the publisher that creates one file per Market Manager with the appropriate aggregation.

HIC

[tanelry]

Isn’t Object based access still fine for special cases, as long as I restrict user freedom by unchecking security options in document and sheet properties, (add new sheets=no, add sheet objects=no, access object properties=no)?

Recently I had specific requirement to show full company-level data on a dashboard to everyone, but details and analytic charts had to be reduced by department - user should only see their own deparment(s).

I solved it using unlinked authorization table and additional SET analysis on restricted detail views’ expressions.

In data model there is a separated „UserDepartment“ table with two fields:

- USERID (linked to section access)

­- UserDepartment (department names, same as Department in transactional data but not linked to this field)

This way only UserDepartment table is being reduced by authorization, while transactional data is not affected by reduction.

On dashboard I use normal chart expressions, like sum(Amount). No drill to details there.

On detail reports I use SET analysis to reduce the data: sum({<Department=p(UserDepartment)>} Amount).

Of course document and sheet security options need to be restricted properly - basically uncheck all options.

[hic]

No, I don't think object based access is a good solution.

It is true that you can prevent the user from seeing unauthorized data, if you keep the file on a server, if you disable server objects and collaboration, if you have the appropriate object show conditions, if you make sure there are no other objects or sheets through which these data are visible, if you every time you make a change to the file make sure that none of the above has been broken, etc. I just think there are too many ifs for it to be a good, robust, manageable solution...

If you allow users to download the file, the data is still available for a malicious user - also if you have unchecked all security options in document properties. Remember that the file is not encrypted. I will not go into how you would hack such a file, but rest assured that a skilled malicious user is able to. So the file needs to stay on the server.

But my main concern is manageability. The file will be changed from time to time, and sooner or later the developer will forget to put the appropriate show condition into an object. So it will be very easy to publish confidential data by mistake.

HIC