article QlikView Section Access Examples in Member Articles

QlikView Section Access Examples

Colin-Albert — Fri, 07 Oct 2022 13:22:04 GMT

The attached file SectionAccess.zip contains several example documents that show how Section Access can be used to restrict access to specific users and to specific data.

This is not a guide on how to use Section Access, but instead shows some of the common errors and how to avoid them.

An excellent guide to Section Access can be found here http://community.qlik.com/docs/DOC-1853

Marcus_Sommer has accumulated a comprehensive list of Section Access Resources in this document Section Access

These examples will require a licenced copy of QlikView to open them, so they not suitable for users with QlikView Personal Edition.

The example documents will ask for a username and password to open them and set the Section Access permissions. The following usernames and passwords can be used to open the documents and see the different functionality with Section Access. When changing from one user to another, you must close QlikView before you can open a document as another user.

UserID, Password

ADMIN, ADMIN

JOHN, JOHN

MARY, MARY

PETER, PETER

SIMON, SIMON

You can view the load script to see how the Section Access and data is configured.

Section Access Example 1

This document attempt to restrict access to a list of named users, but this fails as the section access field names are not set as UPPERCASE in the script.

Section Access Example 2

The app is now correct and prompts for a username and password before users can access the data.

Examples 1 & 2 use the Basic section access data from SectionAccess.xlsx

Access, UserID, Password

USER, USER, USER

ADMIN, ADMIN, ADMIN

USER, JOHN, JOHN

USER, MARY, MARY

USER, PETER, PETER

ADMIN, SIMON, SIMON

Section Access Example 3

The data is restricted so users can only access the Cost Centre data as listed in the Cost Centre column in Section Access.

Access, UserID, Password, CostCentre

USER, USER, USER, *

ADMIN, ADMIN, ADMIN, *

USER, JOHN, JOHN, A

USER, MARY, MARY, B

USER, PETER, PETER, C

ADMIN, SIMON, SIMON, A

Section Access Example 4

This adds extra DUMMY rows to the section access table so the * entries can now access all cost centres.

Access, UserID, Password, CostCentre

USER, USER, USER, *

ADMIN, ADMIN, ADMIN, *

USER, JOHN, JOHN, A

USER, MARY, MARY, B

USER, PETER, PETER, C

ADMIN, SIMON, SIMON, A

DUMMY, DUMMY, DUMMY, D

DUMMY, DUMMY, DUMMY, E

DUMMY, DUMMY, DUMMY, F

DUMMY, DUMMY, DUMMY, G

DUMMY, DUMMY, DUMMY, H

Note – you cannot open the document as user DUMMY because DUMMY is an invalid Access value, only ADMIN and USER entries are allowed to open the document.

Section Access Example 5

This adds Region to the restrictions as well as CostCentre, but the restriction on Region fails as the

region fieldname is not UPPERCASE.

Access, UserID, Password, CostCentre, Region

USER, USER, USER, *, *

ADMIN, ADMIN, ADMIN, *, *

USER, JOHN, JOHN, A, North

USER, MARY, MARY, B, South

USER, PETER, PETER, C, East

ADMIN, SIMON, SIMON, A, North

ADMIN, SIMON, SIMON, A, East

DUMMY, DUMMY, DUMMY, D, North

DUMMY, DUMMY, DUMMY, E, South

DUMMY, DUMMY, DUMMY, F, East

DUMMY, DUMMY, DUMMY, G, West

DUMMY, DUMMY, DUMMY, H

Section Access Example 6

The Region fieldname is uppercase, but the restriction on Region still fails as the region data is not UPPERCASE.

Section Access Example 7

The Region restriction now works by loading the region data into a second field named REGION_SA and forcing the data in REGION_SA to UPPERCASE.

Version3 - The Region field has been removed from the Section Access table. This fixes the error identified by kanhomcake (March 27 2014).

Hopefully this will help avoid some of the basic errors that can be made when implementing Section Access.

Colin Albert.

Re: QlikView Section Access Examples

kanhomcake — Thu, 27 Mar 2014 09:31:11 GMT

hi Collin

Nice to see your work! I have one question, are your sure having more than one data columns (COSTCENTRE and REGION_SA) in Section Access will work? I opened your Section Access 7 file with PETER user then I got Access Denied message.

From my little experience, I have never successfully made it, except tweaking it by combining two (or more) data columns into one field then it works.

If your posted solutions really work, it will be very helpful and much more convenient than what I have done. Please kindly share more!!!.

Thankssss

Re: QlikView Section Access Examples

Colin-Albert — Thu, 27 Mar 2014 10:30:12 GMT

Hi Wannita,

Thanks for pointing out the error.

The fix is to remove the Region field from the Section Access part of the script and just use REGION_SA

The code below has commented out the Region line.

I will post an updated zip file soon.

Thanks

Colin

section access;

