topic Re: Security based on Selection of data in QlikView

Security based on Selection of data

Wed, 08 Aug 2012 04:15:55 GMT

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)

profile	department	customer	type
P0A	DeptA1	CustomerA1	Type1
P0A	DeptA1	CustomerA2	Type2
P0A	DeptA1	CustomerA3	Type1
P0A	DeptA1	CustomerB1	Type1
P0A	DeptA1	CustomerB2	Type2
P0A	DeptA1	CustomerB3	Type1
P0A	DeptB1	CustomerA1	Type1
P0A	DeptB1	CustomerA2	Type2
P0A	DeptB1	CustomerA3	Type1
P0A	DeptB1	CustomerB1	Type1
P0A	DeptB1	CustomerB2	Type2
P0A	DeptB1	CustomerB3	Type1

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

profile	department	customer	type
P0A	DeptA1	CustomerA1	Type1
P0A	DeptA1	CustomerA2	Type2
P0A	DeptA1	CustomerA3	Type1
P0A	DeptB1	CustomerB1	Type1
P0A	DeptB1	CustomerB2	Type2
P0A	DeptB1	CustomerB3	Type1

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

Re: Security based on Selection of data

Wed, 08 Aug 2012 05:02:02 GMT

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.

Re: Security based on Selection of data

Wed, 08 Aug 2012 05:24:16 GMT

Hi Michael,

Thanks for the quick reply.

Let me put it this way:

profile	department	customer	type
P0A	DeptA1	CustomerA1	Type1
P0A	DeptA1	CustomerA2	Type1
P0A	DeptA1	CustomerA3	Type1
P0A	DeptB1	ALL	ALL
P0A	DeptB1	ALL	ALL
P0A	DeptB1	ALL	ALL

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

Re: Security based on Selection of data

Wed, 08 Aug 2012 05:26:43 GMT

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

Re: Security based on Selection of data

Wed, 08 Aug 2012 06:22:10 GMT

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

Re: Security based on Selection of data

Wed, 08 Aug 2012 23:30:15 GMT

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.

Re: Security based on Selection of data

Fri, 10 Aug 2012 06:19:08 GMT

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

Re: Security based on Selection of data

IAMDV — Fri, 10 Aug 2012 11:26:38 GMT

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,

www.QlikShare.com

Re: Security based on Selection of data

Mon, 13 Aug 2012 06:10:01 GMT

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).

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

profile	department	customer	type
P0A	DeptA1	CustomerA1	Type1
P0A	DeptA1	CustomerA2	Type1
P0A	DeptA1	CustomerA3	Type1
P0A	DeptB1	CustomerA1	Type1
P0A	DeptB1	CustomerA1	Type2
P0A	DeptB1	CustomerA2	Type1
P0A	DeptB1	CustomerA2	Type2
P0A	DeptB1	CustomerA3	Type1
P0A	DeptB1	CustomerA3	Type2
P0A	DeptB1	CustomerB1	Type1
P0A	DeptB1	CustomerB1	Type2
P0A	DeptB1	CustomerB2	Type1
P0A	DeptB1	CustomerB2	Type2
P0A	DeptB1	CustomerB3	Type1
P0A	DeptB1	CustomerB3	Type2

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

Thanks,

Robinson

Re: Security based on Selection of data

Mon, 13 Aug 2012 06:46:19 GMT

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

Re: Security based on Selection of data

IAMDV — Mon, 13 Aug 2012 09:43:41 GMT

No problem Robinson.

I don't have time today but I'll look into this and get back to you ASAP.

Where are you based?

Cheers,

www.QlikShare.com

Re: Security based on Selection of data

Tue, 14 Aug 2012 03:01:23 GMT

Thanks Deepak

Re: Security based on Selection of data

IAMDV — Thu, 16 Aug 2012 08:31:30 GMT

Interesting! I saw your spreadsheet and it's complex for sure. Please can you also attach the application? It helps to test the script while working Section Access.

Thanks,

www.QlikShare.com

Re: Security based on Selection of data

Thu, 16 Aug 2012 09:52:37 GMT

Hi Deepak,

Thanks.

I am following a different approach now. I am creating an Excel sheet (link table) with all the possible allowed selection criteria for the user login. Fields from this table will be given to the user for selections, so that ways the selections for each profile is controlled. And this would be linked to the main Fact table using a composite key of all fields.

Now, this way, I can also add n number of rows and flag then as 0 or 1. I will then check in the expressions for the flag and enable/disable them (for each possible selection criteria). Even here I am facing some issue with the ApplyMap function..

http://community.qlik.com/message/248835#248835

I am trying different ways to do this.

I am attaching the qvw (few data is scrambled) and the Excel file witht he possible combinations.

Thanks again for your help.

Thanks,

Robinson