topic Re: Create multiple SQL warehouse with custom access control in Warehousing & Analytics

Create multiple SQL warehouse with custom access control

jay99 — Tue, 11 Jun 2024 23:04:52 GMT

We set up the SQL warehouse IAM role in the settings option. This is applied to all warehouses. How do I create sql warehouses with multiple IAM roles to maintain access control.

Re: Create multiple SQL warehouse with custom access control

Hana — Mon, 06 Jan 2025 16:58:45 GMT

I think I am trying to do the same thing, apologies if I've jumped on this and it's not (let me know and will remove this and start a new thread).

I have created muliple SQL Warehouses and assigned different Entra group permissions to each SQL Warehouse. Each of those groups have access to their own catalog schema (entra group permissions granted here too - Entra Group A has permissions to Catalog Schema A. Entra group B to Catalog Schema B etc). However, these schema's have views over the same source table. I have granted read permissions for each entra group to this source table location (ADLS2). The problem I have is when a user belongs to more than 1 group.

For example user Steve belongs to Entra Group A and Entra Group B. Group A has been granted permission to SQL Warehouse A and this is how Steve is connecting to the data in Power BI. However, because he is in both Group A and Group B, he can see both catalog schema's. The risk is that Steve saves data for Group B in the Power BI dashboard which should only have Group A data in.

How can I restricted the access from SQL Warehouse A to only see Catalog Schema A? Or is there another approach I should be taking?

Re: Create multiple SQL warehouse with custom access control

Walter_C — Mon, 06 Jan 2025 17:21:24 GMT

Does the user has permissions to Write on both catalog schemas? Or do he has only write access on Schema A and Select on Schema B? Is this using Unity Catalog right?

In regards original question you can only select one instance profile for the warehouses

Re: Create multiple SQL warehouse with custom access control

Hana — Tue, 07 Jan 2025 10:11:27 GMT

Hi, Yes I am using unity catalog.

Permissions are as follows:

Catalog Privilages:

Entra Group A = USE_CATALOG

Entra Group B = USE_CATALOG

Schema Privilages:

Entra Group A = USE_SCHEMA and SELECT on Schema A

Entra Group B = USE_SCHEMA and SELECT on Schema B

External Location Privilages:

Entra Group A = READ_FILES on main table location

Entra Group B = READ_FILES on (the same) main table location

Then there is a SQL (serverless) Warehouse for each entra group

Entra Group A = Can Use on SQL Warehouse A

Entra Group B - Can Use on SQL Warehouse B

Steve belongs to both Entra group A and B, but when he uses SQL Warehouse A, I only want him to be able to see the data that is returned from the view created in Schema A.

Re: Create multiple SQL warehouse with custom access control

Walter_C — Tue, 07 Jan 2025 12:47:14 GMT

Unfortunately there is no way to restrict the access the compute has, the restrictions are being performed via the users permissions. Only option here will be to submit a feature request through https://docs.databricks.com/en/resources/ideas.html#ideas