trace <<< Section Access >>> ;

// This now works because the field names below are in UPPERCASE
// Users are restricted to only see the Costcentres & Region data they have access to

Access:

LOAD Access as ACCESS,

UserID as USERID,

Password as PASSWORD,

CostCentre as COSTCENTRE,

// Region, // Remove Region field form section access as REGION_SA field is used instead
upper(Region) as REGION_SA // Region data for Section Access restrictions loaded in UPPERCASE`
FROM SECTION_ACCESS.xlsx

(ooxml, embedded labels, table is Region);

trace <<< Section Application >>> ;

section Application;

Re: QlikView Section Access Examples

Colin-Albert — Thu, 27 Mar 2014 10:50:18 GMT

Version 3

The Section Access Examples zip has been updated to resolve the issue identified by Wannita in [K3 Section Access 7.qvw]

Regards

Colin

Re: QlikView Section Access Examples

Anonymous — Mon, 14 Apr 2014 03:53:58 GMT

The issue where profiles of type USER cannot open the document is because of the strict exclusion setting. The ADMIN profile allows for the document to be opened when this setting is checked. However USERS cannot. Therefore examples 5 and 6 will fail the strict exclusion test because initially the field NAME must be upper case then the field VALUE must be uppercase.

As per code above example 7 fails this test because the section access example still has REGION. Therefore this join will fail the strict exclusion test.

Re: QlikView Section Access Examples

Anonymous — Mon, 14 Apr 2014 03:54:20 GMT

does not seem the zip has been updated.

Also below are 3 examples using OMIT.

Example 1: OMIT 1 field per role.

// This now works because the field names below are in UPPERCASE

// OMIT ensures that the user cannot access the column.

DIRECTORY;

Access:

LOAD Access as ACCESS,

UserID as USERID,

Password as PASSWORD,

OMIT

FROM

[SECTION_ACCESS.xlsx]

(ooxml, embedded labels, table is Omit_Simple_1);

Access	UserID	Password	OMIT
USER	USER	USER
ADMIN	ADMIN	ADMIN
USER	JOHN	JOHN
USER	MARY	MARY
USER	PETER	PETER	REGION
ADMIN	SIMON	SIMON	COSTCENTRE
DUMMY	DUMMY	DUMMY
DUMMY	DUMMY	DUMMY
DUMMY	DUMMY	DUMMY
DUMMY	DUMMY	DUMMY
DUMMY	DUMMY	DUMMY

Example 2: Omit 2 fields per role

DIRECTORY;

Access:

LOAD Access as ACCESS,

UserID as USERID,

Password as PASSWORD,

OMIT

FROM

[SECTION_ACCESS.xlsx]

(ooxml, embedded labels, table is Omit_Simple_2);

Access	UserID	Password	OMIT
USER	USER	USER
ADMIN	ADMIN	ADMIN
USER	JOHN	JOHN
USER	MARY	MARY
USER	PETER	PETER	REGION
ADMIN	SIMON	SIMON	COSTCENTRE
ADMIN	SIMON	SIMON	COSTCENTRE
ADMIN	SIMON	SIMON	REGION
ADMIN	SIMON	SIMON	REGION
DUMMY	DUMMY	DUMMY
DUMMY	DUMMY	DUMMY
DUMMY	DUMMY	DUMMY
DUMMY	DUMMY	DUMMY
DUMMY	DUMMY	DUMMY

Example 3: Data Reduction and OMIT

Access:

LOAD Access as ACCESS,

UserID as USERID,

Password as PASSWORD,

CostCentre as COSTCENTRE,

upper(Region) as REGION_SA, // Region data for Section Access restrictions loaded in UPPERCASE`

OMIT

FROM

[SECTION_ACCESS.xlsx]

(ooxml, embedded labels, table is Omit_Reduction);

Access	UserID	Password	CostCentre	Region	OMIT
USER	USER	USER	*	*
ADMIN	ADMIN	ADMIN	*	*
USER	JOHN	JOHN	A	North
USER	MARY	MARY	B	South
USER	PETER	PETER	C	East	REGION
ADMIN	SIMON	SIMON	A	North	COSTCENTRE
ADMIN	SIMON	SIMON	A	East	COSTCENTRE
ADMIN	SIMON	SIMON	A	North	REGION
ADMIN	SIMON	SIMON	A	East	REGION
DUMMY	DUMMY	DUMMY	D	North
DUMMY	DUMMY	DUMMY	E	South
DUMMY	DUMMY	DUMMY	F	East
DUMMY	DUMMY	DUMMY	G	West
DUMMY	DUMMY	DUMMY	H

Re: QlikView Section Access Examples

zagzebski — Mon, 14 Apr 2014 20:46:56 GMT

Just came across this discussion. Very good info A few followup questions;

1. Does Section Access require a password - or can we leave that field off?

2. Related to #1 - can we just use NT NAME and not require a password

3. Are there any issues using this with Access Point.

Re: QlikView Section Access Examples

Colin-Albert — Mon, 14 Apr 2014 21:37:43 GMT

Section access can be done with just a USERID and no password, but I would not recommend this as it will be easy for users to guess another user I'd and access data they should not have access to.

NTNAME authentication does not have a password as the user is already authenticated on the domain and can only have access as their login name.

Section Access will work fine on the access point. Just remember to allow the QSS service account permission to open the document to allow reloading to run.

Re: QlikView Section Access Examples

zagzebski — Mon, 14 Apr 2014 22:36:57 GMT

Thanks for the followup Colin!

1. So if I use NTNAME all I need is "ACCESS" "NTNAME" then an OMIT and/or a Restriction field in the Section Access section?

2. Sorry - not an administrator - What is the QSS service account permission. I know we use QlikView Small Business Edition.

Steve

Re: QlikView Section Access Examples

Colin-Albert — Tue, 15 Apr 2014 06:03:17 GMT

1. Yes

2. Oops that should be QDS - QlikView Distribution Service.

Re: QlikView Section Access Examples

Tue, 06 May 2014 20:08:49 GMT

I can open the documents as admin or Simon and it works fine. But no other user seems to work in the sample applications version 3 to 7. Is there a setting I need to set or something?

This is very useful, if I can get the other users to open the apps.

Re: QlikView Section Access Examples

Anonymous — Wed, 07 May 2014 05:02:16 GMT

As per code above example 7 fails this test because the section access example still has REGION. Therefore this join will fail the strict exclusion test.

Re: QlikView Section Access Examples

Thu, 19 Jun 2014 16:34:32 GMT

A very useful example.

I have an issue that I just cannot get my head around with the access restrictions required in my document.

I have multiple countries and most users are to be allowed access to the Global value and their own country only. I can restrict them to their country but cannot get them to see the Global picture (i.e. Country is Null).

I am using buttons to control country selection so possibly the Section Access is not the right place to control the views.

Any help appreciated!

Re: QlikView Section Access Examples

Colin-Albert — Fri, 20 Jun 2014 08:33:22 GMT

Hi Andrew,

The Global picture (no Country selected) actually means that all Countries are selected in QlikView logic.

So if your section access is restricting a users to only view one country, then that one country is all they can see as a global view.

One possibile solution may to be combine all counties data into an extra country named ALL and add this data to the fact table, then allow users to access two specific countries - their own country and ALL. This way a user could see the overall figures but not the specific data for any one country apart from their own.

Re: QlikView Section Access Examples

Fri, 14 Nov 2014 09:49:21 GMT

Hi Collin,

Thanks for this awesome post, Its really helpful. I have used this in my personal edition to reduce data volume based on filters and giving different users access to different region based on their need.

However can you add a couple of more lines to do the same data reduction from the QMC as well, restricting data for a partcular user.

It will really helpful.

Regards,

Roy

Re: QlikView Section Access Examples

Fri, 13 Feb 2015 16:52:55 GMT

How the ACCESS ADMIN in SECTION ACCESS see all the sheet?

Re: QlikView Section Access Examples

Fri, 13 Feb 2015 18:40:07 GMT

Resolved, like this:

Section Access;

LOAD * INLINE [

ACCESS,USERID,PASSWORD,ABAS

ADMIN,ADMIN,admpre,

USER,000005,Rona5per,1

USER,000161,Fla1440b,2

USER,000162,marco162,3

];

Section Application;

Abas:

LOAD * INLINE [

ABAS, Descrição

, Financeiro

, Comercial

, Logística

1, Financeiro

2, Comercial

3, Logística

];

And, in sheet properties put in Conditional ABAS=2 or ABAS='' for the respective sheet.

Re: QlikView Section Access Examples

Wed, 29 Jul 2015 07:54:33 GMT

I've checked "Initial Data Reduction Based on Section Access” & “Strict Exclusion” & “Prohibit Binary Load”, but it dosn't work. So I've checked "Initial Selection Based on Section Access” then it works.

What's the problem?

Re: QlikView Section Access Examples

Colin-Albert — Wed, 29 Jul 2015 08:40:36 GMT

If you tick Strict Exclusion and your login has no access to any data based on the section access rules defined in your section access script, then the document will not open.

If you untick Strict Exclusion and your login has no access based on your section access rules, then the document will open and you will see ALL data. This is not what you want in a secured environment.

The behaviour you are seeing is section access working correctly.

Strict Exclusion is doing exactly what it is designed to do.

Re: QlikView Section Access Examples

Wed, 29 Jul 2015 14:15:21 GMT

Thanks for your reponse.

I follow the document of Introduction_to_Section_Access_-_Rev_1-1.pdf example 1, but when I've reloaded the script , the error happens for loaded the data! It's not normal.

I don't find out what is the problem.