Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
atsushi_saijo
Creator II
Creator II

Table-level Security with Section Access "not feasible"?

Dear Gurus, 

I have a situation, where each group of tables is owned by different divisions. This ownership is indicated by REDUCTION field in each tables. Section Access defines who has access to what tables.  

But to use REDUCTION field, we are bound to create such relation with security table (=Section Access table). 

FGA Topic - Sanitized-Page-11.jpg

But this will create such a datamart behind Qlik Sense. 

Screenshot 2021-06-15 001313.jpg

Once we needed to link-up with FKs (foreign keys), we end up with such. 

FGA Topic - Sanitized-Page-11 (1).jpg

To the worse, these REDUCTION values are nothing to do with the business meanings: i.e. it completely destroy relational key (FK_1 and FK_2), by creating synthetic key. 

Screenshot 2021-06-15 001920.jpg

Now, Link Table is only effective if the model is simple and record numbers are small. If we take on 50-80 million distinct FKs with 2 FACTS + 8-12 DIM tables, Link Table should host all the combination of distinct values. This is no-go. 

In Qlik Sense, can we achieve "Table-Level Security" and FK relationships at the same time? (I doubt it.)

To elaborate this case more, here is the example. 

  • You have Section Access, with USERID and REDUCTION. 
  • FACT_SALES is owned by Sales team. 
  • DIM_COA is owned by Finance team. 

Users are setup as such: 

  • DOMAIN_USER_01 can access both tables. 
  • DOMAIN_USER_02 is an accountant, and only have access to DIM_COA. 
  • DIM_COA has one-to-many relationship to FACT_SALES. 

FGA Topic - Sanitized-Danger of Unpredictable (1).jpg

Now, we must implement user-access restriction based on table's REDUCTION, but still ensure the FK relationship. This results in such model, and no records is associatable. 

Screenshot 2021-06-15 004517.jpg

*This type of security can be easily implementable by the BI tool, that does credential passthrough to the base table.

In my opinion, there must be a compromise that:

  • This type of security is not possible in Qlik Sense. 
  • Alternative is to create a Reporting Datamart. Original security properties are overwritten by ETL, so that Security table and FACT table will handle security as single point. The rest of cascaded filtering can be slightly loose.

<This is the traditional Section Access, RLS>

FGA Topic - Sanitized-Page-11 (2).jpg

I think this is still acceptable. Is DIM data so secret? Usually no. They would be mostly some type of product, market segment, department, customer ....etc. It is the FACT that has to be confidential. 

If DIM has PII (Personally Identifiable Information), create a special composite key, that effectively nullify via the FK if user is unauthorized. 

But first of all, I am grateful if you possibly advise me about your opinion/view because you are probably far more experienced than me, a Spark data engineer. Thanks for your help.

Labels (2)
6 Replies
avinashelite

This is do able follow this below code approach, I have not used id's I have introduced new fields with the combination i.e. in each table create a new field like below e.g. SALES_Key etc 

 

Sales:
LOAD ID as Sales_ID,
Value as Sales_Value,
Upper('Y') as SALES_Key;
Load * Inline
[
ID,Value
1,1000
2,2000
];


Fin:
LOAD ID as Fin_ID,
Value as Fin_Value,
UPPER('Y') as FIN_Key;
Load * Inline
[
ID,Value
1,1000
2,2000
];


LINKTable:
LOAD SALES_Key as SALES,
SALES_Key,
'N' as FIN
Resident
Sales;

Concatenate( LINKTable)
LOAD 'N' as SALES,
FIN_Key as FIN,
FIN_Key
Resident
Fin;

 

 


Temp:
Load * Inline
[
A,U,S,F
Admin,A,Y,Y
User,B,Y,Y
User,C,Y,N
User,D,N,Y
];

SECTION Access;

STAR is *;

LOAD Upper(A) as ACCESS,
Upper(U) as USERID,
Upper(S) as SALES,
Upper(F) as FIN
Resident
Temp;

 

SECTION Application;

Drop Table Temp;

 

atsushi_saijo
Creator II
Creator II
Author

Dear Avinashelite, 

Thank you for commenting back with such speed. I examined your approach using Link Table.

