In this video,we will talk about data object privileges in Databricks. We will understand the Databricks data governance model. And we will learn how to manage permissions for different data objects like databases, tables and views.
The data governance model in Databricks lets us programmatically grant, deny and revoke access to our data from Spark SQL. Here is a general command that allow us to give an access privilege on a specific data object to a user or group of users. For example, we can give read access, translated by the ability to do a SELECT operation, on a table called my_table. And this permission is assigned to user number 1. So this table here is a data object on which we set permissions. Let us see what our other object types we have in Databricks. Databricks allows us to configure permissions for the following object types.
Catalog to control access to the entire data catalog, and schema to control access to a database. We can also control access to a table, no matter managed or external table, to a SQL view, or to a named function. In addition, with the ANY FILE keyword, we can control access to the underlying file system. Okay, now we know the data objects on which we can configure privileges. Let us see what are those privileges ?The following privileges can be configured on the data objects.
SELECT which gives read access to an object. MODIFY gives ability to add, delete and modify data to and from an object. CREATE gives the ability to create an object. For example, a table in a database. READ_METADATA gives the ability to view an object and it is metadata. And we have also the USAGE privilege, which in reality does not give any ability, however, it is an additional requirement to perform any action on a database object. And lastly, ALL PRIVILEGES that gives all the above privileges in the same time.
In order to be able to grant a privileges on an object, We need to be either Databricks administrator or the object owner. So if we are a database administrator, we can grant access the privilege for all objects in the catalog and in the underlying file system. Catalog owner can grant a privilege for all objects in the catalog. While the database owner can grant privileges for all objects only in that database. And the table owner can grant the privileges only for the table itself. And of course, similar rules are applied for views and functions.
Finally, in addition to grant operation, We have also other useful operations to manage object privileges. For example, We can deny and revoke privileges. And also we are able to show the granted permissions on objects using SHOW GRANTS operation.
In this demo, we will explore how to manage permissions for databases, tables and views in Databricks SQL. Navigate to the Databricks SQL Workspace. Make sure that our SQL warehouse is running. And now navigate to the SQL editor in the left sidebar.
In this demo, we will create a database called HR DB. And within this database, we will create a table called employees with ID, name, salary and city columns.
Then we will insert some data in this table.
And lastly, we will create a view for employees in Paris city.
Let us run these commands. Now, in order to allow other user to access this new database and table, let us configure their permissions.
We will create a new query. Let us start by granting several privileges on the whole HR database to a group of users called HR Team.
GRANT SELECT, MODIFY, READ_METADATA, CREATE ON SCHEMA hr_db TO hr_team;
GRANT USAGE ON SCHEMA hr_db TO hr_team;
GRANT SELECT ON VIEW hr_db.paris_emplyees_vw TO `[email protected]`;
So all the members in this group will have the ability to read, and modify the data, access metadata information and also the ability to create a new object like tables and view in this database. Let us run this command. Now the HR team has the necessary privileges.
GRANT USAGE ON SCHEMA hr_db TO hr_team;
However, for users to perform any action on a database object, they must have an additional privilege which is the USAGE privilege. Without this privilege, the objects in the database cannot be used.
We can run a specific SQL command simply by selecting it and click Run Selected.
We can also assign privileges to individual users.
GRANT SELECT ON VIEW hr_db.paris_employees_vw TO `[email protected]`;
Here, for example, we are granting a read access on our view object to a user from outside of the HR team. Let us select this query and click Run Selected. Lastly, let us review the assigned permissions using the SHOW GRANTS command.
SHOW GRANTS ON SCHEMA hr_db;
The HR team has all the granted privileges.
And we are the owner of this database as we were the one who created it. We can also show the granted privileges on our view.
SHOW GRANTS ON VIEW hr_db.paris_employees_vw;
Here we can see the user Adam indeed has the SELECT privilege on this view. And the HR team inherited the database privileges.
In addition to SQL editor here, we can also use the Data Explorer to manage permissions. From the left side navigator select the Data tab to access the Data Explorer.
The Data Explorer allows users and admins to navigate different data objects like databases, tables and views, explore data schema, metadata and history. In addition to setting and modify permissions. From here we can find the database we created previously. And by clicking on the database name, it displays a list of the containing tables and views on the left hand side. On the right, we will see some details about the database, like the owner information.
Use the Permissions tab to review who currently has permissions on this database. As expected, we see here the granted privileges for the HR team group. we can select a privilege here and click on S to remove this privilege. The privilege has been successfully revoked.
From here, we can also change the owner. If we click here. we have the option to edit the owner. An owner can be set as an individual or a group.
Let us set the owner to admins, which is the default group containing all workspace's administrators. As we can see, the admin group now is the owner of this database. Of course, from this window we can also grant permissions.
Let's say we would like to allow all user to review metadata about this database. We click the Grant button. We select All Users groups. And we choose both READ_METADATA and USAGE privileges. Click on Grant. And now we see here the granted privileges to the Users group.
Similarly, we can manage permissions for tables and views. Simply, we click on the table name. And then, we click on the Permissions tab. From here.
Let us, for example, give all users the ability to query this table. We click first on Grant button. We select all users group. And we chose the selective privilege. Lastly, we click Grant. Now all users can query this table. As we can see, the Data Explorer is really useful and powerful tool to manage your data objects.
<aside> 💡
However, at present only the ANY FILE object cannot be set from the Data Explorer. We need to use the SQL editor instead.
</aside>
And what's interesting about Databricks is it is that we can see all the SQL queries run behind the Data Explorer. Simply, navigate to the Query History in the left sidebar. As we can see, query history shows all the queries run in the Databricks SQL, including the Data Explorer.
In the previous lectures, we discussed the data governance model of Databricks Hive metastore. In this lecture, we will see an overview of Unity Catalog, which is the new governance solution of Databricks platform. You will understand Unity Catalog
and it is architecture. And we will see the three level namespace introduced by Unity Catalog. Lastly, we will describe the security model of Unity catalog.
Unity Catalog is a centralized governance solution across all your workspaces on any cloud. It unifies governance for all data and AI assets in our Lakehouse including files, tables, machine learning models and dashboards. And these can be simply achieved using SQL language. So with the Unity catalog, we define our data access rules once across multiple workspaces and clouds.
Before Unity Catalog, users and groups were defined per workspace. Also, access control was managed via the Hive metastore within the workspace. By contrast, Unity Catalog sits out of the workspace and is accessed via a user interface called the Account Console. Users and the groups for Unity Catalog are managed through this account console and assign it to one or more workspaces. Metastores are likewise separated out of the workspace and managed through the account console where they can be assigned to the workspaces.
A Unity Catalog metastore can be assigned to more than one workspace, enabling multiple workspaces to share the same DBFS storage and the same access control lists.
We saw previously the traditional two level namespaces used to address tables within the schemas. Unity Catalog introduces a third level, which is catalogs.
Let us understand better the hierarchy of Unity Catalog. The metastore is the top level logical container in Unity catalog. It represents metadata, that is, information about the objects being managed by the metadata, as well as the access control list that governs access to those objects.
In a metastore, we have catalogs, which is the top level container for data objects in Unity Catalog, and forms the first part of the three level namespace we just saw. Don't confuse Unity Catalog metastore with the Hive metastore. The Hive metastore is the default metastore linked to each databricks workspace. And while it may seem functionally similar to a Unity catalog metastore, Unity Catalog metastores offer improved security and advanced features. Unity Catalog metastore can have as many catalogs as desired.
Catalogs contain schemas. A schema, also known as a database, is the second part of the three level namespace. Schemas usually contain data assets like tables, views and functions, forming the third part of the three level namespace.
Unity Catalog also supports authentication to the underlying cloud storage through Storage Credentials. Storage Credentials apply to an entire storage container. On the other hand, External Locations represent the storage directories within a cloud storage container.
In addition, Unity Catalog adds Shares and Recipients which are related to Delta Sharing. Shares are collections of tables shared with one or more recipients. Delta sharing is out of scope for this course.
In Unity Catalog, we have three types of identities or principals which are users, service principals and groups.
Users are individual physical users which are uniquely identified by their email addresses. A user can have an admin role to perform several administrative tasks important to Unity catalog, such as managing and assigning metastores to workspaces and managing other users.
A Service Principal is an individual identity for use with automated tools and applications. Service Principals are uniquely identified by Application ID. Like users, Service principals can have admin roles which allow them to programmatically carry out administrative tasks.
Lastly, we have groups that collect users and service principals into a single entity. Groups can be nested with other groups. For example, a parent group called Employees can contain two inner groups: HR and Finance groups. Databricks identities exist at two levels: at account-level and at workspace-level.
Unity Catalog supports a feature called Identity Federation, where identities are simply created once in the account console. Then they can be assigned to one or more workspaces as needed. So Identity Federation eliminates the need to manually create and maintain copies of identities at workspace-levels.
Unity Catalog has CREATE, USAGE, SELECT and MODIFY privileges. In addition, we have also privileges related to the underlying storage which are READ FILES and WRITE FILES which replace the ANY FILE privilege we saw previously with Hive metastore. Lastly, we have EXECUTE privilege to allow executing user defined functions.
SS putting all together, we can see here the security model of Unity catalog.
As we can see, Unity Catalog uses different security model than Hive metastores for granting privileges. There are different privileges types, and extra securable objects and principals.
Here, we continue to use GRANT statement in order to give a privilege on a securable object to a principle.
Unity Catalog is additive, this means that your legacy Hive metastore is still accessible once Unity Catalog is enabled. Regardless of the Unity Catalog metastore assigned to the workspace, the catalog named hive_metastore always provides access to the Hive metastore local to that workspace.
In addition to its centralized governance model, Unity Catalog also has a built-in data search and discovery. And also provides automated lineage where you can identify the origin of your data and where it is used across all data types like tables, notebooks, workflows and dashboards. And lastly, as we saw, Unity Catalog unifies existing legacy catalogs. So there is no hard migration needed when enabling Unity Catalog.
At the end in order to access the account console, you can log in as an account administrator via this link: https://accounts.cloud.databricks.com