Complex Authorization - How to create authorization bridge
In this article I would like to share my experience with section access. I didn’t find articles in the community with similar solution and I hope it will be helpful for people that meet with complex authorization face to face first time.
As we can see in the articles in the simplest case, the authorization table can have only two columns: USER and reducing field (Region, etc.), but usually we have two or three columns for data reducing.
In case of two columns we have four combinations of %AuthID and need make our load four times:
Load Region &'|'& Product as AuthID, Region &'|'& Product as %AuthIDFrom OrderDetails ; Load Region &'|'& Product as AuthID, Region &'|'&'<ANY>' as %AuthIDFrom OrderDetails ; Load Region &'|'& Product as AuthID, '<ANY>'&'|'& Product as %AuthIDFrom OrderDetails ; Load Region &'|'& Product as AuthID, '<ANY>'&'|'&'<ANY>' as %AuthIDFrom OrderDetails ;
In case of three reducing fields, we have eight combinations.
Number of combinations can be calculated by following formula:
C = 2n
because every field can be in two states: <ANY> or Value
In my case I had 8 columns for authorization and 256 combinations, I was upset when understood that I should create 256 tables with different combinations manually to load my Authorization Bridge table correctly. I started looking for a solution, below you can see script that solve the problem. In the example I use only four field because I want show the general idea.
1. Create table with one field and two values
Temp: LOAD * Inline[ Field Value <ANY> ];
Create table with Field combinations TmpCombinations: LOADFieldASField1Resident Temp; joinLOADFieldASField2Resident Temp; joinLOADFieldASField3Resident Temp; joinLOADFieldASField4Resident Temp; DROPTABLE Temp;