Complex Authorization - How to create authorization bridge

[vitaliichupryna]

Hi Guys,

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.

First of all  in my opinion the most useful articles about complex section access is  Basics for complex authorization and Data Reduction Using Multiple Fields written by Henric Cronström.

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 %AuthID  From OrderDetails ;
    Load Region &'|'& Product as AuthID, Region &'|'&'<ANY>' as %AuthID  From OrderDetails ;
    Load Region &'|'& Product as AuthID, '<ANY>'&'|'& Product as %AuthID  From OrderDetails ;
    Load Region &'|'& Product as AuthID, '<ANY>'&'|'&'<ANY>' as %AuthID  From OrderDetails ;

In case of three reducing fields, we have eight combinations.

Number of combinations can be calculated by following formula:

C = 2ⁿ

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.

Steps:

1. Create table with one field and two values

Temp:
LOAD * Inline [
      Field
      Value
      <ANY>
];

Create table with Field combinations
TmpCombinations:
LOAD Field AS Field1 Resident Temp;
join LOAD Field AS Field2 Resident Temp;
join LOAD Field AS Field3 Resident Temp;
join LOAD Field AS Field4 Resident Temp;
DROP TABLE Temp;

TmpCombinations table:

Field1	Field2	Field3	Field4
<ANY>	<ANY>	<ANY>	<ANY>
<ANY>	<ANY>	<ANY>	Value
<ANY>	<ANY>	Value	<ANY>
<ANY>	<ANY>	Value	Value
<ANY>	Value	<ANY>	<ANY>
<ANY>	Value	<ANY>	Value
<ANY>	Value	Value	<ANY>
<ANY>	Value	Value	Value
Value	<ANY>	<ANY>	<ANY>
Value	<ANY>	<ANY>	Value
Value	<ANY>	Value	<ANY>
Value	<ANY>	Value	Value
Value	Value	<ANY>	<ANY>
Value	Value	<ANY>	Value
Value	Value	Value	<ANY>
Value	Value	Value	Value

2.      Create table with real field names

NoConcatenate
Combinations:
LOAD
if(Field1='Value', '[Region]',chr(39)&Field1&chr(39))      AS Field1,
if(Field2='Value', '[Product]',chr(39)&Field2&chr(39))     AS Field2,
if(Field3='Value', '[SubRegion]' ,chr(39)&Field3&chr(39))  AS Field3,
if(Field4='Value', '[SubProduct]',chr(39)&Field4&chr(39))  AS Field4
Resident TmpCombinations;
DROP TABLE TblCombinations;

Table with real field Names:

Field1	Field2	Field3	Field4
'<ANY>'	'<ANY>'	'<ANY>'	'<ANY>'
'<ANY>'	'<ANY>'	'<ANY>'	[SubProduct]
'<ANY>'	'<ANY>'	[SubRegion]	'<ANY>'
'<ANY>'	'<ANY>'	[SubRegion]	[SubProduct]
'<ANY>'	[Product]	'<ANY>'	'<ANY>'
'<ANY>'	[Product]	'<ANY>'	[SubProduct]
'<ANY>'	[Product]	[SubRegion]	'<ANY>'
'<ANY>'	[Product]	[SubRegion]	[SubProduct]
[Region]	'<ANY>'	'<ANY>'	'<ANY>'
[Region]	'<ANY>'	'<ANY>'	[SubProduct]
[Region]	'<ANY>'	[SubRegion]	'<ANY>'
[Region]	'<ANY>'	[SubRegion]	[SubProduct]
[Region]	[Product]	'<ANY>'	'<ANY>'
[Region]	[Product]	'<ANY>'	[SubProduct]
[Region]	[Product]	[SubRegion]	'<ANY>'
[Region]	[Product]	[SubRegion]	[SubProduct]

3. Calculate number of rows in our table (will use in cycle), after this we will be ready to load our data

Let vRowCount=NoOfRows('Combinations');

4. Load data in the cycle, for this we need create variable for every field

FOR i = 0 to vRowCount -1

vFieldName1 = peek('Field1',$(i),'Combinations');
vFieldName2 = peek('Field2',$(i),'Combinations');
vFieldName3 = peek('Field3',$(i),'Combinations');
vFieldName4 = peek('Field4',$(i),'Combinations');

AuthorizationBridge:

LOAD *

Where Exists (Auth, %AuthID);
LOAD Distinct

    AuthID,
     $(vFieldName1) & '|' & $(vFieldName2) & '|' & $(vFieldName3) & '|' & $(vFieldName4) as %AuthID,
Resident Orders;

Next i;

DROP Tables Combinations, Orders;

We load all possible combinations and not all are valuable, so we can add condition

Where Exists (Auth, %AuthID);

In the preceding load – it will keep only combinations that we have in the Authorization table.

At the end we have Authorization Bridge table.

Thanks,

Vitalii

[beck_bakytbek]

Thanks a lot for great example to issue: Access Section in QS