topic Better Security Architecture in QlikView

Better Security Architecture

Anonymous — Tue, 18 Aug 2015 09:37:28 GMT

Hello,

I'm in a process of moving the security (Section Access and Sheets visibility to users) from the in-line script code and from the 'Sheet Properties'->Conditionnality respectively.

All the solutions that I've found suggest to create another table as "Sheets Access" that will be connected to the section access table via the NTNAME. something like that :

Section Access;

LOAD

ACCESS,

NTNAME,

REGION // <- this is a field name

FROM

(ooxml, embedded labels, table is [Section Acesss]);

Section Application;

SHEETS_ACCESS:

LOAD NTNAME,

MODEL1->HOME_EXPANDED], // <- this is a sheet name - NOT GENRIC

MODEL1 ->HOME_REGULAR],

MODEL2->DASHBOARD]

FROM

(ooxml, embedded labels, table is [Sheets Acesss]);

However, I find it not that convinient to work with for the long term becuase it is not generic enough.

For example if I tommorow I would like to add another sheet or field I'll have to add a new column in the excel files, and then rewrite the load script of the excel files.

In addition, the Sheet_Access excel file might contain lot's of columns (as the number of sheets in each model X num of models ) - not convinient enough.

I thought of a generic solution that the sheet access table would look like that:

SHEETS_ACCESS:

LOAD

NTNAME,

MODEL_NAME, // <- a generic coloumn

SHEET_NAME // <- a generic coloumn

FROM

And here is my question - Do you think it is possible? and if then how can I assimilate it in the condition expression of the sheet properties?

I think this will be the most generic and simple architecure for security, but because I'm pretty novice in QV I would like to get some opinions.

Thanks.

Tomer

Re: Better Security Architecture

sundarakumar — Tue, 18 Aug 2015 10:05:15 GMT

Please correct me if an wrong.

you have sheet security implemented as shown below

Name	Sheet 1	Sheet 2	Sheet 3
aa	Yes	no	no
bb	no	yes	yes

You would like it to be implemented as

Name	Access
aa	Sheet 1
bb	Sheet 2
bb	Sheet 3

so that you can just add entries to the table where the script is being untouched?

If so this can be achieved with a little data modeing and changing the sheet show condition accordingly. The new sheets need to be writted with corresponding show conditions when it is being developed.

Regards,

Sundar

Re: Better Security Architecture

Anonymous — Tue, 18 Aug 2015 11:01:47 GMT

Yes. You understood what I want to do and you described it well. (even better than me )

Now as for the implementation itself I thought of doing something like that:

SHEETS_ACCESS:

LOAD

NTNAME,

MODEL_NAME,

SHEET_NAME,

VISIBLE

FROM

(ooxml, embedded labels, table is [Sheets Access - GENERIC])

WHERE(MODEL_NAME = 'Finance');

but when I get to the Sheet properties I don't know what to write as for the expression in the conditional text box.

I would something clear like "when model_name='FINANCE' and VISIBLE=1" then the sheet will be seen, but I'm not sure how to write that.

Re: Better Security Architecture

sundarakumar — Tue, 18 Aug 2015 11:47:47 GMT

I would prefer to add only the required sheets to the table itself instead of a visibile flag. It can be achieved by simple where conditions like where visible=1.

since this is section access the user will have only his data i.e.aa will have only sheet one in the previous example shown.

You can hide sheets with sheet condition as below

substringcount(concat(Sheet_field_name,'|'),'Sheet 1')

this is to be added to sheet show condtion. This will return zero for bb who dont have access to it. Similar conditions needs to be written of corresponding sheets.

Hope this helps.

Regards,

Sundar

Re: Better Security Architecture

Anonymous — Tue, 18 Aug 2015 12:05:09 GMT

All right.

One more question -

It doesn't filter by the NTNAME for some reason. What do I do wrong? for example - If I create a table box it shows me all the records from the SHEETS_ACCESS table regardless the NTNAME. And ofcourse beacause of that the sheet condition doesn't work either.

the script is:

Section Access;

LOAD ACCESS,

NTNAME,

TARGETID,

COMPANYNUM

FROM

(ooxml, embedded labels, table is [Section Acesss]);

Section Application;

SHEETS_ACCESS:

LOAD

NTNAME,

MODEL_NAME,

SHEET_NAME

FROM

(ooxml, embedded labels, table is [Sheets Access - GENERIC])

WHERE(MODEL_NAME = 'Finance');

Re: Better Security Architecture

sundarakumar — Tue, 18 Aug 2015 12:09:46 GMT

as of my knowledge SECTION ACCESS keyword will let the qlikview know that the following code is for Section access. You might note all the entries here will be automatically changed to caps.

SECTION APPLICATION is the keyword that will apply the section access , data reduction.

hope this helps.

Regards,

Sundar

Re: Better Security Architecture

Anonymous — Tue, 18 Aug 2015 12:23:54 GMT

All right.

One more question -

the script is:

Section Access;

LOAD ACCESS,

NTNAME,

TARGETID,

COMPANYNUM

FROM

(ooxml, embedded labels, table is [Section Acesss]);

Section Application;

SHEETS_ACCESS:

LOAD

NTNAME,

MODEL_NAME,

SHEET_NAME

FROM

(ooxml, embedded labels, table is [Sheets Access - GENERIC])

WHERE(MODEL_NAME = 'Finance');

Re: Better Security Architecture

sundarakumar — Tue, 18 Aug 2015 17:28:03 GMT

Did you set the conditions on Document setting?

Document setting->opening->Initial data reduction based on section access.

Step by step way of Section access implementation is explained in the attached document.

Hope this helps.

Regards,

Sundar