Advertisements

Some more #Qlikview Section Access “Gotcha’s”

Section Definition  –  Section (access | application)

With the section statement, it is possible to define whether the subsequent LOAD and SELECT statements should be considered as data or as a definition of the access rights.

The access rights section is specified by ‘Section Access’

If nothing is specified, section application is assumed.  (i.e. a normal qlikview is in section application mode)


 

Quick Definition of Section Access

The logic for reduction is that the reduction values are applied as selections, and the possible data after the selection is the reduced data provided to the user.


Common Errors

Most Common Error:  not UPPERing ALL the fields.

HIC spells it out

 All fields in Section Access must be upper case. Hence, the reducing field must be in upper case also in the data. Use the Upper() function and name the fields in upper case.


ADMIN rights are only relevant for local documents! Documents opened on a Server are always accessed with USER rights.


Tip: Comment out the Section access to see how it is joining

Tip from Steve Dark – Use % on the field name so you can hide the prefix

Note i.

A * character in the OMIT means all listed OMIT fields will be unavailable

Note ii.

A * character in the ‘Reducing Field’ also only allows access to all listed fields

As noted by Steve Dark

The biggest gotcha with Section Access is the use of * for all values.  A user being admin does not actually grant them rights to any data, a * has to be used also.  The * however doesn’t give access to all rows, only all of the rows that are explicitly listed in Section Access.  If all values appear in the Section Access (ie. someone has explicit rights to each value) then all is good, if not you will need to add some dummy rows to the Section Access so each value is listed.


So one way of granting true * access is by using a SQL union ALL to the reducefield for the QVSERVICE account. The service account needs ADMIN access if you want to be able to reload it anyways 🙂

SECTION ACCESS;

AUTH:
LOAD "ACCESS",
 "NTNAME",
 "REDUCEFIELD";
SQL SELECT [ACCESS]
 ,[NTNAME]
 ,[REDUCEFIELD]
FROM [Test].[dbo].[SecurityTbl]
UNION ALL
select 'ADMIN', 'DOMAIN\QVSERVICE', REDUCEFIELD
from [Test].[dbo].[Fields];

OR you could join it in Qlikview

SECTION ACCESS;

AUTH:
LOAD "ACCESS",
 "NTNAME",
 "REDUCEFIELD";
SQL SELECT [ACCESS]
 ,[NTNAME]
 ,[REDUCEFIELD]
FROM [Test].[dbo].[SecurityTbl]

Load Distinct
'ADMIN' as ACCESS,
'DOMAIN\QVSERVICE' as NTNAME,
REDUCEFIELD
 Resident
 TheFields;

Note iii. Section access on the Desktop versus the Access Point (QVS)

From Peter Cammaert

  • ADMIN access in the Desktop: in QlikView Desktop, SA entries with ACCESS=ADMIN will always be able to get in, whatever the link values, data reduction results, strict exclusion setting, etc. This is a behavior that is Desktop-only. Probably configured this way to allow developers to debug…document security.
  • Strict exclusion: in the Access Point, everybody is a USER, even the IDs that have ACCESS=ADMIN. Since most ADMINS have an empty link field (for example the service account responsible for reloading the document should best be configured with an empty link field.)  Strict exclusion will always deny them access because there is no data left after reduction.

My Findings:

If you want to use strict exclusion with the Access Point, you need to have the service account that reloads it as an ADMIN in section access. If you want to be able to use a * you need to create dummy records for every value in your reduction field.

Interestingly enough, even if you grant NTFS permissions to view a qlikview on the access point, it still checks section access to decide whether it is viewable to the user or not. (Whether the user or group is in section access) They can still get accessed denied if they have a reduction that isn’t part of your reduction fields (or null).

References

Of course HIC wrote a primer on section access 🙂 I love all of his white papers!

This is another good article on more complex authorization

Section Access: Strict Exclusion https://community.qlik.com/docs/DOC-8562

https://community.qlik.com/docs/DOC-1853

https://community.qlik.com/docs/DOC-8633

https://community.qlik.com/blogs/qlikviewdesignblog/2014/06/09/methods-for-data-reduction

https://community.qlik.com/blogs/qlikviewdesignblog/2014/06/02/data-reduction-using-multiple-fields

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: