Qlik Community

QlikView Documents

Documents for QlikView related information.

QlikView Section Access Examples

QlikView Section Access Examples

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.

Labels (2)
Attachments
Comments
kanhomcake
New Contributor III

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

WV

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;

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

Employee
Employee

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.

Employee
Employee

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);

AccessUserIDPasswordOMIT
USERUSERUSER
ADMINADMINADMIN
USERJOHNJOHN
USERMARYMARY
USERPETERPETERREGION
ADMINSIMONSIMONCOSTCENTRE
DUMMYDUMMYDUMMY
DUMMYDUMMYDUMMY
DUMMYDUMMYDUMMY
DUMMYDUMMYDUMMY
DUMMYDUMMYDUMMY

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);

AccessUserIDPasswordOMIT
USERUSERUSER
ADMINADMINADMIN
USERJOHNJOHN
USERMARYMARY
USERPETERPETERREGION
ADMINSIMONSIMONCOSTCENTRE
ADMINSIMONSIMONCOSTCENTRE
ADMINSIMONSIMONREGION
ADMINSIMONSIMONREGION
DUMMYDUMMYDUMMY
DUMMYDUMMYDUMMY
DUMMYDUMMYDUMMY
DUMMYDUMMYDUMMY
DUMMYDUMMYDUMMY

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);

AccessUserIDPasswordCostCentreRegionOMIT
USERUSERUSER**
ADMINADMINADMIN**
USERJOHNJOHNANorth
USERMARYMARYBSouth
USERPETERPETERCEastREGION
ADMINSIMONSIMONANorthCOSTCENTRE
ADMINSIMONSIMONAEastCOSTCENTRE
ADMINSIMONSIMONANorthREGION
ADMINSIMONSIMONAEastREGION
DUMMYDUMMYDUMMYDNorth
DUMMYDUMMYDUMMYESouth
DUMMYDUMMYDUMMYFEast
DUMMYDUMMYDUMMYGWest
DUMMYDUMMYDUMMYH
zagzebski
Contributor


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.

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.

zagzebski
Contributor

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

1. Yes

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

Not applicable

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.

Version history
Revision #:
1 of 1
Last update:
‎02-24-2014 06:54 PM
Updated by: