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

[Wojciech_BUK]

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.

[Wojciech_BUK]

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.

[Wojciech_BUK · Accepted solution]

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 😞