cancel
Showing results for 
Search instead for 
Did you mean: 
Data Governance
cancel
Showing results for 
Search instead for 
Did you mean: 

Databricks SQL View with table access control

LukaszJ
Contributor III

Hello,

I want to grant 'select' option on some columns in my table.

So i created view, and now people can access my view but cannot access the table.

However, they cannot do 'select' on the view, because they do not have privileges to the table.

What can I do in this situation?

Best regards,

Łukasz

1 ACCEPTED SOLUTION

Accepted Solutions

NM
New Contributor III

In that case, you need to grant SELECT permission on table too. I'm not sure if you can only grant permissions on only some columns. It will be on all columns of the table if permission is given on table.

View solution in original post

12 REPLIES 12

Kaniz
Community Manager
Community Manager

Hi @Łukasz Jaremek​, By default, all users have access to all data stored in a cluster’s managed tables unless table access control is enabled for that cluster. Once table access control is enabled, users can set permissions for data objects on that cluster.

This feature requires the Premium Plan. This feature requires a Data Science and Engineering cluster with an appropriate configuration or a Databricks SQL endpoint.

This section covers:

Hubert-Dudek
Esteemed Contributor III

If the owners are not the same, the user (one which has SELECT on VIEW) must also have  SELECT privileges on the underlying table.

Please check that owner of the view and table are the same. If not, please set it to the same owner (user or group).

ALTER SCHEMA <schema-name> OWNER TO `<user-name>@<user-domain>.com`

Dear Hubert,

Thank you for the answer.

To my mind, It does not make sense.

So how can I only share some of the columns in the table with someone?

The problem is that the table is fed frequently. It seems strange to create a new one (without a few columns) after every update of the first one.

Do you have any advice?

Best regards,

Łukasz

Thanks for the tip regarding the view and the owner needing to have the same owner - this resolved my issue!!

01_binary
New Contributor III

For best practice, grant usage on database and SELECT on view. They don’t need to have access to table.

Dear 01_binary,

I suppose it doesn't work with this combination.

Then the user (who does not have access to the table, but has view access) gets a message that the table is not authorized.

Best regards,

Łukasz

NM
New Contributor III

In that case, you need to grant SELECT permission on table too. I'm not sure if you can only grant permissions on only some columns. It will be on all columns of the table if permission is given on table.

Kaniz
Community Manager
Community Manager

Hi @Łukasz Jaremek​ , We haven’t heard from you on the last response from community members, and I was checking back to see if those suggestions helped you. Or else, If you have any solution, please share it with the community as it can be helpful to others.

LukaszJ
Contributor III

Dear Kaniz,

Yes you are right.

I am so sorry for the problem, but I do not have time to answer every day.

However, I will always give an answer, with a slight delay at most.

Best regards,

Łukasz

Kaniz
Community Manager
Community Manager

Hi @Łukasz Jaremek​, It's absolutely fine. Please take your time. Please let us know whether these suggestions helped you in any way.

LukaszJ
Contributor III

To sum up,

It is not possible to create a view based on a table that someone does not have permission to.

It's a pity, because this option would be very useful to limit access to batch-fed tables.

Thanks all for the help and your time!

Regards,

Łukasz

Kaniz
Community Manager
Community Manager

HI @Łukasz Jaremek​, Thank you for your excellent update. Please don't forget to click on the "Select As Best" button whenever you resolve your question.

Welcome to Databricks Community: Lets learn, network and celebrate together

Join our fast-growing data practitioner and expert community of 80K+ members, ready to discover, help and collaborate together while making meaningful connections. 

Click here to register and join today! 

Engage in exciting technical discussions, join a group with your peers and meet our Featured Members.