12 Replies Latest reply: Jan 26, 2018 5:13 AM by Marcus Sommer RSS

    Complex Security problem

    Angel Tomov

        Hello, dear Qlikers !

       

           Hadn't posted discussions in quite a while, but I got my self into a Project, where the Security of my clients is really complex.

       

      So I am looking for ways to Copy the security of my client's database, into Qlik Sense.

       

      So basically they have around 100 users from different parts of the world.

       

       

      The security in the Database is based on the columns from the DB named:
      Segment,(with 2 values inside) - dimension

      Region( with 12 values inside) -  dimension

      Country( with 7 values inside) - dimension

      Sales Amount - this is an expression

      Cost of Goods Sold  - expression

      Margin - Expression

      Intercompany transactions- dimension

       

       

      So at the end i got 8 columns on which I have to reduce data on.

       

       

      So my Question is, how to proceed.

       

      My client wants to use AD groups for the security, and He told me that they can make Any group I want into the AD.

       

      My client needs me to tell him what Groups I want from him to create into the AD, so we can use them into Qlik sense

       

      And I also have no idea, how to use AD groups to reduce data in Qlik Sense.

       


      Any Advices on how to proceed with this project will be highly appreciated.

       

      I have Knowledge on Security rules( I have used them in the past by allocating custom properties to users and to streams/apps/sheets)

      I also have knowledge on Section Access ( I have used it in 5 of my projects before, where I had to cut data on 3 different dimensions)

       

      So Please, If Any of you has made Complex Security Reductions in Qlik Sense, please help, because I am lost...

       

      I can attach files for testing if somebody has any idea how to do it ;-)

       


      Thanks in advance for your time

        • Re: Complex Security problem
          Angel Tomov

          anything ?

          I saw many posts, but in the end its not clear to me integrate AD groups into QS ...

            • Re: Complex Security problem
              Marcus Sommer

              I think you could just use a section access like this one:

               

              Section Access;

               

              star is *;

               

              SectionAccessRegion:

              LOAD upper(ACCESS) as ACCESS,

                   upper(NTNAME) as NTNAME,

                   upper(REGION) as __REGION

              FROM [$(pInc@SA)SectionAccessRules.xlsx] ($(typXLSX) Region);

               

              SECTION Application;

               

              and in NTNAME are your users or usergroups listed from the active directory. If your dimensions like Segment, Country, Region a hierarchy you could just use the least granularity to connect them to your fact- or better dimension-table. Also possible is to make a combined key of these fields. In this case it's not a complex authorization else a quite straight ones.

               

              If there are no real hierarchy it's more complicated because you will need to create something like a cartesian key of all possible combinations and remove the unwanted. Also if users should be able to access only their details but able to see consolidated overall results or the other data masked in some way or something similar.

               

              I suggest just to start with a dummy-application with just a few inline-tables/records to simulate the later datamodel and developing it step by step and not within the final application. Very important is to have always backups of your applications because by some errors there is no way back and it will be loss.

               

              Here you will find many informations about Section Access.

               

              - Marcus

                • Re: Complex Security problem
                  Angel Tomov

                  Оkay, I will loop through the files in your post, but in the meantime, its safe to say that my client has to make 3 groups in the ad
                  group 1. Segments

                  Group2. Countries

                  Group3. Regions

                   

                  where Countries are offices of my client spread through 7 countries.

                  ?

                    • Re: Complex Security problem
                      Marcus Sommer

                      I'm not sure that 3 groups each related to a dimension will work in your scenario else I think you will need more groups, for example:

                       

                      GroupCountry1:

                      User1

                      User2

                      User3

                       

                      GroupCountry2:

                      User4

                      User5

                      User6

                       

                      ....

                       

                      GroupRegion1:

                      GroupCountry1

                      GroupCountry2

                      ....

                       

                      - Marcus

                        • Re: Complex Security problem
                          Angel Tomov

                          I can't seem to understand this logic... ( GroupRegion1; has users from Groupcountry1 and groupcountry2)

                           

                          GroupRegion1:

                          GroupCountry1

                          GroupCountry2

                           

                          So, You are saying that for Each region I need group?
                          for example

                          Country1:

                          user1

                          user2

                          user3

                          country2:

                          user4

                          user5

                          user6

                          country3:

                          user7

                          user8

                          user9

                          country4:

                          user10

                          user11

                          user12
                          Region1:
                          user1
                          user6
                          user12

                          region2:

                          user7

                          user2

                          user4

                          region3:

                          user3

                          user8

                          user9;

                          Segment1:

                          user1

                          user2

                          user3

                          user4

                          user5

                          user6

                          Segment2:

                          user7

                          user8

                          user9

                          user10

                          user11

                          user12

                           

                           

                           

                          ?

                            • Re: Complex Security problem
                              Marcus Sommer

                              It's not quite easy to explain - the aim of this grouping is mostly a hierarchy of nested usergroups and I think if you look on the already existing groups within the active directory you will see the logic behind it - and I wouldn't be surprised if most of your needed groups/logics are already there because the access-logic in Qlik is usually the same like the defined logic how users could access the ERP and file-storage in your company.

                               

                              - Marcus

                                • Re: Complex Security problem
                                  Angel Tomov

                                  What can you suggest me to make this work?

                                    • Re: Complex Security problem
                                      Marcus Sommer

                                      At first you need to understand which user/usergroup should be allowed to access which data in your applications. This means there must be any rules to determine these access and with them to create some kind of access-matrix.

                                       

                                      On this basis you could look to find some pattern within the access-rights to shortcut the matrix with some further usergroups and/or to nest them.

                                       

                                      Very important on this point is also to recognize if there are any conflicts between the various access-rights because a denial on some data-parts couldn't be reversed with any allowance. In general there are workarounds possible but they require the use from certain settings and workflows and/or the generation of more data and rather quite complicated datamodels. Often it will be a better compromise to use two or several parallel applications each with a specialized secion access.

                                       

                                      But like here and above mentioned you need to understand the logic of section access and user/usergroups within active directory very thoroughly - because it's always really critical and the least mistake made it vulnerable or prevent the right or each access.

                                       

                                      - Marcus

                                        • Re: Complex Security problem
                                          Angel Tomov

                                          Yes, this was my first suggestion towards my Client, to make several applications with different SA and different DATA loaded.

                                           

                                          I understand your logic, but I got 1 more question.

                                           

                                          IF i have connected through QMC to the AD,

                                          can i use in section access just the following

                                          section ACCESS:

                                          GROUP,

                                          COUNTRY

                                          section application;

                                          ???

                                            • Re: Complex Security problem
                                              Marcus Sommer

                                              It should look like:

                                               

                                              Section Access;

                                               

                                              star is *;

                                               

                                              SectionAccessRegion:

                                              LOAD upper(ACCESS) as ACCESS,

                                                   upper(NTNAME) as NTNAME,

                                                   upper(REGION) as __REGION

                                              FROM [$(pInc@SA)SectionAccessRules.xlsx] ($(typXLSX) Region);

                                               

                                              SECTION Application;

                                               

                                              - Marcus

                                                • Re: Complex Security problem
                                                  Angel Tomov

                                                  Okay, but I see that you are using .xlsx file, is this a file made by you, or It's dynamic and if somebody makes changes in the AD, it will reflect in the .xlsx file?

                                                    • Re: Complex Security problem
                                                      Marcus Sommer

                                                      Yes it's a static excel because we use DMS with custom users as authentication mode (the custom users/usergroups will be handled like NT users so that's the same from a section access point of view) and we have not often changes to them (mayby ones a year) because we use functional users like Region1, Region2 instead of personalized users.

                                                       

                                                      Whereby the import point was here the structure of the load and not the source of the data. You need just to change the excel with the results of your queries on the active directory. I think the following will be quite helpful for it: Section Access (Data Reduction) using Active Directory.

                                                       

                                                      - Marcus