| title | description | author | ms.author | ms.reviewer | ms.date | ms.service | ms.topic | dev_langs | monikerRange | |
|---|---|---|---|---|---|---|---|---|---|---|
sp_pdw_log_user_data_masking (Azure Synapse Analytics) |
sp_pdw_log_user_data_masking configures user data masking in Azure Synapse Analytics activity logs. |
WilliamDAssafMSFT |
wiassaf |
randolphwest |
08/21/2024 |
sql |
reference |
|
>=aps-pdw-2016 || =azure-sqldw-latest |
[!INCLUDE applies-to-version/asa-pdw]
Use sp_pdw_log_user_data_masking to enable user data masking in [!INCLUDE ssazuresynapse-md] activity logs. User data masking affects the statements on all databases on the appliance.
Important
The [!INCLUDE ssazuresynapse-md] activity logs affected by sp_pdw_log_user_data_masking are certain [!INCLUDE ssazuresynapse-md] activity logs. sp_pdw_log_user_data_masking doesn't affect database transaction logs, or [!INCLUDE ssNoVersion] error logs.
Syntax for Azure Synapse Analytics and Analytics Platform System (PDW).
sp_pdw_log_user_data_masking [ [ @masking_mode = ] value ]
[ ; ]
Note
[!INCLUDE synapse-analytics-od-unsupported-syntax]
In the default configuration, [!INCLUDE ssazuresynapse-md] activity logs contain full [!INCLUDE tsql] statements, and can in some cases include user data contained in operations such as INSERT, UPDATE, and SELECT statements. If there's a problem on the appliance, this permits the analysis of the conditions that caused the problem without a need to reproduce the issue. In order to prevent the user data from being written to [!INCLUDE ssazuresynapse-md] activity logs, customers can choose to turn on the user data masking by using this stored procedure. The statements are still written to [!INCLUDE ssazuresynapse-md] activity logs, but all the literals in statements that might contain user data are masked; replaced with some predefined constant values.
When transparent data encryption is enabled on the appliance, masking of the user data in [!INCLUDE ssazuresynapse-md] activity logs is automatically turned on.
Determines whether transparent data encryption log user data masking is enabled. masking_mode is int, and can be one of the following values:
| Value | Description |
|---|---|
0 |
Disabled, user data appears in the [!INCLUDE ssazuresynapse-md] activity logs. |
1 |
Enabled, user data statements appear in the [!INCLUDE ssazuresynapse-md] activity logs but the user data is masked. |
2 |
Statements containing user data aren't written to the [!INCLUDE ssazuresynapse-md] activity logs. |
Executing sp_pdw_log_user_data_masking without parameters returns the current state of transparent data encryption (TDE) log user data masking on the appliance as a scalar result set.
User data masking in [!INCLUDE ssazuresynapse-md] activity logs enables replacement of literals with predefined constant values in SELECT and Data Manipulation Language (DML) statements, as they can contain user data. Setting masking_mode to 1 doesn't mask metadata, such as column names or table names. Setting masking_mode to 2 removes statements with metadata, such as column names or table names.
User data masking in [!INCLUDE ssazuresynapse-md] activity logs is implemented in the following way:
-
TDE and user data masking in [!INCLUDE ssazuresynapse-md] activity logs are turned off by default. The statements aren't automatically masked if database encryption isn't enabled on the appliance.
-
Enabling TDE on the appliance automatically turns on the user data masking in [!INCLUDE ssazuresynapse-md] activity logs.
-
Disabling TDE doesn't affect user data masking in [!INCLUDE ssazuresynapse-md] activity logs.
-
You can explicitly enable user data masking in [!INCLUDE ssazuresynapse-md] activity logs by using the
sp_pdw_log_user_data_maskingprocedure.
Requires membership in the sysadmin fixed database role, or CONTROL SERVER permission.
The following example enables TDE log user data masking on the appliance.
EXEC sp_pdw_log_user_data_masking 1;