-
Notifications
You must be signed in to change notification settings - Fork 13.3k
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Filtering data per user on DB table level (Customer or Tenant Support) #7887
Comments
Issue-Label Bot is automatically applying the label Links: app homepage, dashboard and code for this bot. |
There have been conversations about this, we call this feature "row level security", but haven't implemented it yet. This would require a solid SiP (Superset improvement proposal) |
Hi, |
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 [HSQLDB] 20190704 SQL corruption, Filter clause Sincerely, [design draft] HSQLDB 2.5.0, user guide, page 101, Would H2 become capable of RLS by adding (Where clause) to the Grant statement? in syntax Grant ... on Table client_po to role_merchant in semantics Grant ... on Table client_po to role_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.
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.
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. |
@mistercrunch . I want to know if this feature is being developed.Beause I also need this feature.If this feature is being developed, when can it be online? |
Is it Available macros?At present, I control the data permission by such custom. I know it is not perfect, but it solves my current dilemma |
Can this be done though defining the data source? It will be safer and can be reused |
Currently this PR is in the works: #8699 - feel free to take a look and provide feedback. |
This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions. For admin, please label this issue |
Is your feature request related to a problem? Please describe.
Most enterprise data sets are divided by a major ID column that strictly groups data of customer accounts into silos that are . not matched in 99% of the use cases - let's call the column "account_id" for a simple example. User Alice is allowed to see data
"WHERE account_id = 1"
, Bob is allowed to see data"WHERE account_id=2"
and Chris can see"WHERE account_id in (2 ,3)"
This filter should be applied BEFORE any data hits dashboard or Slice logic. Ideally speaking one user should see only his data in the table - as if none else would use the system.
Describe the solution you'd like
It would be great to have a general mechanism to define a column filter that always matches a predefined user criteria. In the perfect implementation this would be a selection of the users metadata against a table column:
First go to" Sources/tables/Edit Table"
There will be a new tab "FILTERS"
Here you can select a column and a criteria the value of the column needs to match against. The criteria can be obtained from the user object in the easiest implementation. In the given example it would be:
Table: my_fact_table
Column: account_id
Criteria:
my_fact_table.account_id = user.account_id (direct single match)
OR
my_fact_table.account_id in user.account_ids (1:many match)
In some use cases even a combination of two of those columns would make sense (think about sellers and buyers and a deal incorporating a buyer_id and seller_id). But this might be the last 20%.
Describe alternatives you've considered
There's potentially some not as convenient work around possible with JINJA. The docs are suboptimal here and it is also very SqlLab focussed. It would be great to have some better documentation how to use JINJA in this use case and potentially even point out how to deal with the users metadata better (most users will likely also be using SSO and potentially get the control data from a different app using SSO/OAuth or even a REST call).
Additional context
The functionality does not describe a full tenant support, but the most basic and most central topic would be covered: Clean separation of data. This looks like be a basic ask of many users in the data world. Implementation effort should be minor considering templating can be used internally.
The text was updated successfully, but these errors were encountered: