Section access puzzle

[shelvinpatel]

Hi there,

I hope I will find the answer for my section access puzzle.  I have attached a sample application whichhas section access by Territory.

User: ADMIN – admin user, can see all territories and ispart of admin group

User: USERALL – can see all territories

User: USERUK – can see only UK territory data

User: USERUS – can see only UK territory data

User: REP_A_UK & REP_B_UK – can see only UK territorydata

User: REP_A_US & REP_B_US – can see only US territorydata

Based on above set up, sales rep in UK can see all data inUK for all products and same for US. This is not an issue as they should have visibility of whole operationin their own territory.  Other generalusers have same access, based upon territory.

So far it’s working. Now I need to extend this section access so that each territory rep cannot only see their territory data but also sales of their own product portfolioacross all territories.

For example:  REP_A_UKis based in UK therefore should see all data in UK but also he/she is responsiblefor product AA1 and AA2 therefore should able to see all sales in allterritories for those two products.

The general users should continue work based upon theterritory set up.  Looking forward to thesolution!!

Thanks, Shelvin

[hic · Accepted solution]

This can be achieved if you use an authorization key consisting of the concatenated Region and Product values. You also need to use an "ANY" symbol which  represents any product or any region. The authorization table will then contain two lines for the REP_UK_A:

Region=<ANY>/Product=AA1

Region=UK/Product=<ANY>

The ANY symbol can then be expanded to several Products or Regions using the subfield function.

See further the attachments.

/HIC

[shelvinpatel]

Hi Henric,

Thanks for the easy answer.  I haven't tried using subfield to generate addtional lines for all possible combination before.  Instead I was try through * (star) option which didn't work then trying to generate the authorisation table via script which wasn't the good option.  Your solution is much simpler.

I am using domain authentication therefore there will be no two entries for 'Reps' but instead 'Reps' will be part of a security group 'QV-UK' therefore two lines will be as below:

QV-UK, UK, ANY

REP-A-UK, ANY, AA1:AA2

This shouldn't be an issue but will let you know the outcome.

Thanks once again.

Shelvin

[shelvinpatel]

Hi Henric,

When I thought about your solution in more detail, I realise the one element of the security.  I have a central link table to link all data and some tables only link via Territory. i.e TerritoryCondition.  This is just one of them.  This is the reason, I was trying '*' option intially but wasn't working.  I can see your solution will have similar issue unless I add Product as dummy column in all tables and have dummy value.  Not ideal though.

Any suggestion.

Thanks, Shelvin

[hic]

I think it would be enough to define the AUTHORIZATION_KEY inside your linktable. The reduction will propagate correctly to all other tables. Branches that are linked only via Territory will not have any product information.

/HIC

[shelvinpatel]

I was defining authorisation key within link table but generating mapping table from actual dimension table therefore null was missing out.  I have changed the mapping table to be generated from link table as well and it has resolved the issue.  See attached. login is 'admin' and other user ids you can figure out in the script.

Although I haven't tried this change in my main application where link table has millions of record and concatenating on the field would be real test of Qlikview .

Thanks for your help.