Masking Sensitive Data

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

The mask expression can be either a constant string (for example: XXX-XXX-XXX) or a 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:

1
LEFT(credit_card,4) || '-XXXX-XXXX-' || RIGHT(credit_card,4)

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

  Copy code

  Copy code

  Switch to dark mode

  Switch to dark mode

  12345678
  {
  "role":
    {
      "name":"db::UNMASK_ENTIRE_SCHEMA",
      "schema_privileges":[{
        "privileges":["UNMASKED"]}]
    }
  }

  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

  Copy code

  Copy code

  Switch to dark mode

  Switch to dark mode

  12345678910
  {
  "role":
    {
      "name":"db::UNMASK_EMPLOYEES_PAYMENT",
      "object_privileges":[{
        "type":"VIEW",
        "name":"db::CVD_EMPLOYEES_PAYMENT",
        "privileges":["UNMASKED"]}]
    }
  }

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 doesn't affect the top-most calculation view of a stacked scenario because the applied privileges are the ones from the session user. But it's 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.