Unrestricted Section Access Users

[#1]

Hello,

I am developing an application in which I want to restrict some users to their own data, and have other users without any data restrictions. I am deploying this on QlikView server, so setting the ACCESS column to be ADMIN won't help me as every user on server is treated as with ACCESS type of USER.

My script looks like what I have below. Note that I have a LoginID column in my data which matches the AD account/NTNAME:

Section Access;

LOAD * INLINE [

  ACCESS, NTNAME

  USER, Domain\User1Alias

  USER, Domain\User2Alias

  USER, Domain\User3Alias

];

Section Application;

LOAD * INLINE [

  NTNAME, ResourceLoginID

  Domain\User2Alias, Domain\User2Alias

  Domain\User3Alias, Domain\User3Alias

];

Suppose I only want User2 and User3 to be restricted to their own data, but User1 should be able to see all data. How can I configure my script to do this? Note that some records have null for ResourceLoginID, so there will be records without User1, User2, or User3 in the ResourceLoginID column, but User1 should still be able to see those records.

[swuehl]

Section access with reduction of field values works like selections, so I think you should consider replacing the NULL in ResourceLoginID records with a special value you can link to your authorization table (e.g. DUMMY)

Then create a record for each link value you need to give User1 access to.

LOAD * INLINE [

  NTNAME, ResourceLoginID

  Domain\User2Alias, Domain\User2Alias

  Domain\User3Alias, Domain\User3Alias

  Domain\User1Alias, Domain\User2Alias

  Domain\User1Alias, Domain\User3Alias

  Domain\User1Alias, DUMMY

];

edit: renamed dummy field value to DUMMY

[#3]

I like the idea of replacing the NULL values with a DUMMY value.

Currently I have about 255 users total, 5 of which are admin users and need access to all the data, the rest of which should be restricted to their own data. I imagine I can use * to bypass having to put 5x255 lines for each admin user by using the following syntax:

LOAD * INLINE [

  NTNAME, ResourceLoginID

  Domain\User2Alias, Domain\User2Alias

  Domain\User3Alias, Domain\User3Alias

  Domain\User1Alias, DUMMY

  Domain\User1Alias, *

];

However, any user that I add the * for is not able to see other records. Am I doing this incorrectly? I have added a "STAR IS *;" clause at the start of my Section Access script but still no luck.

Any ideas?

[swuehl]

I had issues with STAR IS statement in the past, so I try to avoid it (but you can try your luck, maybe it's working for you). I usually use a link table to create a reduction field value ALL to authorize admin users to see all records.

1) create the DUMMY field value for NULL in your fact table with ResourceLoginID

2) Create a link table in section application:

ReductionLink:

LOAD DISTINCT

          ResourceLoginID,

          upper(ResourceLoginID) as REDUCTION

FROM YourOriginalTableWithResourceLoginID;

CONCATENATE (ReductionLink)

LOAD

          ResourceLoginID,

          'ALL' as REDUCTION

FROM YourOriginalTableWithResourceLoginID;

3) Then your section access table could look like

LOAD ACCESS, NTNAME, Upper(REDUCTION) as REDUCTION INLINE [

  ACCESS, NTNAME, REDUCTION,

  ADMIN, Domain\User1Alias, ALL

  USER, Domain\User2Alias, Domain\User2Alias

  USER, Domain\User3Alias, Domain\User3Alias

];

edit: I believe you need to apply upper() on the REDUCTION field:

[Peter_Cammaert]

First let's eliminate a few misconceptions: the wildcard conecpt is totally alien to QlikView. Except when used as function call parameters or when used in Section Access tables. Meaning that in Qlikview there is no other association than explicit association with which every value corresponds to an identical value in another place.

In Section Access, a compromise has been made by the QlikView designers. In SA, there appears to be a wildcard character that translates to every value found in the same column in the same table. Not in the same field. Not anywhere else. Unfortunately not in any other associated like-named field.

If you want to give access to individual users (identified by their username) to ranges of values in your data model, you will have to specifiy a link to every one of them. A wildcard in the section access table won't save you because it doesn't know about all the values that will be loaded later on. It just knows about the other values in the same SA table.

To make this data (-expansion) problem more manageable, QlikView developers often resort to grouping techniques borrowed from other IT management environments. For example, every Windows account is being made member of a Users group by default. To grant all users read-only/execute access to the MS Excel progrzm, Windows will by default grant read-only access to the group Users instead of granting access to every individual user.

You can do the same by assigning every user to a specific imaginary QlikView group with corresponding rights. For example:

Section Access;

LOAD * INLINE [

ACCESS, NTNAME, GROUPNAME

ADMIN, Domain\ServiceAcount,

USER, Domain\User1Alias, DIV1

USER, Domain\User2Alias, DIV2

USER, Domain\User3Alias, ALL

USER, Domain\User666Alias, DIV2

:

];

Section Application;

LinkTable:

LOAD * INLINE [

GROUPNAME, MyRealLinkField

DIV1, 1

DIV2, 2

];

CONCATENATE (LinkTable)

LOAD 'ALL' AS GROUPNAME, MyRealLinkField

RESIDENT LinkTable;

Or it would be even better to lift all possible MyRealLinkField values from your data source to make it adapt to a changing world.

One word of advice: keep everything that could change in the near future out of your script to improve the longevity of your QVW document. Except if you're seeking for job security

Best,

Peter

[#6]

How would this example look if I were using excel instead of inline tables. My fields in my Section Access table are ACCESS, NTNAME, REDUCTION FIELD. I will be using NTNAME as the link between Section Access and Section Applicatoin.