How to set security permissions to Databases in databricks through Notebooks

Question

We are stuck on the way to set security permissions to Databases by using Notebooks %sql.

At first, let me explain our situations and settings. We run the following code on Notebooks:

%sql CREATE DATABASE X ; GRANT USAGE ON DATABASE X TO admins ; X.test_table (id STRING, num INT)

And the following status are set in Admin Console -> Access Control
[Enabled] Workspace Access Control/Cluster, Pool and Jobs Access Control/
Table Access Control/Personal Access Tokens
[Disabled] The rest of them

However, other uses who are not set as admins can refer its data test_table.
Is there any reason why we cannot set the security permission?

Any help would be appreciated!

Answer 1

Hello @Asuka ,

Welcome to the Microsoft Q&A platform.

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.

The requirements for managing object privileges depends on your environment:

• An administrator must enable and enforce table access control for the workspace.
• The cluster must be enabled for table access control.

Only High Concurrency clusters support table access control.

As an example, an administrator could define a finance group and an accounting database for them to use. To set up a database that only the finance team can use and share, an admin would do the following:

CREATE DATABASE accounting;  
GRANT USAGE ON DATABASE accounting TO finance;  
GRANT CREATE ON DATABASE accounting TO finance;  

With these privileges, members of the finance group can create tables and views in the accounting database, but can’t share those tables or views with any principal that does not have USAGE on the accounting database.

Reference: Azure Databricks – Data object privileges

Hope this helps. Do let us know if you any further queries.

------------

• Please accept an answer if correct. Original posters help the community find answers faster by identifying the correct answer. Here is how.
• Want a reminder to come back and check responses? Here is how to subscribe to a notification.