topic Re: Unity Catalog - Lakehouse Federation: Permission to read data from foreign catalogs in Administration & Architecture

Unity Catalog - Lakehouse Federation: Permission to read data from foreign catalogs

Wojciech_BUK — Tue, 02 Jan 2024 14:54:23 GMT

I have seup connection "SQL-SV-conn" to SQL Server and based on that connection I have created foreign catalog "FC-SQL-SV".

I have granted All permission on CATALOG to developers:

Use Catalog
Use Schema
Select

But they can not query table (e.g. by running SELECT * FROM FC-SQL-SV.my_schema.my_table statement ) and they got this error:

PERMISSION_DENIED: User is not an owner of Connection 'SQL-SV-conn'.

Is this a bug ?
Why users need Ownership over CONNCETION, this is not secure at all as I don't want End User to manage my connection.

Re: Unity Catalog - Lakehouse Federation: Permission to read data from foreign catalogs

Wojciech_BUK — Wed, 03 Jan 2024 13:25:25 GMT

Hi Kaniz,

Sorry but this is not what i am looking for.
This is Azure SQL server and I can connect and read data from it when I have ACL on both Unity Catalog objects:

Foreign Catalog (select)
and
Connection (Owner)

In my post i mentioned that users can't query SQL Server using Foreign Catalog when they are granted only Select and Use permission on it.
Error states that the end user need also OWNER on CONNECTION that was created in Unity Catalog and if i grant it - they can read the data.

But in my option this is highly insecure.

Imagine if you have Table in Unity Catalog and you have to grant both "SELECT" on table for and user as well as ONWER on External Location and Creadentials -> then entire ACL won;t make any sense.

Re: Unity Catalog - Lakehouse Federation: Permission to read data from foreign catalogs

Wojciech_BUK — Thu, 04 Jan 2024 08:42:08 GMT

OK, I have found out the answer in below docummentation:

https://learn.microsoft.com/en-us/azure/databricks/query-federation/#limitations

Single-user access mode is only available for users that own the connection.

So when I use e.g. Job Cluster that runs in single access mode, then princiapal running the job must be Owner of Connection, thats is sad 😞