Skip to main content

PrivaceraCloud Documentation

Table of Contents

Access use-case - How do I give a user access to a table or restrict from running a SQL select query?

:

This demo will show how you can give yourself access to see the data in the sample table sales_data.

In the PrivaceraCloud web portal, open the Sales Data All Access policy. You can see the Catalog is set to your catalog, the schema is set to sales_schema, table to sales_data and column is set to * (asterisk).

This means we want to define a policy for the <catalog>.sales_schema.sales_data table across all the columns of the table. Scroll down to the Allow Conditions and you will notice your email id in the Select User section. Scroll up to the top and toggle the Enabled switch besides the Policy Name. This will disable the policy. Scroll down and save the policy.

Switch to your Databricks Workspace's SQL Editor and re-run the select query on the secure view,

select count(*) from <catalog>.sales_schema_secure.sales_table

You will get the following error message,

User does not have USE SCHEMA on Schema `<catalog>.sales_schema_secure`

Now run the following select query,

select * from <catalog>.sales_schema_secure.sales_table

You will get the same error message.

Now, go back to the policy in the PrivaceraCloud web portal, re-enable the policy and then re-run the query. You should be able to get the proper result this time as you have been granted access through PrivaceraCloud access policies.

Let's review what we did here. By modifying a policy in the PrivaceraCloud portal, we are able to restrict a user from accessing a table. You no longer need to know the intricacy of SQL Grant/Revoke statements. In PrivaceraCloud policy, notice that we are using a wildcard (asterisk) to grant permissions on all the columns. Similarly we can use a wildcard in the name of the table - we can say all tables starting with the word sales would be part of this policy. This will allow you to create a policy ahead of time that is even before a table is created in Databricks Unity Catalog. As soon as such a table is created, PrivaceraCloud will detect and apply the policy on it. Since all these policies are applied on Unity Catalog, the same policies will be applied on all the Databricks workspaces.

If you have another test user in Databricks workspace or a colleague, you can add her to the policy and check if she is able to access the sales_data view.