Skip to main content

Privacera Documentation

Table of Contents

Example: Manage access to Databricks SQL with Privacera

Now that you have Privacera installed, this document helps you to start working with Privacera to set up access controls for Databricks SQL. Common uses of Privacera to create policies and enforce those policies in Databricks SQL are detailed.

You want to ensure that your data access is consistent and controlled across your computing ecosystem.

Implementing data access policies through Privacera Access Management gives you a single pane of glass to see what data your users can access.

By default in Privacera, your data is not accessible. You can define data access policies that permit only authorized users to access your data. When you define a policy, you apply it to a particular Privacera-connected system that holds your data.

Privacera has several different kinds of policies, such as tag policies and resource policies. Creating resource policies is detailed here, because they are the most basic and easiest to create.

About the data in these examples

These examples rely on TPC DS. The table involved is CUSTOMER.

About users, groups, and roles

These examples show a user named Emily (with username emily), who is in the data_analysts group and has the Privacera role DATA_ANALYST.

What has already been set up

These examples assume that the following prerequisites are already set up:

  • Your account administrator has connected Databricks SQL.

  • Users, groups, and roles have already been created.

Enable access to entire table for a user

Your users need access to the TPC DS CUSTOMER table. They are currently prevented from seeing it. By default in Privacera, your data is not accessible.

UC-dbxsql-error.png

The Privacera audit log also shows that they do not have access:

UC-dbxsql-auditerror.png

You need to create a resource policy in Privacera that gives Emily all access to the CUSTOMER table.

In this example, you should have the following information ready:

  • Name of the user to give access to

  • Name of the database

  • Name of the table

  • Name of the column

Create a policy to give a user access to a table
  1. In Privacera, expand Access Management and click Resource Policies.

  2. Under DATABRICKS_SQL_ANALYTICS, click the privacera_databricks_sql_analytics link.

    databricks_sql_analytics_policy_repo.png

    Lists of policies are displayed on following tabs:

    • ACCESS

    • MASKING

    • ROW LEVEL FILTER

  3. On the ACCESS tab, click Add New Policy.

    The Policy Detail page is displayed.

  4. Enter a unique policy name.

    Enabled/Disabled: Accept the default, which is Enabled.

    Normal/Override: Accept the default, which is Normal.

  5. Enter a descriptive policy label that helps you find this policy when searching for policies and filtering policy lists.

  6. From the global pulldown, select database.

  7. Enter the required database name.

    This example uses tpcds.

  8. Enter the required table name.

    In this example, we apply policy to the CUSTOMER table.

  9. Enter the required column names.

    Because this policy is to give access to the entire table, this example uses the * wildcard, which indicates all columns.

    UC-dbxsql-1emily_access_policy.png
  10. Ignore Add Conditions.

  11. Enter a description of the policy to identify it among other policies.

  12. Enable/disable Audit Logging: Accept the default, which is Yes.

  13. In the Allow Conditions section, under Select User, enter the name of the user and add permissions for that user. This example is for user emily and all permissions.

    UC-dbxsql-2emily_permissions.png
  14. Click Save to complete the new policy.

Verify the policy in Databricks SQL

To see if the policy has been applied, log in to the database and enter the following SQL command:

select * from tpcds.customer      

The results show success. The entire table is now visible.

UC-dbxsql-success.png

The Privacera audit log also shows success:

UC-dbxsql-3auditsuccess.png

Hide a column from a group of users

The data analysts group needs access to the CUSTOMER table, but they should not be able to see customers' phone numbers.

This organization has an internal directive that requires that customer phone numbers and other PII must be protected and shielded from employee view. This internal directive is to comply with regulatory requirements.

The values of the C_PHONE column are currently visible and must be hidden:

UC-dbxsql-column_allowed.png

You need to create a policy that masks the values in the C_PHONE column from the data analysts group.

In this example, you should have the following information ready:

  • Name of the group to give access.

    In this example, the group is data_analysts.

  • Name of the database.

    In this example, the database name is tpcds.

  • Name of the table.

    In this example, the table is CUSTOMER.

  • Name of the column.

    In this example, the column is C_PHONE.

