Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
andgid
Partner - Contributor
Partner - Contributor

Security-issue

Hi,

I have a question regarding a security/modelbuilding issue:

I have a Qlikview-file with some tables (see below)

error loading image

SALES_ABC is the fact-table, the other ones are dimension-tables.

My problem is that the user logged in (found in USRS-table) should just see the data in the fact-table depending on their "security-level" found in the two fields CUSTOMER_ATTRIBUTE04_ID and SEC_VALUE1. When Opening the file the data is reduced based on section access.

The link for CUSTOMER_ATTRIBUTE04_ID selects the right customer categories, works OK.

The problem is the field SEC_VALUE1 the should select the right Product groups via PRODUCT_ATTRIBUTE01_ID, but if I name the field in USRS to PRODUCT_ATTRIBUTE01_ID instead of SEC_VALUE1 Qlikview cannot load the data, probably due to some circular links.

My solution is now using Macros. It nearly works OK, but if anyone have another solution, please feel free to post that one instead.

The problem with the macro-solution is reading the SEC_VALUE1 field. I'm not that used to VBScript and must say that the APIGuide isn't that easy to understand either. My thoughts was that the method GetPossibleValues for the field SEC_VALUE1 should give me just one value due to the fact of Reduced data on load, but it still gives me all values.

One more thing that I have to mention:

The file is running on a Qlikview server with 20 different users (i.e different security levels) and they are also using AJAX instead of the IE Plugin.

Version used is v9.00 SR2

Anyone having a better solution or at least can point me in the right direction reading the SEC_VALUE1 for the logged on user?

Best regards,

/Anders

7 Replies
vgutkovsky
Master II
Master II

If I understood your question correctly, SEC_VALUE1 and PRODUCT_ATTRIBUTE01_ID contain the same type of values (so that matching is appropriate)? If that's the case, instead of using a macro, have you tried doing this with an IF statement in your expressions? I think with the kind of calculations you need you can do it and not even lose much on performance. So it would look like this:

if(PRODUCT_ATTRIBUTE01_ID = SEC_VALUE1, calculate formula)


Regards,

andgid
Partner - Contributor
Partner - Contributor
Author

Hi Vlad,

Thanks for your answer.

The only thing I want to do is select the Products with PRODUCT_ATTRIBUTE01_ID = SEC_VALUE1 if SEC_VALUE1 is not equal to '*', in that case all Products should be shown.

By selecting Products for a specific SEC_VALUE1 it would be best if the non-selected ones are hidden/reduced for that user.

I might be stupid, but I don't really understand your offered solution. But I see that I wasn't so clear with my question either 😉

Regards,

/Anders

vgutkovsky
Master II
Master II

I'm sorry, but I don't really understand your question well enough to offer an answer... Maybe someone else can help Anders?

Thanks,

andgid
Partner - Contributor
Partner - Contributor
Author

Vlad,

Let me try to explain it better in that case.

I've a USRS-table with users using section access and nt-authentication to set up the security.

Per User I've 2 fields (CUSTOMER_ATTRIBUTE04_ID and SEC_VALUE1) that can have either * (a star) or an ID that should select rows from the fact-table for:

a) CUSTOMERS included in the group with ID = CUSTOMER_ATTRIBUTE04_ID (or all customers if ID = *)

b) PRODUCTS included in the product-group with ID = SEC_VALUE1

The a) case is solved automatically by normal QV-links betweem fields with same names in different tables.

Case b) cannot be solved that way because of "circular references for links" (or whatever, see picture in my first post)

Therefore I tried to solve this problem with a macro reading the value for SEC_VALUE1 for the logged on user, but I was unable to do that, due to my lack of experience in both macro and/or VBScript.

The best solution would be not using macros at all, but I cannot get the databasemodel to work (be loaded) into QV the way I want it to, and therefore I tried the macro approach.

So, my main problem is easy, how to read the value of the field SEC_VALUE1 found in the USRS-table for the logged on user using macro?

Hope that makes my problem more understandable without messing everything up 😉

Regards,

/Anders

vgutkovsky
Master II
Master II

OK, I think I understand now why you can't (or shouldn't) do this with IF statements. For something like this you would probably need a nested IF, which would crank up the memory required. I think this should work to get the current Windows identity in VBScript:


Dim objNetwork
Set objNetwork = CreateObject("WScript.Network")
strUserName = objNetwork.UserName


Then just compare this field to your SEC_VALUE1. Hope this helps.

andgid
Partner - Contributor
Partner - Contributor
Author

Hi again,

Find out the user shouldn't be hard, I can use the function OSUser to do that.

My problem is to select the right SEC_VALUE1 from the table USRS.

My code looks like this right now (remember that I'm a newbee on macros 😉


Sub ChooseValidData
Set q = ActiveDocument

Set secval = q.Fields("SEC_VALUE1").GetPossibleValues

If secval.count=0 then
sval=""
else
sval = secval.Item(0).Text
end if

Set lb = ActiveDocument.GetSheetObject("lbAO")
Set cp = lb.GetProperties

if cp.Layout.HideExcluded = true then
cp.Layout.HideExcluded = false
lb.SetProperties cp
end if

if secval = "ResNF" then
q.Fields("PRODUCT_ATTRIBUTE01_ID").Select "A8"
q.Fields("PRODUCT_ATTRIBUTE01_ID").Lock
cp.Layout.HideExcluded = true
lb.SetProperties cp
end if

If secval = "ResFG" then
q.Fields("PRODUCT_ATTRIBUTE01_ID").Select "A7"
q.Fields("PRODUCT_ATTRIBUTE01_ID").Lock
cp.Layout.HideExcluded = true
lb.SetProperties cp
end if

end sub


It is the code for getting the right value to the variable secval that gives me problem.

How can I "filter" the values found in USRS to just give me the value for the logged on user?

Section access uses the NTSID value to get the right user, and that value can also be found in the USRS-table. But I haven't found a way to get that value so that I can use it to select the corresponding user in the USRS-table, and that way get the right SEC_VALUE1...

Regards,

/Anders

andgid
Partner - Contributor
Partner - Contributor
Author

Just bumping my question, I really need to know how to find out the value in the field SEC_VALUE1 from the USRS-table for the user logged on..

Regards,

/Anders