Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Security based on Selection of data

Hi QlikExperts,

I have a question regarding restriction of data within fields, based on selections on another field. I have tried to put an example of the data below:

Actual Data: (possible values)

profiledepartmentcustomertype
P0ADeptA1CustomerA1Type1
P0ADeptA1CustomerA2Type2
P0ADeptA1CustomerA3Type1
P0ADeptA1CustomerB1Type1
P0ADeptA1CustomerB2Type2
P0ADeptA1CustomerB3Type1
P0ADeptB1CustomerA1Type1
P0ADeptB1CustomerA2Type2
P0ADeptB1CustomerA3Type1
P0ADeptB1CustomerB1Type1
P0ADeptB1CustomerB2Type2
P0ADeptB1CustomerB3Type1

Security Part: (what should the user logged in with specific profile see)

profiledepartmentcustomertype
P0ADeptA1CustomerA1Type1
P0ADeptA1CustomerA2Type2
P0ADeptA1CustomerA3Type1
P0ADeptB1CustomerB1Type1
P0ADeptB1CustomerB2Type2
P0ADeptB1CustomerB3Type1

As seen in the example above, Profile P0A should see only CustomerA1, CustomerA2 and CustomerA3 and the data in field type, when he selects DeptA1.

But when he selects DeptB1, he should see the relevant data as seen in the above table.

However, in section access I have mentioned P0A sees DeptA1 and DeptB1 only.

Please can someone help. Hope I have best described the problem case.

Thanks in advance,

Robinson

13 Replies
Not applicable

Re: Security based on Selection of data

Do any users need to see the combination of DeptA1 and CustomerB1 together?

If not, I would simply remove those records from the data in the script.

Not applicable

Re: Security based on Selection of data

Hi Michael,

Thanks for the quick reply.

Let me put it this way:

profiledepartmentcustomertype
P0ADeptA1CustomerA1Type1
P0ADeptA1CustomerA2Type1
P0ADeptA1CustomerA3Type1
P0ADeptB1ALLALL
P0ADeptB1ALLALL
P0ADeptB1ALLALL

So if P0A selects Dept1A he should see restricted data only, but if he selects DeptB1, he should see all data.

In other words, this is restriction of data (section access), based on user selections.

Thanks,

Robinson

Not applicable

Re: Security based on Selection of data

You will probably need to create a mapping table that contains all the combinations that are allowable.

Not applicable

Re: Security based on Selection of data

Hi Michael,

You mean to create an excel table with allowable combinations for different profiles?

Create a key out of the key fields in that excel an link it to the fact table in the data model. Then make users select the fields from the excel file?

But will that be a feasable way to do? Lets say the number of profiles increase, so does the probable combinations (eg. a few more fields will be added for data restrictions).

Will it not be a pain to maintain the excel file?

Currently I have 11 profiles, out of which 2 of them have the above restrictions. If I define a mapping table for the 2 profiles, I will have to add the other profiles with all combinations also, as I would be using the fields from the excel?

I had tried above approach, but was giving some issues once i add in the profiles in excel. Will have to give it another try.

Thanks again,

Robinson

Not applicable

Re: Security based on Selection of data

I don't think you should need all possible combinations for profiles that have no restriction.  Only those that do have a restriction.  You may be able to use joins in the script to build a table of all possible combinations if that helps.

Obviously a complicated security model like this is always going to cause maintainance issues.

Not applicable

Re: Security based on Selection of data

Hi Michael,

The challenge is all the profiles have some sort of restriction on the fields.. There is only 1 profile, which does not have restriction, behaves probable like a super user...

Thanks,

Robinson

Luminary
Luminary

Re: Security based on Selection of data

Hi Robinson,

You need multi level/ Hieararchy based on Section Access Field Reduction. You need to be very careful while building something like this and please do thorough testing. Also, it gets more complex if you want to use "Strict Exclusion" option. Here is an idea to get started and this apprach is NOT using "Strict Exclusion" and with this approach you need to make sure that Section Access Reduction Field Value MUST EXIST witin your data model. If the value doesn't exist then Users will get full access. So you need to build a logic to remove the Users who don't have matching field value in Data Model compared to Section Access Reduction Field.  I'm attaching the sample application along with the script...

 

LOAD


UPPER(Profile) AS PROFILE,
UPPER(Department) AS DEPARTMENT,
UPPER(Customer) AS CUSTOMER,
UPPER(Type) AS TYPE,
UPPER(Profile) AS KEY_01,
UPPER(Profile) &'_' & UPPER(Department) AS KEY_02,
UPPER(Profile) &'_' & UPPER(Department) &'_' & UPPER(Customer) AS KEY_03,
UPPER(Profile) &'_' & UPPER(Department) &'_' & UPPER(Customer) &'_' & UPPER(Type) AS KEY_04
FROM
[..\Security based on Selection of data.xlsx]
(ooxml, embeddedlabels, tableis Sheet1);

SECTION ACCESS;
LOAD * INLINE [
ACCESS, USERID, PASSWORD, KEY_01, KEY_02, KEY_03, KEY_04
ADMIN, ADMIN, ADMIN,
USER, P0A, P0A,,,P0A_DEPTA1_CUSTOMERA1,
USER, P0A, P0A,,,P0A_DEPTA1_CUSTOMERA2,
USER, P0A, P0A,,,P0A_DEPTA1_CUSTOMERA3,
USER, P0A, P0A,,,P0A_DEPTB1_CUSTOMERA1,
USER, P0A, P0A,,,P0A_DEPTB1_CUSTOMERA2,
USER, P0A, P0A,,,P0A_DEPTB1_CUSTOMERA3,
USER, P0A, P0A,,,P0A_DEPTB1_CUSTOMERB1,
USER, P0A, P0A,,,P0A_DEPTB1_CUSTOMERB2,
USER, P0A, P0A,,,P0A_DEPTB1_CUSTOMERB3,
];

SECTION Application;

I hope this makes sense!

Cheers,

DV

www.QlikShare.com

Not applicable

Re: Security based on Selection of data

Hi Deepak,

Thanks for the reply.

From your example qvw, the ones marked in green should be the desired output (restricted values in CUSTOMER AND TYPE), for the respective selection (DEPARTMENT).

output.JPG

To elaborate more, the desired output should be something like this:

profiledepartmentcustomertype
P0ADeptA1CustomerA1Type1
P0ADeptA1CustomerA2Type1
P0ADeptA1CustomerA3Type1
P0ADeptB1CustomerA1Type1
P0ADeptB1CustomerA1Type2
P0ADeptB1CustomerA2Type1
P0ADeptB1CustomerA2Type2
P0ADeptB1CustomerA3Type1
P0ADeptB1CustomerA3Type2
P0ADeptB1CustomerB1Type1
P0ADeptB1CustomerB1Type2
P0ADeptB1CustomerB2Type1
P0ADeptB1CustomerB2Type2
P0ADeptB1CustomerB3Type1
P0ADeptB1CustomerB3Type2

And to add more to the complexity, there are also measures that should be hidden based on the above restricted selections.

Thanks,

Robinson

Not applicable

Re: Security based on Selection of data

Hi Deepak,

To be more precise about the securities, I have tried to simulate the requirement (maybe it will be more clear than my example posted above), and have attached an excel file for the same.

In reference with the excel file

Selection Filters are the fields available for selection. ALL means all possible values should be available.

Input Indicators are nothing but measures. Yes means the measure should evaluate to zero (value should not be displayed) No means the measure should be visible.

A Selection means values for these will be populated from the database - I can manage this one.

Sorry for the complexity.

Thanks,

Robinson