Skip to content

Native Masking and Unmasking Types

This page describes how native masking and unmasking work for Microsoft SQL Server when using Privacera with native masking enabled. Unmasking scope depends on the SQL Server edition and deployment (see Unmasking overview).

To enable native column masking and row-level filtering in the connector (portal or YAML), see. Enable Native Support.

Masking overview

  • Applied to all users: Native masking is not user-specific. For a given column, the same mask is shown to every user who does not have unmasking permission.
  • Public group only: In the Privacera UI, when defining a masking policy you must enter public in the user/group/role input. If you enter any other user, group, or role, the policy will throw an error and masking will not work. Masking is effectively applied in one go for that column for all non-privileged users.

Unmasking overview

User-specific access

Unmasking permissions can be granted to specific users, groups, or roles, allowing them to view the original (unmasked) data.

Scope based on platform and version

  1. On-premises SQL Server 2019 and earlier

    Unmasking is supported only at the database level. In the UI, enter the database name, set the schema to None, and specify the user, group, or role to grant unmasking access. Unmasking cannot be applied at the schema, table, or column level.

  2. Azure SQL Database and SQL Server 2022 and later

    Unmasking is supported at the column level. In the UI, you must provide the database name, schema name, table name, column name, and the user, group, or role to receive unmasking access.

Ranger service and connector behavior

Unmasking behavior depends on the Ranger service definition used by the connector:

  • Use mssql service definition for:

    • Azure SQL Database
    • SQL Server 2022 and later

    → Supports column-level unmasking.

  • Use privacera_mssql_19 service definition for:

    • SQL Server 2019 and earlier

    → Supports database-level unmasking.

Configuration

By default, the connector is configured to use the mssql service definition.

To configure it for SQL Server 2019, update the following property in vars.connector.mssql.yml:

YAML
1
CONNECTOR_MSSQL_RANGER_SERVICE_NAME: "privacera_mssql_19"

For detailed configuration steps, see Access management on AD user and group.

Masking types

Credit Card

The last four digits of the value remain visible; the rest are replaced with X in a credit-card-style format.

Example: 4532-1234-5678-9012 → XXXX-XXXX-XXXX-9012.

Supported only for string data types (e.g. varchar, nvarchar, char, nchar, text, ntext).

Custom

Custom masking keeps a number of leading characters (prefix) and trailing characters (suffix) visible, and replaces the middle with a mask value you specify.

In the UI you configure three inputs:

  • Prefix (number of leading characters that stay visible, e.g. 1, 2, 3, 4)

  • Suffix (number of trailing characters that stay visible, e.g. 1, 2, 3, 4)

  • Masked value or expression (the string that replaces the middle portion, e.g. xxxxxx).

Example: Prefix = 2, Suffix = 3, Mask value = xxxxxx Original value: privacera → Masked: prxxxxxxera

Supported only for string data types (e.g. varchar, nvarchar, char, nchar, text, ntext).

Default

Default applies a full mask based on the column’s datatype. Data in storage is unchanged; only the value returned to the user is obscured.

Datatype category Types Masked value
String char, nchar, varchar, nvarchar, text, ntext 'XXXX' (or fewer X for fields shorter than 4 characters)
Numeric bigint, int, smallint, tinyint, bit, decimal, numeric, money, smallmoney, float, real 0
Binary binary, varbinary, image A single byte with ASCII value 0

Email

Only the first character of the username and the domain part (e.g. @privacera.com) are shown; the rest of the username is obfuscated.

Example: access_control@privacera.com → aXXXX@privacera.com.

Supported only for string data types (e.g. varchar, nvarchar, char, nchar, text, ntext).

Nullify

The column value is returned as NULL to the user.

Example: privacera → NULL

Supported only for string data types (e.g. varchar, nvarchar, char, nchar, text, ntext). Not supported for numeric types.

Random Number

The original numeric value is replaced by a random number within a range you define. In the UI you set Min value and Max value (e.g. min 100, max 200). Returned values can be decimals within that range (e.g. 100.0, 101.2, 199.8).

Supported only for numeric data types (e.g. int, bigint, smallint, tinyint, decimal, numeric, float, real, money, smallmoney, bit).

Summary

Aspect Masking Unmasking
Scope Column-level (which column is masked). On-prem SQL Server 2019 and lower: database only (schema = None). Azure SQL / on-prem SQL Server 2022+: column-level (schema, table, column).
Who sees masked data? All users except those with unmasking. N/A (masking applies to everyone by default).
Who sees real data? Only users/groups/roles granted unmasking. Users/groups/roles you configure in the unmasking policy.
UI: user/group/role Must be public only. Any valid user, group, or role name.
Service definition Uses connector’s Ranger service (default mssql). Use privacera_mssql_19 via CONNECTOR_MSSQL_RANGER_SERVICE_NAME for on-prem 2019 and lower.