It has been 7 years since the QlikView for Developer's Cookbook was published!
The book, which was very much targeted at QlikView developers, is still available. Just for fun, I thought that it might be interesting to write a series of posts looking back at some of those recipes and seeing how they could be recreated in Qlik Sense Business.
In this post, I'll look at the recipe on Section Access Gotchas. In Qlik, Section Access is the area of script where we connect users to the data to allow row-level security. It is very powerful and relatively easy to implement for many typical use cases.
Before diving in, it is worth pointing out that Qlik Sense Business has a great feature, missing from QlikView when I was writing the Cookbook, that means if a Section Access script would result in the locking out of the developer, then tags the reload as unsuccessful and won't save the faulty setup. This will help with a lot of Section Access issues!
In the recipe I showed a number of different issues. Starting off with this load script:
Section Access;
Access:
LOAD * INLINE [
ACCESS, USER.EMAIL, Link
ADMIN, stephen.redmond@notmymail.fake, *
USER, user1@notmymail.fake, US
USER, user2@notmymail.fake, UK
USER, user3@notmymail.fake, Fr
];
Section Application;
Sales:
Load * Inline [
Link, Country, Sales
US, USA, 1000
UK, United Kingdom, 800
Fr, France, 750
De, Germany, 940
];
Now, when I login as the Admin, I get to see all the data as expected. However, when I login as user1, I still see all the data when I was expecting to only see US data! What is going on?
Well, a simple rule on the Section Access tables is that all the fields must be in capital letters. Simple fix so:
Section Access;
Access:
LOAD * INLINE [
ACCESS, USER.EMAIL, LINK
ADMIN, stephen.redmond@notmymail.fake, *
USER, user1@notmymail.fake, US
USER, user2@notmymail.fake, UK
USER, user3@notmymail.fake, Fr
];
Section Application;
Sales:
Load * Inline [
LINK, Country, Sales
US, USA, 1000
UK, United Kingdom, 800
Fr, France, 750
De, Germany, 940
];
Updating the Link to LINK in both tables and now User1 only sees US data. What of user3? If I log in as User3 I get Access Denied! This is because of a second Gotcha - any data field that links users to data and that contains letters, must only contain capital letters! So, "Fr" doesn't work. Simple fix:
Section Access;
Access:
LOAD * INLINE [
ACCESS, USER.EMAIL, LINK
ADMIN, stephen.redmond@notmymail.fake, *
USER, user1@notmymail.fake, US
USER, user2@notmymail.fake, UK
USER, user3@notmymail.fake, FR
];
Section Application;
Sales:
Load * Inline [
LINK, Country, Sales
US, USA, 1000
UK, United Kingdom, 800
FR, France, 750
DE, Germany, 940
];
Now, User3 can login and see the France data. All is great. Or is it?
But let us just quickly log back in as the Admin - we can only see US, UK and France! Where is Germany? This is a Gotcha whereby all of the users must be linked to all of the data or the security will remove access to the unlinked data. Interestingly, also having an admin user who is linked to none of the data will also work. It does look like the Admin user is linked to everything via the "*", but that isn't actually everything - it is only everything that is linked! Confused yet?
We can fix it like this:
Section Access;
Access:
LOAD * INLINE [
ACCESS, USER.EMAIL, LINK
ADMIN, stephen.redmond@notmymail.fake, NONE
USER, user1@notmymail.fake, US
USER, user2@notmymail.fake, UK
USER, user3@notmymail.fake, FR
];
Section Application;
Sales:
Load * Inline [
LINK, Country, Sales
US, USA, 1000
UK, United Kingdom, 800
FR, France, 750
DE, Germany, 940
];
Now, the Admin user can see everything. The "NONE" is arbitrary and can be any value as long as it doesn't exist in the data. Actually, I prefer to script things so that at least one user is linked to all of the data values. That way I don't have to rely on the "*".
As well as holding a Master's Degree in Data Analytics, Stephen Redmond is a practicing Data Professional of over 20 years experience. He is author of Mastering QlikView, QlikView for Developer's Cookbook and QlikView Server and Publisher.
Follow @stephencredmond
