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.
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:
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:
-
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_accessorall_datasource_accesspermissions, although you mentioned this wasn't needed before. -
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.
-
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?