[GitHub] [incubator-superset] cbg-jahn commented on issue #7887: Filtering data per user on DB table level (Customer or Tenant Support)

[GitBox <gi...@apache.org>]

cbg-jahn commented on issue #7887: Filtering data per user on DB table level (Customer or Tenant Support)  
URL: https://github.com/apache/incubator-superset/issues/7887#issuecomment-518068692
 
 
   Re: row level security (RLS)
   
   Dear Apache Derby community:
   
   The RLS subject was discussed recently in H2 and HSQLDB forums.  The excerpt is provided below.
   
   [H2 database]
   
   20190426 row level security
   groups.google.com/forum/#!msg/h2-database/kn5E7B_70rQ/GkZ_dnACDAAJ
   
   [HSQLDB]
   
   20190704 SQL corruption, Filter clause
   [user guide] HSQLDB 2.5.0, page 101, 
   row level security (RLS), using the Filter clause.
   www.sf.net/p/hsqldb/discussion/73674/thread/fe3b588587
   
   Sincerely,
   Ray
   
   [design draft]
   
   HSQLDB 2.5.0, user guide, page 101,
   fine grained access control,
   Filter Where clause for RLS.
   
   Would H2 become capable of RLS by adding (Where clause) to the Grant statement?
   
   in syntax
   
   Grant ... on Table client_po to role_merchant
   Where ( client, USER() ) in (Select client, merchant from permission_table);
   
   in semantics
   
   Grant ... on Table client_po to role_merchant
   Where ( client, USER() ) in permission_table( client, merchant );
   
   The reasoning is provided below.
   
   [business case]
   
   We are looking for a terse solution to access control, with a differentiation metric based on the (assignment / association).
   
   Each client, or (client, division), is assigned to a group of merchants. The organization table (client, merchant) ensues. Company adjusts the groups (reallocating merchants) upon business change. In database each client should be served / handled only by assigned / authorized merchants at the given time.
   
   Two types of Grant syntax exist for access control.
   
   1. table based
      Grant ... on Table client_po to role_merchant;
   
   In table based approach, all merchants are allowed access to all client POs in the entire table. No differentiation among merchants. This is not enough.
   
   2. row based
      Grant ... on Table client_po to role_merchant
      Where ( client, USER() ) in permission_table( client, merchant );
   
   In row based approach, when the (Where ... in ...) predicate is supported in the Grant statement, the permission_table( client, merchant ) would control, ROW BY ROW, the access to the table of trading data. This RLS, based on differentiation metric of (client-merchant association), is what we need. This scenario of (client-merchant association) may be extended to financial industry, health care systems, insurance claims, etc., where agents are assigned / authorized to serve the designated institutional clients.
   
   Alternative designs could achieve similar security results. But the Where clause in HSQLDB 2.5.0 appears to be a clever winner hands down. It is terse, versatile, easy on code maintenance, and very importantly, easy on association management (personnel reallocation) in database.
   

----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
 
For queries about this service, please contact Infrastructure at:
users@infra.apache.org


With regards,
Apache Git Services

---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org