What we have:

Databricks Workspace Premium on Azure

ADLS Gen2 storage for raw data, processed data (tables) and files like CSV, models, etc.

What we want to do:

We have users that want to work on Databricks to create and work with Python algorithms. We do have central tables were raw data gets ingested (by jobs) and is used by the users to feed the algorithms.

These central tables are maintained by the administrators and should only be readable by the users. In the process of the development the users also need to work with other files (e.g. CSV, model files, etc.) and therefore need a access to files stored on ADLS.

Now the users really like the ability to switch between SQL, Python and so on. Extract the needed data from the central tables with SQL in a DF and convert to Pandas do their stuff and potentially bring it back to a table.

The main problem is that I cannot combine the Table Access Control and the Credential Passthrough (Limitations). The users should be on one hand able to only see and query tables they have access to (not UPDATE, DELETE, DROP, etc.), on the other hand they should be able to freely work with files that are in another area (container) of the ADLS.

I have now spend quite some time to get my head around the options to limit access within the workspace in order to separate different groups of users and their respective access. However I have not found the ideal solution, yet.

This is what I have tried.

Table Access Control

Limits the access on certain tables/views/etc. but work only on HC clusters and further access to files is only possible with a mount using a service principal. All other users will also have access to the data. -> not an option

Credential passthrough

Works and limits the access of an individual user on a mount as well as directly (abfss://...). I can create a DB with MANAGED tables that use the mount as LOCATION. -> great but

Jobs cannot impersonate and therefore access the mount and therefore the table. I have tried to add a service principal user (scim-sp) and change the job owner to the service principal. But that did not work. The only option I see is to create the tables not on a mount but directly with a LOCATION abfss://... But ideally I would like to work with MANAGED Tables that use a mount as a source -> anyway

Problem here is that the users see databases and tables that they potentially do not have access (in the ADLS). And worse they can drop tables and DBs (not the underlying data though). And worse worse they could create a table with the same name but in another location and therefore screw up other processes/jobs that run with data from those tables.

JDBC/ODBC Connection

FYI: Tables that are MANAGED and located on a mount with credential passthrough can not be accessed via JDBC. They have to be located with abfss:// and the service principal key configuration (see best practices) has to be in the cluster spark config.

So this is my situation, did I miss some option here. Any ideas are welcome.

Links:

Best Practices: https://docs.microsoft.com/en-us/azure/databricks/security/data-governance

Table access control: https://docs.microsoft.com/en-us/azure/databricks/security/access-control/table-acls/

Credential passthrough: https://docs.microsoft.com/en-us/azure/databricks/security/credential-passthrough/adls-passthrough