Create a policy to mask a column from a group
    1. In Privacera, expand Access Management and click Resource Policies.

    2. Under DATABRICKS_SQL_ANALYTICS, click the privacera_databricks_sql_analytics link.

      databricks_sql_analytics_policy_repo.png

      Lists of policies are displayed on the following tabs:

      • ACCESS

      • MASKING

      • ROW LEVEL FILTER

    3. On the MASKING tab, click Add New Policy.

      The Policy Detail page is displayed.

    4. Enter a unique policy name.

      Enabled/Disabled: Accept the default, which is Enabled.

      Normal/Override: Accept the default, which is Normal.

    5. Enter a descriptive policy label that helps you find this policy when searching for policies and filtering policy lists.

    6. Select the required database.

      In this example, the database name is tpcds.

    7. Select the required table.

      In this example, the table is CUSTOMER.

    8. Select the required column.

      In this example, the column is C_PHONE.

      UC-dbxsql_policy_column.png
    9. Ignore Add Conditions.

    10. Enter a description of the policy to identify it among other policies.

    11. Enable/disable Audit Logging: Accept the default, which is Yes.

    12. In the Allow Conditions section, select a group and add permissions for that group.

    13. Click Save to complete the new policy details.

    14. Privacera displays a message that you must have an access policy to accompany the policy you are creating. See Enable access to entire table for a user for how to create an access policy.

      In that access policy, make sure you remove the Data Admin permission for the user. Otherwise, the user can see the original, unprotected database.

      UC-dbxsql_remove_dataadmin.png

    Secure database view created by Privacera

    Databricks SQL does not have the native capability to create column masks or row filters.

    For this reason, Privacera creates a secure view of the original database and applies policy to that secure view. The name of a secure view is:

    originalDatabaseName _secure

    In this example, the name of the secure view is tpcds_secure.

    In Privacera, the access policy itself must always specify the name of the original database, not the secure view.

    Note

    You should tell users the name of the secure view for their queries and that access to the original database is no longer allowed.

    Verify the policy in Databricks SQL

    To see if the policy has been applied, log in to the database and enter the following SQL command against the secure view tpcds_secure:

    select * from tpcds_secure.CUSTOMER      

    The results show success. The values of the C_PHONE column are not visible:

    UC-dbxsql-nulled_c_phone.png

    Display only rows with a specific value to a user role

    The CUSTOMER table includes a column named C_MKTSEGMENT. The data analyst group's users need to see only rows that relate to the HOUSEHOLD market segment.

    For users with the Privacera role DATA_ANALYST, you need to create a policy to filter out only rows whose C_MKTSEGMENT column has the value HOUSEHOLD.

    In this example, you should have the following information ready:

    • Name of the pertinent role.

      In this example, the role is DATA_ANALYST. This is a custom role name.

    • Name of the database.

      In this example, the database name is tpcds.

    • Name of the table.

      In this example, the table is CUSTOMER.

    • Name of the column and its value to filter.

      In this example, the column is C_MKTSEGMENT and the value for the row filter is HOUSEHOLD.

    Create a new policy for a role
    1. In Privacera, expand Access Management and click Resource Policies.

    2. Under DATABRICKS_SQL_ANALYTICS, click the privacera_databricks_sql_analytics link.

      databricks_sql_analytics_policy_repo.png

      Lists of policies are displayed on the following tabs:

      • ACCESS

      • MASKING

      • ROW LEVEL FILTER

    3. On the ROW LEVEL FILTER tab, click Add New Policy.

      The Policy Detail page is displayed.

    4. Enter a unique policy name.

      Enabled/Disabled: Accept the default, which is Enabled.

      Normal/Override: Accept the default, which is Normal.

    5. Enter a descriptive policy label that helps you find this policy when searching for policies and filtering policy lists.

    6. Select the required database.

      In this example, the database is tpcds.

    7. Select the required table.

      In this example, the table is CUSTOMER..

      UC-dbxsql-policy_top_half.png
    8. Ignore Add Conditions.

    9. Enter a description of the policy to identify it among other policies.

    10. Enable/disable Audit Logging: Accept the default, which is Yes.

    11. In the Allow Conditions section, select a role and the row level filter for that role.

      The role in this example is DATA_ANALYST.

      The filter should be entered as follows. The value is quoted:

      columnName =" value "

      In this example, the filter is: C_MKTSEGMENT="HOUSEHOLD".

      UC-dbxsql_bottom_filter_mkt_sgment_household.png
    12. Click Save to complete the new policy details.

    13. Privacera displays a message that you must have an access policy to accompany the policy you are creating. See Enable access to entire table for a user for how to create an access policy.

      In that access policy, make sure you remove the Data Admin permission for the user. Otherwise, the user can see the original, unprotected database.

      UC-dbxsql_remove_dataadmin.png

    Secure database view created by Privacera

    Databricks SQL does not have the native capability to create column masks or row filters.

    For this reason, Privacera creates a secure view of the original database and applies policy to that secure view. The name of a secure view is:

    originalDatabaseName _secure

    In this example, the name of the secure view is tpcds_secure.

    In Privacera, the access policy itself must always specify the name of the original database, not the secure view.

    Note

    You should tell users the name of the secure view for their queries and that access to the original database is no longer allowed.

    Verify the policy in Databricks SQL

    To see if the policy has been applied, log in to the database and enter the following SQL command against the secure view tpcds_secure:

    select * from tpcds_seecure.CUSTOMER      

    The results show success. Only rows with market segment HOUSEHOLD are displayed:

    UC-dbxsql-household_result.png