SQL Lab - permission enforcing based on schema access (Gamma roles)

[hscabral]

Bug description

There is a scenario here that seems to be a permission bug.

I have one Database connection, where I have multiple schemas. The schemas are identical in structure, but differ in data, of course.

I create Gamma users, and I create Gamma roles for different schemas. Gamma_A and Gamma_B, let's say. The only permission for each of these roles is schema access on [DB].[db_conn].[schema_A], etc.

In my use case, I need these Gamma users with access to SQL Lab. In it, the single schema assigned to the Gamma_A role is the only one that shows in the schema dropdown - this is correct. However, in the SQL query window, if I change the schema name in my SQL, I'm able to access the tables in other schemas.

Example:

SELECT * FROM schema_a.projects and SELECT * from schema_b.projects work equally. This Gamma user, however, only was assigned schema access on schema_a.

Screenshots/recordings

No response

Superset version

master / latest-dev

Python version

3.10

Node version

16

Browser

Chrome

Additional context

No response

Checklist

• I have searched Superset docs and Slack and didn't find a solution to my problem.
• I have searched the GitHub issue tracker and didn't find a similar bug report.
• I have checked Superset's logs for errors and if I found a relevant Python stacktrace, I included it here as text in the "additional context" section.

[dosubot]

Hey @hscabral! 👋 I'm here to help you with any bugs, questions, or contributions you have. Let's squash those bugs together!

I found a similar closed issue that might be relevant to your situation: Can't access database in SQL lab. This issue was referenced by another issue, Can't access database in SQL lab, which was last updated on September 20, 2024 ^[1].

Regarding your specific problem, it seems like there might be a discrepancy between the schema permissions enforced in the UI and during SQL execution. Superset enforces schema permissions during SQL query execution by checking if the user has access to the specific schema permission using the get_schema_perm function. This function generates the schema permission string based on the database, catalog, and schema names, and checks if the user has the necessary access rights ^[2][3].

If the permissions are not being enforced as expected, it might be worth checking if there are any known workarounds or patches in the Superset codebase for handling schema permission issues. There are functions like create_schema_perm and delete_schema_perm that manage schema permissions, which might be useful in addressing this issue ^[4].

To continue talking to Dosu, mention @dosu.