I have another inquiry also regarding this. Previously 4.0.1 when I select for example select * from database_name.schema_name.table_name it was working fine.

I applied the fix but still cannot select with database_name access denied. I required to grant full access id: to do that which wasnt before.

Apache Superset Permission denied for sql access between databases

Dec  8 11:26:10 ef-extsuperset superset[3696623]: 2024-12-08 11:26:10,802:DEBUG:superset.sql_parse:Parsing with sqlparse statement: select * from abcm.dbo.temp ;
Dec  8 11:26:10 ef-extsuperset superset[3696623]: 2024-12-08 11:26:10,816:DEBUG:superset.stats_logger:#033[36m[stats_logger] (incr) SqlLabRestApi.execute_sql_query.error#033[0m
Dec  8 11:26:10 ef-extsuperset superset[3696623]: 2024-12-08 11:26:10,816:DEBUG:superset.stats_logger:#033[36m[stats_logger] (timing) SqlLabRestApi.execute_sql_query.time | 122.99154791980982 #033[0m
Dec  8 11:26:11 ef-extsuperset superset[3696616]: 2024-12-08 11:26:11,842:DEBUG:superset.stats_logger:#033[36m[stats_logger] (incr) log#033[0m

but I am able to select with dbo only:

Apache Superset Permission denied for sql access between databases

This needed for databases I have in the same instance and calling each other.

Originally posted by @Habeeb556 in https://github.com/apache/superset/issues/31183#issuecomment-2525557400

Comment From: dosubot[bot]

I found a similar closed issue regarding database access in SQL Lab, but no solution was found in the comments [1].

Regarding your current issue, it seems that after the update to version 4.0.1, you are encountering permission issues when trying to perform cross-database queries. The error message suggests that you need either all_database_access or all_datasource_access permissions to execute queries like select * from database_name.schema_name.table_name.

To resolve this, you might need to adjust the permissions for the user attempting the query. Here are some steps you can consider:

  1. Check User Roles and Permissions: Ensure that the user has the necessary permissions. If cross-database queries are required, you might need to grant all_database_access or all_datasource_access permissions, although you mentioned this wasn't needed before.

  2. Review Role Changes in Version 4.0.1: Since you mentioned this issue arose after updating to version 4.0.1, review any changes in the release notes or documentation regarding permissions and roles that might affect database access.

  3. SQL Lab Permissions: Ensure that the user has the SQL Lab access permissions, as these are required to execute queries in SQL Lab.

If these steps do not resolve the issue, you may need to investigate further into any specific changes in your Superset configuration or database settings that could be affecting access.

To continue talking to Dosu, mention @dosu.

Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Other

Comment From: rusackas

@Habeeb556 are you still facing this one? Or is @betodealmeida still looking at it? Was tempted to close as stale, but if there's a real permissions problem here, I'm not so inclined to.

Comment From: Habeeb556

@Habeeb556 are you still facing this one? Or is @betodealmeida still looking at it? Was tempted to close as stale, but if there's a real permissions problem here, I'm not so inclined to.

Hello @rusackas, unfortunately the issue still exists in version 4.1.2. I cannot query with the databasename as I do before and I need to grant the user full access to all schema not just for a specific schema.

Comment From: Habeeb556

Hi @betodealmeida,

I think I have identified the issue — it seems related to how catalogs were introduced in SIP-95. I am working with SQL Server (and sometimes Oracle), and when I run a query like:

SELECT * FROM db.dbo.table

Superset interprets db as the catalog, not the database, which causes confusion.

In superset/security/manager.py, the get_schema_perm method returns a schema_perm like:

[db].[db].[dbo]

This is incorrect in our case, as the first two parts are duplicates. I also noticed a comment in the code mentioning that:

"Because of this, the format used for permissions is different depending on whether a catalog is passed or not."

This behavior may be a problematic for analytics teams, as they frequently use joins and need to reference the database explicitly by name.

Could you please advise if there’s a recommended workaround or a potential fix for this?

Comment From: betodealmeida

Superset interprets db as the catalog, not the database, which causes confusion.

"Catalog" is just the generic name (and ANSI standard) for a SQL Server "database". It's a collection of schemas. Here's the terminology for different databases and the ANSI SQL standard:

Database System Part 1 Part 2 Part 3 Example
SQL Server Database Schema Object MyDB.dbo.Users
Oracle Database Link Schema/Owner Object PROD_DB.HR.EMPLOYEES
Trino Catalog Schema Table hive.default.customers
Presto Catalog Schema Table postgresql.public.orders
MySQL Database - Table ecommerce.products
PostgreSQL Database Schema Table myapp.public.accounts
BigQuery Project Dataset Table my-project.analytics.events
ANSI SQL Catalog Schema Object catalog.schema.table

This behavior may be a problematic for analytics teams, as they frequently use joins and need to reference the database explicitly by name.

Right, the reason why we did SIP-95 was to standardize this and introduce permissions for catalogs, to allow Superset to enforce cross-catalog queries. Before, there was no way for an admin to prevent users from doing this, other than enforcing permissions outside of Superset in the database itself.

Comment From: betodealmeida

@Habeeb556 when you say this query fails:

SELECT * FROM abcm.dbo.temp;

Can you clarify:

  1. What roles/permissions the user has?
  2. What is the default catalog the connection is using (it should be in the SQLAlchemy URI, I think)?

If the default catalog is something else than abcm, then to run the query above you would need one of these permissions (this is from memory, I could be wrong):

  • all database access
  • all datasource access
  • database access to the SQL Server DB
  • catalog access to abcm
  • schema access to abcm.dbo
  • dataset access to abcm.dbo.temp

If you don't have any of these permissions, then the query should fail.

Comment From: Habeeb556

@Habeeb556 when you say this query fails:

SELECT * FROM abcm.dbo.temp; Can you clarify:

  1. What roles/permissions the user has?
  2. What is the default catalog the connection is using (it should be in the SQLAlchemy URI, I think)?

If the default catalog is something else than abcm, then to run the query above you would need one of these permissions (this is from memory, I could be wrong):

  • all database access
  • all datasource access
  • database access to the SQL Server DB
  • catalog access to abcm
  • schema access to abcm.dbo
  • dataset access to abcm.dbo.temp

If you don't have any of these permissions, then the query should fail.

Yes, URI mssql+pyodbc://SuperSet:XXXXXXXXXX@abcm

The permission was schema access on [abcm].[dbo]

Comment From: Habeeb556

++ If I grant permission schema access on [abcm].[id:], this works but it is granting all schemas below this database

Comment From: betodealmeida

@Habeeb556 so you're saying that even if you have the schema access on [abcm].[dbo] permission this query still fails?

SELECT * FROM abcm.dbo.temp;

Comment From: Habeeb556

@Habeeb556 so you're saying that even if you have the schema access on [abcm].[dbo] permission this query still fails?

SELECT * FROM abcm.dbo.temp;

Yes, exactly

Comment From: betodealmeida

OK, that sounds like a bug. Let me take a look. Thanks!