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

[pljsoftware]

Hi Plaidsanne,

I had same problem when the application run on server. The problem is that the server run the application with an user that don't have authorization, this exclude all data in published application.

The solution is to load data from "ServerUser" with star is *; or star is ALL;

I prefere to creare a new field NEWNTNAME in Section Access with same NTNAME value and after use only NEWNTNAME in AuthorizationTable as key to filter user data.

So in excel I have this fields:

ACCESS	NTNAME	NEWNTNAME
ADMIN	SERVER\ADMINQV11V1PR	ALL
ADMIN	MY-PC\MYSELF	MY-PC\MYSELF

so you can create a new table where put you filter fields

NTUSERNAME	F1	F2	IS_AM_USER	IS_RELOAD_DOCUMENT_ENABLED
ALL	<ANY>	<ANY>	Y	Y
MY-PC\MYSELF	<ANY>	<ANY>	Y	Y

I hope this help you.

Regards

Luca Jonathan Panetta

[#4]

Hi Thank You,

So are you saying I need to have under

Star is ALL;

SECTION ACCESS;

                                                  This (From Excel)

ACCESS	NTNAME	NEWNTNAME
ADMIN	SERVER\ADMINQV11V1PR	ALL
ADMIN	MY-PC\MYSELF	MY-PC\MYSELF

SECTION APPLICATION;

                                                  This (From another Excel)

NTUSERNAME	F1
ALL	<ANY>
MY-PC\MYSELF	<ANY>

Because I normally reserve the excel under Section Application for Object and Tab Level Security and Restrict data in the Section Access portions of excel.

Sorry I am new to Section Access.

[hic]

I wouldn't use Star is ALL. Your approach with <ANY> should work. Why it doesn't, I don't know - I haven't seen your app.

So, I suggest you look at Generic keys and if you still cannot get it to work, post a question on the forum in its own thread.

HIC

[pljsoftware]

Hi Henric,

I explained the problem in my previous post.

The problem is that the server run the application with an user that don't have authorization, this exclude all data in published application. This because when QlikView server run the application by SERVER\ADMINQV11V1PR and save the application all the configuration permission for all user are lost and mantained only for SERVER\ADMINQV11V1PR.

I lost 3 days to solve it.

Best Regards

Luca Jonathan Panetta

[mborsadw]

Henric,

In one of the threads (section access and application access) you had:

Example:

This table you would interpret as "A can see the data if (Shop=X and Product=ANY) or (Shop=ANY and Product=Y)". But QlikView interprets it the other way around: "A can see data if (Shop=X or Shop=ANY) and (Product=ANY or Product=Y)". So it won't work. A will be able to see everything.

This blog explains how to achieve this using concatenation of the field values and a Bridge which is great!

Is there a way to support wildcards such as below:

USER	%AUTHID
A	X|P*
A	S*|Y

In query terms it would be something like :

(shop=X and product like 'P%') or (shop like 'S%' and product = Y)

Regards,

Mufaddal

[#8]

Hi Henric,

I've been trying to work through your Technical Brief on Generic Keys, but am hitting a bit of a roadblock with performance.  I have 900,000 products spread over roughly 2,000 suppliers.  I've built the generic key in a consolidated fact table (which contains facts at both product and supplier level) and built the product dimensional link table with an Apply Map to give a composite of Product ID | Supplier ID as the Key and %Key fields, and then replicated for each <ANY> scenario (ie. ANY product for a Supplier ID, ANY product and ANY Supplier ID), thereby giving me a Dimensional Link table of 3m records.  The problem I have is that the performance on the application is now horribly slow when trying to drill through the various levels of the hierarchy.

When I've re-read your brief, you have a similar scenario in your 1st example (with Product ID and Supplier ID), but this doesn't appear in the 3rd scenario dealing with multiple fact tables, which unfortunately is the exact scenario I'm trying to tackle, and with multi-granularity of facts.  We have budgets at the supplier level, but need to also look at the transactional situation at the product level, as well as the current stocking situations at product level.

I am therefore curious how you would expand your brief to incorporate further hierarchical tables into the model, for instance a product / product supplier / in-house product supplier categorisation?  I wondered about actually taking the supplier list and creating this as a separate dimension from the product dimension, and adding a further Dimensional Link table for the suppliers, but I'm worried that this is then going to lose any possible linkages with the product data itself.

Any advice you could give would be greatly appreciated.

Dave.

[francescopuppin]

Hi David,

I have built in these days a solution of Section Access applied to a multi-fact data model with different granularities, which sounds somehow similar to your case. I am currently building a small mock up with a "one-to-many" (1-n) approach, to be able to share it with the Community.

In my case I have Sales and Pending Orders at the lowest granularity (ClientID, ProductID, Date) while Budget and Forecast are of course less granular (they are both at Client Country, Product Category and Month). I could easily add in my small mock up the Stock at product level, to make it even more similar to your case.

It is obvious that you have some very large data volumes, and this cannot be changed.. But in fact this could be a good use case, to test if my approach is improving your performance, as I hope.

To say it in words, I have built an Authorization Bridge Table (ABT) by making 4 concatenated statements of Load Resident against the Link Table (LT). And because the Link Table has a unique identifier, the association between ABT and LT is 1-n instead of n-n. I am probably using a bit more of RAM, because I am adding to the memory an additional Symbol Table at the lowest granularity, but I believe that the CPU performance is having a benefit from having a 1-n instead of n-n. This is all to be proven, but it is probably worth trying 😉

Please let me know your impressions. I will build the small mock up as soon as I can.

Have a nice day

Francesco

[JonasValleskog]

Hi David,

Have you ensured that your security related tables all reside inside the Section Access scope of the application? I.e. your bridge table between your fact and your user security table could in theory all live in this scope as long as you don't retain any business data in there - i.e. if you only use it for your security "plumbing". I do not recommend that you retain a flattened product x supplier many to many bridging table inside your main model - let the dimensions live separately as they normally would hanging off your fact table one by one by their respective primary keys instead and only build your product -> any supplier and supplier -> any product key maps in between the fact and the security table AND keep that bridge in the Section Access scope. As long as your logic is correct - users will be associated with the right products and suppliers indirectly via the fact this way - assuming that you are happy to only link users to products and suppliers that actually has at least one fact record to associate them by - else it can get a bit trickier, yet it would be solvable with some further modelling work. I'll leave that topic be for now, else this will turn into an essay....

I may be wrong here, but my working hypothesis is that the Section Access scope tables only assist in reducing the scope of the population of data that you have access to at time of login - after that point, there shouldn't be a need for the engine to retain any state vectors or similar dynamic content for the data contained in any access related tables and thus it should in theory not impact performance if you link a user to a defined set of fact records via one line or 3 million lines. Happy to stand corrected, but from empirical evidence on a client site with a pretty large data set and a complex security model, this certainly seemed to be the case. If you think about it - security restrictions are not dynamic. They are hard rules that do not change based on user interactions. Either a user can see a piece of data - or they can't. My educated guess is that the mapping of users to what rows they can see in the data table(s) is pre-computed post refresh as a static map of row pointers. This would also explain why the little used real-time dynamic data update feature didn't work with Section Access... But I digress.

Curious to hear if you get around your performance challenge, feel free to reach out if you get stuck. QlikView/Sense data modelling and performance tuning are two specialisms of mine.

Best regards

Jonas

[Anonymous]

hic‌ Thanks for solution its very useful, i had implemented the same but than i had to also use Hierarchy section access(tree) with this which is bit difficult, how would that be possible.

Also how different is this solution than only using Section Access with listed columns on which data reduction is required, without creating Generic Key.

Regards,

ASP

[#12]

Hi hic‌, this has helped me a lot. However, I have a problem. I'm using 12 fields to reduce data, and thousands of rows. So it's hard the authorization bridge table. Is there any other way to do it?