topic section access and data restriction loaded from excel in QlikView

section access and data restriction loaded from excel

andries_bos — Thu, 22 May 2014 10:41:56 GMT

I would like to control/authorize the access to certain data (set by company_ID) from within an excel file. this makes it easy to control, but also to append for new users.

I have managed to get this work by separation this into two different worksheet, but would like to control this in a simple way :

What already worked:

worksheet 1:

access	NTNAME	Range
ADMIN	Andries	0
USER	John	1

worksheet 2:

Range	Company
0	ALL
1	010
1	020
2	010
2	030

This solution will limit the normal user John to only see where company has a value of 010 or 020 and user abos (also as admin) would see all.

However, as I have 300 users each having different access asigned, I would like to control this from one exel file. Although i have searched for a long time , I could not find any examples for this.

MY controller/authorization excel would be like:

User		Company
NTNAME	access	010	011	012	020	030

John	User	1	1	0	0	0
George	User	0	0	1	1	1
Andries	Admin	1	1	1	1	1

How could I combine the this authorization file to grant john access to only company 010,011 without separation and explicit create anew record for each combination as in the first example.

If you do find an example where this already has been done, please share.

The next level/step would be to control access in a simple way for sheets and for reports. We do nog have the reporting module, so acces would be done on document level.

Attached you will find the script for loading my first example (may helpfull for other people)

Re: section access and data restriction loaded from excel

andries_bos — Fri, 06 Jun 2014 07:23:21 GMT

I managed to combine my original xls list as provided above of company access per user into one 'clean' list. To manage it in an easy way (step by step), I saved my 'access list' into a qvd.

If loading this qvd within my section access area, the rebuild will terminate without any detail; if it's loaded outside my section access, it rebuilding fine.

Star is ALL;

Section Access;

Toegang:

LOAD NTNAME,

ACCESS,

BEDRIJF_ID

FROM

[..\..\Data\QVD\Toegang1.qvd]

(qvd);

where 'BEDRIJF_ID' is my unique company values the users are restricted to within the rest of the data.

Could one use a qvd as a source within the section access? I'm missing something, but do not know the 'limitations of the game'. Information would be welcome

Re: section access and data restriction loaded from excel

hic — Fri, 06 Jun 2014 08:39:10 GMT

Yes, you can use a QVD as a source in Section Access. But why would you? An Excel sheet or a DB table is a lot better if you want to edit it. The only advantage with a QVD is that it loads fast - and that is irrelevant for such a small table.

Your solution using a crosstable is a good one, and if you are satisfied with what it does, you shouldn't change it. But there are alternatives. One is to store all companies like a comma separated list

and use Subfield(COMPANY,',') as COMPANY to split it up on different lines.

Secondly, I don't think it is a good solution to use a wildcard "*" to represent <ALL>. It will only match the companies that actually appear in Section Access. It will not match companies that exist in data but not in Section Access. Instead you should look at Generic Keys for this. See more on

Basics for complex authorization

Generic keys

HIC

Re: section access and data restriction loaded from excel

andries_bos — Fri, 06 Jun 2014 13:50:32 GMT

I almost solve my problem...

If I load my info from my own list, i will be prompt to login; if I add exact the same data using an inline load, it's working as expected.... My document setting as for both attemps 'restricted load'

just for comparison of the data , i have changed my field names with a 's' :

data from both sources are looking exact the same; I have forced a conversion of the field 'BEDRIJF_ID' to a TEXT field:

SECURITY:

LOAD Text(BEDRIJF_IDs) AS BEDRIJF_IDs, NTNAMEs,ACCESSs

INLINE [

ACCESSs, NTNAMEs, BEDRIJF_IDs

ADMIN, VANWERVEN\ADMINISTRATOR,

USER, SRV04\qv-krvw, 020

USER, VANWERVEN\abos, 010

USER, TEST, 030

];

Toegang:

Load Text(BEDRIJF_ID) AS BEDRIJF_ID, ACCESS, NTNAME;

LOAD ACCESS1 AS ACCESS,

NTNAME1 AS NTNAME,

Text(BEDRIJF_ID1) AS BEDRIJF_ID

resident Toegang1;

Using the 'toegang' solution where the data look exact the same, i get the prompt, but I could not manage to login : I have to calcal a few times or end the process using task manager:

This behaviour beets me. Any ideas?

Re: section access and data restriction loaded from excel

hic — Fri, 06 Jun 2014 20:38:47 GMT

Try using

Upper(BEDRIJF_ID) as BEDRIJF_ID

and the same on all the other fields in Section Access. There could be a difference in the case conversion for the two sorces.

HIC

Re: section access and data restriction loaded from excel

andries_bos — Mon, 23 Jun 2014 13:49:08 GMT

Henric, only by adding an extra char to the field, I could force a

Upper(BEDRIJF_ID) as BEDRIJF_ID to correctly be interpreted as a char and not as an integer (our companies names are 010, 020 etc,). However, your suggestion does not provide a working solution.

I have added he controlling xlsx and my sample qvw file. Maybe anyone could find a solution..

To enable anyone to help me, i have disabled 'script exclusion' and 'section access' part within my script. This part is now commented to enable opening this file. However, this file does show the essential of controlling access using excel as the controlling file.

To my opinion, this solution could be practical for a lot of users as the controlling of data could be easily done within the excel file.

Any ideas to get this working, would be great!

Files could be downloaded at

http://scancare.synology.me:5000/fbsharing/FJGNpJCY (xlsx file )

http://scancare.synology.me:5000/fbsharing/1VuexQyj (qvw file)

Re: section access and data restriction loaded from excel

andries_bos — Mon, 23 Jun 2014 15:01:29 GMT

My example does seems to work !! filtering my data per user as long as I do not use the document option'strict exclusion' ! ?

However, I would like to use this option as my report would contain secure financial information in the future.

I'm missing in my opinion the 'rules of the game' to use strict exclusion' ?? As far as I can see, user 'abos' does have some data and should be granted access to this document due to the amount of data filtered for him...?