In your approach, the LINKTable will need to host all the possible combination of values from Fin and Sales. If Fin (accounting) is 90 million rows (that often has corrections and manual entries) and Sales is 87 million rows, we basically need to create the relation set 90*87 million rows of distinct IDs = (yes in Sales/Accounting, Document IDs should not duplicate. Let's assume here all IDs (Primary Keys) are line level, and all distinct.) 

In addition, what if we needed to add additional 8 dimension tables? The link table will need to add additional combinations of values further. 90*87 mililion * 1650 (products) * 11 million (customer) * ..... 

So I conjecture it is very difficult to maintain Link Table to have table-level security. 

The code of yours produces such model (Temp will disappear with your full code): 

Screenshot 2021-06-15 061655.jpg

And indeed, Link Table is as such: 

Screenshot 2021-06-15 062426.jpg

I think this approach has limitation in scale. But how would you think? I am grateful for your further deliberations. 

marcus_sommer

I wouldn't try to develop the datamodel in the described direction else merging both fact-tables. Depending on your data and further requirements a simple concatenating of both tables may not enough else you may need multiple measures of joining and/or mapping appropriate information to the intended table-part and also various actions to clean and/or prepare the data there.

Further it may also helpful and/or necessary to move some of the dimension-information into the fact-table and also to merge some of the dimension-tables, too. Especially in regard to your rather complex section access you should apply it against a single table - ideally one of the dimension-tables instead of the fact-table but if it's easier there then there but not against multiple tables. To be able to implement such logic you may also need to change your section access information - maybe with some self-joins - to create an appropriate combined key against the datamodel.

Probably it sounds like a lot of work but I think it will be in each case easier as linking a section access against multiple tables which may in general be applicable in some ways but I assume that the disadvantages which comes with such particular measurements are especially by larger datasets too big. Therefore my suggestion to do it in the "classical" Qlik way.

- Marcus

atsushi_saijo
Creator II
Creator II
Author

Dear Marcus, thank you for the considerate comment. I have examined and produced my deliberation. I am grateful for your advice further.

I reached the same conclusion to avoid Link table due to the scalability.

Additional disadvantage of Link Table is the extra ETL in DWH. Even in the cloud-based MPP (Massively-Parallel processing), DISTINCT (practically QUALIFY ROW-NUMBER() OVER ..... or group by) often causes performance bottleneck. DWH team flags this as design flaw.

In the enterprise context, security policy further goes such as data source-level security, security on ETL process code (some source system are differently treated, and confidentiality differ), and most prominently source system. Each table are generated via different ETL, source system, and thus confidentiality level. It is very hard to implement with traditional Section Access.

The compromise is that if we secure FACT 100% (where most secret resides in a form of AMOUNT), the security in dim tables could be loose. I assume I have no other choice but to proceed with classic RLS of Section Access.

This is the motivation that SEC_BRIDGE is directly linked to FACT. FACT should contain the composite security key (illustrated below as single key, REDUCTION). In fact, even if we use DIM as bridge, it must contain the REDUCTION (=example), so in the end it produces the same result.

FGA Topic - Sanitized-Page-11 (2).jpg

In practice REDUCTION (example) is often composite, such as entity within the enterprise, source system, process# of ETL, ... all those codified and made as composite. They must be secured based on the security policy. In this instance, "table" (or memory object within Qlik Sense) must be secured individually, and these REDUCTION code has no relevance in business meaning.

You mentioned "Flattening" the dimensions into Fact table. I assumed that your point is to make DIM tables minimized so that link-table would have minimum keys. But this approach would be not taken in this time.

Thanks for your comment, and I am grateful for any further remarks you may find useful.

marcus_sommer

I'm not sure if it's really a weakness that you couldn't (easily) combine multiple security policies - from different data-sources over multiple ETL into varieties of end user views. Probably it's rather seldom that there exists really very carefully examined security policies which span over multiple modular structured data-levels - often the logics comes from times in which security hadn't the importance like nowadays and the demands to change and/or to extend the business-logics are so rapidly that you couldn't develop and/or adjust them in time.

This doesn't mean that data shouldn't be secured so well as possible but rather not with the thought of a " egg-laying woolly milk sow" else with more particular logics.

In Qlik it's for me to consider each datamodel as an own source which gets a best as possible (from the previous level) deduced access control without the expectation to transfer the security model 1:1. Very important in this regard is further not to combine data within a single datamodel which have more or less opposite security demands to each other else to keep the things as simple as possible even if it means to produce n different views (many users won't notice it at all and well explained with some straight forwarded usability - maybe per document chaining - it will be mostly acceptable).

- Marcus

atsushi_saijo
Creator II
Creator II
Author

Dear Marcus,

Thank you for the considerate thought. Much appreciated. This is very good point.

"to consider each datamodel as an own source ... without expectation to transfer the security model"

When datamart for Qlik Sense is created, the security should be applied brandnew (and much simpler), so that it satisfies specific analytic users. I think it boils down to the point that BI is created for specific business question. It is not another DWH.