cancel
Showing results for 
Search instead for 
Did you mean: 
Administration & Architecture
Explore discussions on Databricks administration, deployment strategies, and architectural best practices. Connect with administrators and architects to optimize your Databricks environment for performance, scalability, and security.
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Wojciech_BUK
Valued Contributor III

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.
4 REPLIES 4

Kaniz_Fatma
Community Manager
Community Manager

Hi @Wojciech_BUK, You may need to check the user account under which the service is running. By default, it may run as a Local System, trying to pass the machine name through as the login. You can try changing this to a least-privileged domain user or your account and then granting that u....

I hope this helps you resolve your issue. If you have any further questions, please feel free to ask me. 😊

Wojciech_BUK
Valued Contributor III

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
Valued Contributor III

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 😞

Kaniz_Fatma
Community Manager
Community Manager

I want to express my gratitude for your effort in selecting the most suitable solution. It's great to hear that your query has been successfully resolved. Thank you for your contribution.




 

Join 100K+ Data Experts: Register Now & Grow with Us!

Excited to expand your horizons with us? Click here to Register and begin your journey to success!

Already a member? Login and join your local regional user group! If there isn’t one near you, fill out this form and we’ll create one for you to join!