Basics for complex authorization

[hic]

Authorization is about determining which data a user is allowed to see. QlikView has several different ways by which you can reduce the data so that the user only gets to see the data he is allowed to see.

[hic]

In the simplest case, the authorization table defining who-gets-to-see-what is just a two column table linking user names to e.g. regions. But sometimes you need a more complicated logic. It could be that you have users that are allowed to see all products but just within one region, and at the same time all regions but just for one product. In other words, you want to make the reduction in several fields with the possibility of OR-logic.

QlikView can do this and here’s how you do it:

1. Create an authorization table by loading the authorization data into QlikView after concatenating the reducing fields into one single generic authorization key:
   
        Load USER, REGION &'|'& PRODUCT as %AuthID From AuthTable ;
   
   
   
   
2. Create an authorization key in the table with the most detailed transactions:
   
        Load *, Region &'|'& Product as AuthID From OrderDetails ;
   
   If you don’t have all the necessary keys in the table, you can fetch fields from other tables using Applymap. See more about Applymap here:
   
   
3. Create an authorization bridge table linking the two above tables. Since the %AuthID field can contain generic symbols such as '<ANY>', several load statements are needed to create the bridge table:
   
        Load Region &'|'& Product as AuthID, Region &'|'& Product as %AuthID  From OrderDetails ;
        Load Region &'|'& Product as AuthID, Region &'|'&'<ANY>' as %AuthID  From OrderDetails ;
        Load Region &'|'& Product as AuthID, '<ANY>'&'|'& Product as %AuthID  From OrderDetails ;
        Load Region &'|'& Product as AuthID, '<ANY>'&'|'&'<ANY>' as %AuthID  From OrderDetails ;
   
   
   
   
4. Reduce the file on the USER field using either Section Access or QlikView Publisher.

Using the above method you can create quite complex security models. For instance, you can use generic symbols also for product groups. Read more about generic keys in this Technical Brief.

Good luck!

HIC

Further reading related to this topic:

A Primer on Section Access

Data Reduction Using Multiple Fields

Authorization using a Hierarchy

[axnvazquez]

[DELETE PLEASE]

[axnvazquez]

Hi,

At first I had 3 dimensions to make all the posible combinations with <ANY>, but now I have 5, and I don't want to leave anyone behind, so is there a way to create all the posible combinations in a more reliable process ? 

Thanks in advance!

[barnabyd]

G'day,

Five dimensions in Section Access seems a bit excessive but I have thought of two solutions. Unfortunately, my generic code solution requires a number of cross joins and it would take quite a while to explain how it could work and would make this a very long post. It would also be difficult to debug. I think, for safety, you should stay with the hard-coded method as described by Henric. However, you might want to look up Grey Code as a good way to arrange your statements. I find Grey Code makes it easy to see if you've missed a combination.

I hope this helps.

Cheers,

Barnaby.

[axnvazquez]

Thank you @barnabyd for your answers, I will look up the Grey Code.

What do you suggest of using instead of this for a Section Access based on 5 Dimensions ?? Right now I have to give access based on division, project, country, market and operation ... and of course, one person can have multiple access.

[barnabyd]

G'day @axnvazquez,

My comment about 5 dimensions being excessive is more about the business decision to implement such a complex security model. Whenever I have been involved in these discussions, I like to point out that the more complex security models lead to more administrative overhead. Mostly, the business will accept this and will minimise their requirements when they realise there is a trade-off. That's my experience anyway. The most complex environment I worked with was a four level business hierarchy on a single dimension and that required a large amount of effort from the operations person and myself as the developer.

With your five dimensions, can any of them roll up to any others? For example, is an operation always within a single division? If so, then you don't need the division dimension - it will roll up from the operation.

I hope these are useful comments.

Cheers,

Barnaby.

[Gal123]

We implemented the offered solution and it got the job done.

However, I did encounter with an issue tring to load the section access from a QVD file instead of the excel file.

It appears as if the %AuthID field cannot be read from a QVD in section access but can be loaded  outside the section access part with no issue.

I tried to add Upper and Where 1=1 as suggested in here:

https://support.qlik.com/articles/000002500

But still failed (I'm using Qlik SaaS if that matters).

Any suggestion?

[marcus_sommer]

Are you sure that the %AuthID from both sources contains absolutely the same data? Just load in a dummy-app something like:

t: load %AuthID, ReduceField, 'QVD' as Source from YourQVD;
  load %AuthID, ReduceField, 'Excel' as Source from YourExcel;

and then looking on it per table-box or maybe within a pivot with Source as horizontal dimension. If you hadn't a single ID row per ID with the two sources else two rows - the content is different. And similar with the ReduceField and so on. 

[Gal123]

It's the same field and have the same values (checked). It has something to do with the way section access implement QVD files.

[marcus_sommer]

The source itself is quite regardless unless specialities like the optimized load (which you had considered like mentioned) or a different NULL handling (a QVD could contain NULL but not Excel because there is a missing value just EMPTY but not NULL).

Further differences could occur if there is another load-order applied and/or different transformations in the load-statements - each slightest difference could have an impact.

Beside this - equally looking values may not be identically, for example in regard to numbers and strings and/or any kind of spaces and special chars. Therefore you may need a more careful look to which data are really there.

[oriolmarti]

Thanks!! it's really useful!