13 Replies Latest reply: Aug 16, 2012 5:52 AM by Robinson Selvaraj RSS

    Security based on Selection of data

    Robinson Selvaraj

      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

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

            • Re: Security based on Selection of data
              Robinson Selvaraj

              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

                • Re: Security based on Selection of data

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

                    • Re: Security based on Selection of data
                      Robinson Selvaraj

                      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

                          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
                              Robinson Selvaraj

                              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
                                  Deepak Vadithala

                                  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

                                   

                                    • Re: Security based on Selection of data
                                      Robinson Selvaraj

                                      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