Qlik Community

Qlik Design Blog

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

Employee
Employee

Basics for complex authorization

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.

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 ;

    Authorization table.png
  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 ;

    Authorization bridge.png
  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

28 Comments
shaik_basha
Contributor III

Hi,

  your give explination on secton access,i have one doubt in section access,

supose in product table i ahve 50 categories,i want to give 50 products to one user at condition how i want to prepare authentication table key and transaction table key.

Regards

0 Likes
646 Views
cesaraccardi
Valued Contributor

Hi Henric,

Very nice explanation, thank you for sharing! I currently have a similar requirement in the project I am working and another thing my client asked is to have some excluding access too, is this something that could be implemented with the use of the generic keys (maybe groups like <NOT A> or something like that) or would you suggest another approach?

Regards,

Cesar

0 Likes
646 Views
MCampestrini
Valued Contributor

Hi Henric

Thanks for the explanation. We're just starting deployment of applications and this is what I was missing to deploy it.

0 Likes
646 Views
Not applicable

Ive encountered a strange issue when trying to deploy this approach. USERID, or open a reloaded document in my desktop qlikview with section access restrictions on my NTNAME, straight from the server and the restrictions have applied but if I try and view the document through accesspoint it says I dont have access to the document with strict exclusion set. If I disable strict exclusion I can open it on accesspoint but see all records. Ive tried both upprcase NTNAME, ACCESS rights as USER and ADMIN but it just will not open. Anyone encountered this?  (11.20.12451.0) Windows 2008 Server 64 bit. Active Directory?

0 Likes
646 Views
arniewolff
New Contributor III

John,

Yes. I've experienced (am experiencing) the same issue. The ADMIN setting grants you access to the entire file in desktop mode, but the strict exclusion setting in AccessPoint is not allowing you access because you aren't assigned rights to the file.

Since AccessPoint treats each user as a USER and you have Strict Exclusion set, you need to make sure you are assigning yourself rights explicitly. It should work if you: add at least one record for yourself (or your group) in the Section Access authorization table that has at least one assignment for each column - blanks don't seem to work to grant all access, * allows only other assigned values in the table; then (if you're using an additional Section Application table) make sure that you have at least one record in there for yourself with at least one assignment for each column (and remove any rows with blanks).

That works for me. The painful part seems to be granting admin-type access. I think I need to add rows for every value (business unit in my case) to the authorization table so I can use * as needed.

Let me know how it goes. Good luck!

Arnie

0 Likes
646 Views
joeybird
Contributor II

Hiya

have you written another blog with details of omit, "region" ..."product" ...and say e.g a third column "category" please?

0 Likes
646 Views
Employee
Employee

I haven't written about "OMIT", but I have written other ones about using multiple reducing fields. See e.g. Data Reduction Using Multiple Fields

HIC

646 Views
gauthamchilled
New Contributor III

Hi hic

I followed your suggestion for implementing complext authorization. I have a issue discussed here extended section access help Do you have a better soluton to address this issue?

thanks

Gautham

0 Likes
646 Views
Not applicable

Hi,

I am trying to use <ANY> for in ADMIN user in my section ACCESS TABLE. When I run my Section Access table any user with <ANY> as a key is denied access. Is there any way around this?

0 Likes
646 Views
Employee
Employee

That should work. So I don't know why it doesn't work. Post a separate thread with a sample document.

0 Likes
646 Views
pljsoftware
Contributor III

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:

   

ACCESSNTNAMENEWNTNAME
ADMINSERVER\ADMINQV11V1PRALL
ADMINMY-PC\MYSELFMY-PC\MYSELF

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

    

NTUSERNAMEF1F2IS_AM_USERIS_RELOAD_DOCUMENT_ENABLED
ALL<ANY><ANY>YY
MY-PC\MYSELF<ANY><ANY>YY

I hope this help you.

Regards

Luca Jonathan Panetta

0 Likes
646 Views
Not applicable

Hi Thank You,

So are you saying I need to have under

Star is ALL;

SECTION ACCESS;

                                                  This (From Excel)

ACCESSNTNAMENEWNTNAME
ADMINSERVER\ADMINQV11V1PRALL
ADMINMY-PC\MYSELFMY-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.

0 Likes
646 Views
Employee
Employee

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

0 Likes
646 Views
pljsoftware
Contributor III

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

646 Views
mborsadw
New Contributor II

Henric,

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

Example:Several.png

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
AX|P*
AS*|Y

In query terms it would be something like :

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

Regards,

Mufaddal

0 Likes
646 Views
Not applicable

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.

0 Likes
646 Views
francescopuppin
New Contributor III

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

0 Likes
646 Views
jonascbi
New Contributor III

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

0 Likes
646 Views
anwar_qlik
New Contributor III

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

0 Likes
646 Views
Not applicable

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?

0 Likes
646 Views
YoussefBelloum
Esteemed Contributor

Thank you. very useful, very clear !

0 Likes
646 Views
vchuprina
New Contributor II

Henric,

Thanks for solution its very useful. What you can recommend to do in case if we should reduce data based on ten fields.

0 Likes
646 Views
mpbtejada
New Contributor III

Can I get some help with this implementation of NTNAME as well.

Encountered the same case where I can open in Desktop but could not in Accesspoint.

Already tried adding STAR is * and checked that there are no blanks in the section access column but still could not make it to work in Accesspoint.

0 Likes
646 Views
ag732312
New Contributor II

Hi hic,

Do you have any real example of this type security?

I would like also to know how can this be applied with a third reduction field?

Thanks in advance

0 Likes
646 Views
rammuthiah
Contributor II

Hi,

Can you explain on how to implement Data Reduction using 4 fields in Qlik Sense?

Region,

pbu,

division and

empcode

0 Likes
646 Views
nesawant
New Contributor

Hi Luca,

I am facing same issue. My complex authorization with bridge table works on desktop but when I run it from server it says you do not have access.

How do I resolve this using single section access file?

Best Regards,

Neha

0 Likes
646 Views
dravitag
New Contributor

Hi Henric,

My question is how can i use this complex authorization with more than 2 fields in section access. And with one field having condition of "All except one" like you explained in this thread Section access - exclude just one value!

So, i need combination of both of these solutions: Section access on 4 fields and 1 field with exclude just one value.

Appreciate your help here. Thanks

0 Likes
646 Views
krishgk_ispot
New Contributor

@Henric Cronström@cesaraccardi Did you figure out the solution for excluding the data using <NOT A>  or <NONE> (as shown below) ?

section access;

load * inline [

ACCESS, USERID, CUSTOMERID_ORDER,CUSTOMERID_SALES, OMIT

USER, USER1, 1, 2,

USER, USER2, 2,<NONE>,

USER, USER3,<NONE>,3

];


Also i would like implement the security on a CustomerID field from dimension  table but restrict different CustomerID's from Order and Sales Tables.

Note: CUSTOMERID_ORDER and CUSTOMERID_SALES are alias of CustomerID field from Customer table.


Is it possible to restrict the data this way using single dimension filed on multiple fact tables differently?

0 Likes
646 Views