I understand how to implement a basic section access requirement that will restrict data on a single column for specific users, I have done this and it works fine. As an example, I have an FX Dashboard where some users are restricted to see only certain Trading Desks, but others can see all of them, and anybody not in the first two groups is not allowed into the dashboard. This is working fine!
Now.... the requirements have changed!
1. Certain group of people can see everything (this part I'm ok with)
2. Some people have one, two, three or four restrictions in place.
A. They might be restricted to only one region
B. They might be restricted to see one or more business classes
C. They might be restrcited to see one or more products
D. They might be restricted to see one or more trading desks
I've created a section access table, but this is not working and I suspect it is related to my implementation method, if a user has access to one region, 1 business class, all products and all desks then I'm ending up with a table like this:
ACCESS
NTNAME
REGION
BUS.CLASS
PRODUCTS
DESK
USER
USERNAME1
EMEA
NULL
NULL
NULL
USER
USERNAME1
NULL
BASIC METALS
NULL
NULL
USER
USERNAME1
NULL
NULL
*
NULL
USER
USERNAME1
NULL
NULL
NULL
*
This doesn't work.....
I suspect that what I need is something like
ACCESS
NTNAME
REGION
BUS.CLASS
PRODUCTS
DESK
USER
USERNAME1
EMEA
BASIC METALS
*
*
But, what happens when I have multiple Products in this case, is the section access table going to contain a catersian product of all access?
I also briefly looked at other solutions on the forum for this, and some were recommending using something like an Authorisation Key which joins all of the above together, and creates a CONCAT of all products as part of this. My problem with this solution is that I cannot implement that on a star schema where all four of the above restrictions are held at the dimension level.
