Complex Security problem

[tomovangel]

  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

[tomovangel]

anything ?

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

[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

[tomovangel]

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

?

[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

[tomovangel]

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

?

[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

[tomovangel]

What can you suggest me to make this work?

[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

[tomovangel]

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;

???