SQL Warehouse external (to Databricks) access patterns and suggestions

[dwfchu]

Hi All!

Has anyone encountered a situation where we need to setup data access for Unity Catalog tables for read access such as external data marts, dashboard tools and etc.

We are currently using Databricks to serve data to people in our organisation that are not onboarded onto Databricks and trying out the SQL Warehouse JDBC/DBC serving option to integrate traditional relational DB ETL tools and general SQL clients. Which works well, but.....

We need the users to have READONLY access and would like to have this via a non-human service account (through LDAP or AD) or a Databricks Service Principal.

We looking at these options, with the following PRO and CONS:

• A developer uses his/her personal account in Databricks to generate a PAT for their downstream system to use, works, but provides too much permissions for the users external to the environment
• Onboarding onto Databricks fully, will work well, and allow self-generation of PAT for authentication, but we will be giving them access to the environment unnecessarily
• Onboarding a service account from our AD/LDAP tree, likely not work as service accounts in our network do not have associated email addresses that can be easily accessed
• Creating a Databricks service principal and using OAuth for JDBC connection, may work, but not sure if this is recommended
• Creating a Databricks User and using OAuth for JDBC Connection, would practically work, but still require a login? and a valid email address

We are trying to leverage our local LDAP/AD group/user setup as much as possible, as its easier and also likely a better way to manage this aligned to the our standards for access management

[Kaniz]

Hi @dwfchu, Setting up data access for Unity Catalog tables in Databricks involves several considerations.

Let’s explore your options and weigh their pros and cons:

1. Personal Access Tokens (PATs):

   • Pros:
     • Quick and easy to set up.
     • Developers can generate PATs for downstream systems.
   • Cons:
     • Provides excessive permissions to external users.
     • Not ideal for read-only access.

2. Full Onboarding to Databricks:

   • Pros:
     • Seamless integration.
     • Self-generation of PATs.
   • Cons:
     • Grants unnecessary access to the entire environment.

3. Service Accounts from AD/LDAP:

   • Pros:
     • Aligns with local LDAP/AD group/user setup.
   • Cons:
     • Service accounts lack associated email addresses.
     • May not work effectively.

4. Databricks Service Principal with OAuth for JDBC:

   • Pros:
     • Recommended approach.
     • Allows fine-grained control.
   • Cons:
     • Requires careful setup.

5. Databricks User with OAuth for JDBC:

   • Pros:
     • Practical solution.
     • Requires a login and valid email address.
   • Cons:
     • Still involves user interaction.

Considering your preference for leveraging local LDAP/AD groups, I recommend exploring the Databricks Service Principal with OAuth for JDBC option. It strikes a balance between security and ease of management. You can configure it to provide read-only access while adhering to your access management standards.

Remember to thoroughly test your chosen approach in a controlled environment before rolling it out to production. 🚀