Masking Sensitive Data

Objectives
After completing this lesson, you will be able to:

After completing this lesson, you will be able to:

  • Restrict access to columns containing sensitive data

Column Masking

When calculation views might expose sensitive data to the end-user, It is possible to define a mask on the corresponding column(s) so that only authorized users will be allowed to see the actual data. For other users, the mask will be applied in order to hide part or all of the column when previewing the calculation view data or querying it with a front-end tool.

Defining a Mask Expression

A mask expression is defined in a calculation view as follows:

  1. In the Semantics node choose the Columns tab.
  2. Select a column and choose the Data Masking icon in the toolbar
  3. Define the masking expression using SQL

Only columns of certain data types can be masked in a calculation view:

  • VARCHAR
  • NVARCHAR
  • CHAR
  • SHORTTEXT
Note
Masking is supported for both table types (ROW tables and COLUMN tables).

The mask expression can be either a constant string (for example: XXX-XXX-XXX) or an SQL expression using string functions and/or data from the source column.

For example, you can mask the middle part of a credit card number stored in column credit_card with the following mask expression:

Code snippet
LEFT(credit_card,4) || '-XXXX-XXXX-' || RIGHT(credit_card,4)
Copy code
With this expression, the credit card number 1111–2222–3333–4444 will be masked as 1111-XXXX-XXXX-4444.

Authorizing Access to a Masked Column

When a column of a calculation view is assigned a mask expression, the data of this column is masked unless the user has the UNMASKED privilege for this calculation view. This privilege is generally included in one or several roles defined in the HDB module, and this role is in turn granted to the end-user or, most often, to a role assigned to the end-user.

The UNMASKED privilege can be granted at two different levels, which corresponds to two different role definition syntaxes:

  • At the schema level

    The authorization to view unmasked data (the actual value) is given for an entire schema. In particular, if no schema is specified, the authorization affects all the calculation views defined in the container schema where the role is defined, which is the most common case.

    Code snippet
    {
    "role":
      {
        "name":"db::UNMASK_ENTIRE_SCHEMA",
        "schema_privileges":[{
          "privileges":["UNMASKED"]}]
      }
    }
    Copy code

    Note that the authorization at the schema level allows to unmask columns in all types of objects that support masks, namely: tables, SQL views and graphical calculation views.

  • At the object level:

    The authorization to view unmasked data is given for a specific object. You have to specify the object type and object runtime name (with namespace).

    Code snippet
    {
    "role":
      {
        "name":"db::UNMASK_EMPLOYEES_PAYMENT",
        "object_privileges":[{
          "type":"VIEW",
          "name":"db::CVD_EMPLOYEES_PAYMENT",
          "privileges":["UNMASKED"]}]
      }
    }
    Copy code
Note
Like for any other calculation view, the end user will receive an authorization error if he/she does not have a SELECT authorization on the view. In other words, the UNMASKED privilege does not by-pass the SELECT privilege which is mandatory to display a calculation view’s data.

Choosing a Mask Mode

From SAP HANA Cloud QRC 4/2021 onwards, two different mask modes are available.

  • Default

    Masking is done based on the user calling the calculation view with the masking definition.

  • Session User (new)

    Masking is done based on session user running the SQL query

This distinction does not affect the top-most calculation view of a stacked scenario because the applied privileges are the ones from the session user. But it is especially relevant for underlying views, where the Session User mask mode allows you to mask data from these views by applying the mask (or unmasking) based on the privileges of the Session User, that is, the user executing the query on the top-most view. By contrast, with the Default mask mode, the underlying views are executed with the privileges of the Object Owner, who always has unmasked privileges.

Caution
The mask mode setting is view-global. Besides, in a stacked scenario, upon execution of the top-most calculation view, the Mask Mode option defined is this view does NOT overwrite the ones defined in the underlying views.

Current Limitations to Column Masking

Save progress to your learning plan by logging in or creating an account