topic Re: Create table for non-admins (Table Access Control cluster/workspace) in Data Engineering

Create table for non-admins (Table Access Control cluster/workspace)

Redkite — Thu, 09 Dec 2021 12:06:13 GMT

Is there a way for non admin (at workspace level) or users without having (SELECT, MODIFY on ANY File) to create tables (unmanaged/external) even though they are owner of the database in which they want to create tables in a Table Access Controlled cluster/workspace environment.

Or some how restrict them to create table (with option/location) on a certain location on the storage.

Giving (SELECT or MODIFY on ANY File) makes user semi admin as they can create table on any location the service principle has access on external Data lake.

Re: Create table for non-admins (Table Access Control cluster/workspace)

Hubert-Dudek — Thu, 09 Dec 2021 12:58:49 GMT

Question is do they (users) need to login to databricks at all? maybe they can just use databricks sql endpoints to query data? like here https://www.youtube.com/watch?v=jlEdoVpWwNc

Regarding access management probably @Prabakar Ammeappin and @Werner Stinckens know more.

Re: Create table for non-admins (Table Access Control cluster/workspace)

Redkite — Thu, 09 Dec 2021 13:15:45 GMT

@Hubert Dudek querying the table/view/data based on their Access via SQL end point or TAC cluster is not the problem.

Being able to create the table/view pointing to the datalake themselves is the problem, you need to be an admin (on work space level) or have SELECT and MODIFY on ANY File permissions to create a table.

SELECT and MODIFY on ANY File gives you access to create a table on any location on external Storage (on which Service principle has access) on which you may not have access via a table/view

e.g. let say there are 2 databases called Database1 and Database2 having a table each called Database1.Table and Database2.Table, user2 is owner of Database2 but can not view Database1.Table using table access control.

But for user2 to create a new table in his database called Database2.ANewTable he need SELECT and MODIFY on ANY File on workspace level which also gives liberty to create a new table called Database2.sometable which he can potentially point to datalke/Database1/Table on which he should not have any rights.

Re: Create table for non-admins (Table Access Control cluster/workspace)

Hubert-Dudek — Thu, 09 Dec 2021 13:33:48 GMT

only one idea which I have to restrict access to storage is to use credential passthrough so your user will have access (full or read only etc.) only to what is defined by IAM in azure https://docs.microsoft.com/en-us/azure/databricks/security/credential-passthrough/adls-passthrough

so every database will be on separate mount pointing to separate container in adls with separate access rights

Re: Create table for non-admins (Table Access Control cluster/workspace)

Redkite — Thu, 09 Dec 2021 13:41:13 GMT

That's correct, unlike table access control using credential passthrough all the user will see all the databases and table/view (but they wont be able to query some one else's table/view as it will fail with access error) , but then there is no control over dropping someone else's table from Hive, so based on my example above User2 will be able to run drop table on Database1.Table even though he can not run Select * From Database1.Table because it is created on a mounting point which user2 dont have access, but user2 can change hive megastore e.g. drop tables

Re: Create table for non-admins (Table Access Control cluster/workspace)

Hubert-Dudek — Thu, 09 Dec 2021 16:12:51 GMT

You can ask for access to preview of Unity Catalog

https://databricks.com/product/unity-catalog

Re: Create table for non-admins (Table Access Control cluster/workspace)

Redkite — Fri, 10 Dec 2021 09:55:52 GMT

1- According to Data-brick guys i spoke to unity Catalog "it is still at early stage for production workloads"

2- As per my understanding based on Unity Catalog discussion, it can cater for more granular permissions, but still who can manage the create object permissions is unclear is that granular (e.g. at least database level as well) or do you need to be admin at highest level to create objects.

Re: Create table for non-admins (Table Access Control cluster/workspace)

Blackwell15 — Wed, 15 Dec 2021 08:58:17 GMT

Grant privileges on all the explain tables to non admin user as ... where BIADMIN is the non admin user who wants to generate explain plans.

AdvancedMD Login