Security Model

[#1]

Hello,

I have a fairly large dataset, which is loaded using a SQL view into a Qlikview report. The data can be thought of as

Code1, Code2, Code3, Value

I need to apply security to this, so the logged on user can only view data which they have permissions for. To that end, I have another db table such as

UserName, Code1, Code2, Code3

This is populated with valid combinations that this user can see.

I can join my main dataset with this security dataset within my Script, and this gives the desired restriction. However, it also means that each time the report is loaded, the data must be reloaded.

Can anyone provide an alternate way to do this?

Thanks

[michael123]

Keep main dataset in original script/application. Put security script in new application which reads former application binary at beginning.

Alternative is to store main data SQL load into QVD-file(s) and read from them.

/Michael

[#3]

Thanks - would it also be able to use partial reload functionality to do this? I.e. load main data on a full-reload, load the security data on a partial reload, and create a trigger to do partial reload on open?

[michael123]

Not sure what you mean. Your security data isn't large, is it? There is no need to do partial reload on this. Do a full load. 

If you have 1 application now. Cut the security part of the script and put in a new application which starts with "binary Mymaindata.qvw" (i.e your old file) at first line. This new application is for your users.

/Michael

[#5]

Okay, I'm now at the stage where I've got QVD files populated with data, but I'm having a bit of trouble "joining".

My security table (loaded from DB always, which is fine)

Security:

LOAD NTNAME,

    Company as Key_COMPANY,

    CostCentre as Key_COST_CENTRE,

    Account as Key_ACCOUNT

Where

    NTNAME = Upper(OSUser()) ;

SQL SELECT *

FROM Finance.dbo.USER_SECURITY;

then:

INNER JOIN(Security)

LOAD

     Key_PERIOD_NAME,

     Key_COMPANY,

     Key_COST_CENTRE,

     Key_ACCOUNT,

     Expenses.AccountDescription,

     Expenses.Value

FROM

C:\work\projects\finance\Expenses.qvd

(qvd);

and:

INNER JOIN(Security)

LOAD

     Key_COMPANY,

     Key_COST_CENTRE,

     Key_ACCOUNT,

     DepartmentBudget.AccountDescription,

     DepartmentBudget.Budget

FROM

C:\work\projects\finance\DepartmentBudget.qvd

(qvd);

This seems to result in my actual values getting corrupted (they should be £100,000 etc, but they end up being in the 10's of millions). I can only assume I've applied the join wrong?

[michael123]

What you want to accomplish is dynamic datareduction with section access? It feels like you are trying to solve 2 things at the same time. Showing the right data and at the same time connecting security to it.

First you need to get your data model to work. Can't evaluate what is going wrong with your script since there are parts not seen ("INNER JOIN(Finance)?") and there is no actual data.

When you sort it out you then connect a section access to your data with the security script telling who will see what. Have you read the chapter in Qlikview Reference Manual about Section  Access? Try also searching this site since there has been many questions over the years.

[#7]

Sorry, the Inner join (Finance) should have been Inner join (Security), corrected now.

I'm trying to reduce the data for the purposes of security - I don't think the built in section access will allow what I need?

For example

User Jamie can see COMPANY 111, COST_CENTRE 222, ACCOUNT 333

Therefore, my entire dataset needs to be "reduced" to only show those values to user Jamie.

I can accomplish this in SQL by inner joining the datasets. When I do the same in Qlikview I end up with duplicated data.

If I apply my join criteria to only one dataset, it works perfectly. As soon as I apply the join to more than one dataset (as in the my previous post), the data is repeated.

[michael123]

Here is a blog of generic keys and authorization.  http://community.qlik.com/blogs/qlikviewdesignblog/2012/10/02/complex-authorization

Maybe it can be of some help.

You are not using QVServer? Will the script reload when a user is trying to open the application?

It's difficult to advise with facts given but I would suggest another approach if so.

[#9]

I actually just figured this out - the INNER JOIN above was replaced with INNER KEEP, which has given me the correct results. Seems to work as intended - thank you.