Dashboards should be loaded with current user access rights, not with dashboard owner's access rights

[berlicon]

Bug description

User without access to database table can view data from table via dashboard created by another user who has rights to the table.

How to reproduce the bug

1. I have user1 with access rights to table1 from ClickHouse database
2. I have user2 with NO access rights to table1 from ClickHouse database
3. User1 created dataset, chart and dashboard for viewing data to table1
4. User1 shares dashboard with User2 - added it as dashboard's owner and dataset's owner
5. User2 can view data to table1. It is a bug. He does not have access (database grants) to table1

Superset version

3.0.0

[berlicon]

1. How should I grant access to dashboards to users so that the data is loaded under the access rights of the current user?
2. Or where/how should I change the Superset code to do the same thing (dashboards are loaded under the current user's permissions, not the dashboard creator's permissions)?

[mistercrunch]

What is/are user2's role? You'll want user 2 as Gamma presumably

[berlicon]

It does not matter what role in Superset User2 has. I want that if User2 viewing any dashboard that dashboard be loaded under User2 access rights. And if User2 does not have access rights for viewing data (these rights are stored in ClickHouse DB) he would get an error "Access denied".

[abhishekindigg]

@mistercrunch Even I provided Gamma role still they are not able to view Dashboards.

#28234

[mistercrunch]

It does not matter what role in Superset User2 has

Mmmh. Yes it does. Alpha and and Admin both have all_datasource_access. https://superset.apache.org/docs/security/#alpha

I'm not sure I understand you fully though, it's unclear to me whether you are referring to database permissions or superset permissions at times, please clarify. Just to clarify since there seems to be confusion around this, the typical way to access databases from BI tools is to use a service account that has general read access to everything you want to expose in the BI tools, and implement the restrictions using the security model in the BI tool.

There are ways to have Superset users "impersonate" database users, or use oauth so that each user can effectively have their database-defined user access. If that's what you're looking for there are recent developments in that area.

[berlicon]

@mistercrunch I meant we have Angular app where users can create ClickHouse databases. And we give grants (CRUD operations to tables) to these users with such SQL script: "GRANT SELECT ON "dbo"."DATA" TO 'user26'". Also we have Superset integrated to our app. And users can run SQL queries to some tables where they have grants for SELECT. Also Superset have dashboard functionality. And we have bug that if some user (admin) created dashboard for viewing data to some table he has access and share that dashboard to other users (business users) they could view data they do not have access. I checked that when business user view dashboard, page loaded data from database with dashboard's creator (admin) accout. But I want that dashboard should be loaded under role with current user. So we have users in Superset but their access rights to tables we store in ClickHouse database which Superset does not know. As I understand I have to change backend code of Superset on Python to implement this. But Python/React/Flask and Supersert are not my tech stack and I want help how and where I should modify Superset code. Or maybe we should create dashboards in Superset another way to support this behaviour (users can view data they have access only).

[mistercrunch]

Gotcha. The way that Superset works is typically that you create a database connection using a service account (I'm guessing that's probably what your admin person did), and then you configure your data access within Superset (associating a role to users and to database connections, schemas, tables and row-level-security (RLS) rules). Otherwise Superset doesn't really know what to show you when listing charts, dashboards and datasets, and whether it's safe to share a cache (in the case of RLS).

But, there's recent development around impersonation / oauth for databases. I don't think it's implemented for Clickhouse, but may be implementable if Clickhouse supports oauth. More information here: #27631

Note that a shortcoming of that approach is Superset, unless you duplicate some of the data access policy as Superset roles, will show you all objects (charts, dashboards, datasets, ....) and when you click on it you'll get a "NO ACCESS" error message, which isn't a great experience. But it sounded like this is the behavior you were expecting.

[mistercrunch]

Given the title of the issue, it sounds like berlicon's expectations are very different from the common expectations when configuring/using a BI tool. Unless I'm missing something.

[berlicon]

My expectations were that somehow advice me how to change code in Superset. May be here:
superset\tasks\async_queries.py
@celery_app.task(name="load_chart_data_into_cache", soft_time_limit=query_timeout)
def load_chart_data_into_cache(...)

OK. Let's close this ticket.