OMIT fields complex rule for a single user with two permission roles

[asheppardwork]

Hello Qlik Sense experts.  I am running into a bit of a brick wall and want to know if what I am attempting is even possible.  I am needing to use the Section Access of the Data Load Script to give different levels of access for the same user but for different records.  Details in the thread, but essentially I have a user John Doe; who needs to see the records for two managers Jane Doe and Bobby Doe.  John is allowed to see the hours for Jane Doe's direct reports but is NOT allowed to see the hours for Bobby's.  Is this even possible?

Please find attached the tables and a map of the tables after the load, the PREP_AUTHORIZATION table gets dropped since it is part of the Access granting.

The expected resulting table would look like this:

Code for the Section Access and associated tables:

[PREP_AUTHORIZATION]:

LOAD
'USER' AS ACCESS,
UPPER('SITE\'& ENT_ID) AS USERID,
'ALL' & '|' & UPPER(ENT_ID) AS %AUTH_ID,
'' AS OMIT,
GROUP

Resident
DEFAULT_ACCESS_HIERARCHY;

 Concatenate

LOAD
ACCESS AS ACCESS, 
UPPER('SITE\'& TRIM(USERID)) AS USERID,
'ALL' & '|' & TRIM(UPPER(ENT_ID))  AS %AUTH_ID,
'' AS OMIT,
GROUP


Resident 
Special_Access_Table
where ACCESS='USER';

 Concatenate

LOAD
ACCESS AS ACCESS, 
UPPER(TRIM(USERID)) AS USERID,
'ALL' & '|' & TRIM(UPPER(ENT_ID))  AS %AUTH_ID,
'' AS OMIT,
GROUP

Resident 
Special_Access_Table
where ACCESS='ADMIN';


[SECTION_ACCESS_BUILDER]:

LOAD 
ACCESS, 
USERID,
%AUTH_ID,
OMIT

Resident
PREP_AUTHORIZATION

where GROUP ='STANDARD';

Concatenate

LOAD 
ACCESS, 
USERID,
%AUTH_ID,
'HOURS' AS OMIT

Resident
PREP_AUTHORIZATION

where GROUP ='REDUCED';
 


Section Access;


Authorization:

LOAD 
ACCESS, 
USERID,
%AUTH_ID,
OMIT

Resident
SECTION_ACCESS_BUILDER;



Section Application;
Drop Table PREP_AUTHORIZATION;

[marcus_sommer · Accepted solution]

I think it's not possible - at least in this way. It has two reasons - at first is section access mainly a white-list logic and only the listed values will be kept and further each denying will overwrite each permissions. And here is your record-permissions and field-denying in a conflict with each other.

Direct solutions could depending on the entire requirements become quite complex and will require some adjustments within the data-model like loading the fields twice respectively n times and/or combining the fields to a single access-key and/or creating appropriate extra records within the dimensions/fact/access-tables.

In regard to your scenario I could imagine an approach of combining MGMT_NAME & '|' & HOURS and listing all wanted/possible values to them. Instead of integrating HOURS an extra 0/1 HOUR_FLAG may simplify the matter.

Pure technically there will be ways (nearly) each kind of access-control. But to keep the logic simple and maintainable it's often more suitable not to combine such requirements within a single application else to separate them and using specialized ones.

[asheppardwork]

Thank you marcus, this makes sense.  I was leaning this way already but wanted to be sure before completely giving up on the idea.  I have another idea using parts of the solutions based on a bridge table laid out in these articles below, but so far am still struggling with the syntax.

https://community.qlik.com/t5/QlikView-Documents/Complex-Authorization-How-to-create-authorization-b... 

https://community.qlik.com/t5/Design/A-Primer-on-Section-Access/ba-p/1465766

https://community.qlik.com/t5/Design/Basics-for-complex-authorization/ba-p/1465872

https://community.qlik.com/t5/Design/Data-Reduction-Using-Multiple-Fields/ba-p/1474917