Skip to main content This browser is no longer supported. Show
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support. Dynamic Data Masking
In this articleApplies to: SQL Server 2016 (13.x) and later Azure SQL Database Azure SQL Managed Instance Azure Synapse AnalyticsDynamic data masking (DDM) limits sensitive data exposure by masking it to non-privileged users. It can be used to greatly simplify the design and coding of security in your application. Dynamic data masking helps prevent unauthorized access to sensitive data by enabling customers to specify how much sensitive data to reveal with minimal impact on the application layer. DDM can be configured on designated database fields to hide sensitive data in the result sets of queries. With DDM, the data in the database isn't changed. DDM is easy to use with existing applications, since masking rules are applied in the query results. Many applications can mask sensitive data without modifying existing queries.
The purpose of dynamic data masking is to limit exposure of sensitive data, preventing users who shouldn't have access to the data from viewing it. Dynamic data masking doesn't aim to prevent database users from connecting directly to the database and running exhaustive queries that expose pieces of the sensitive data. Dynamic data masking is complementary to other SQL Server security features (auditing, encryption, row level security, etc.) and it's highly recommended to use it with them in order to better protect the sensitive data in the database. Dynamic data masking is available in SQL Server 2016 (13.x) and Azure SQL Database, and is configured by using Transact-SQL commands. For more information about configuring dynamic data masking by using the Azure portal, see Get started with SQL Database Dynamic Data Masking (Azure portal). Defining a Dynamic Data MaskA masking rule may be defined on a column in a table, in order to obfuscate the data in that column. Four types of masks are available.
PermissionsYou don't need any special permission to create a table with a dynamic data mask, only the standard CREATE TABLE and ALTER on schema permissions. Adding, replacing, or removing the mask of a column, requires the ALTER ANY MASK permission and ALTER permission on the table. It's appropriate to grant ALTER ANY MASK to a security officer. Users with SELECT permission on a table can view the table data. Columns that are defined as masked, will display the masked data. Grant the UNMASK permission to a user to enable them to retrieve unmasked data from the columns for which masking is defined. The CONTROL permission on the database includes both the ALTER ANY MASK and UNMASK permission. Note The UNMASK permission does not influence metadata visibility: granting UNMASK alone will not disclose any Metadata. UNMASK will always need to be accompanied by a SELECT permission to have any effect. Example: granting UNMASK on database scope and granting SELECT on an individual Table will have the result that the user can only see the metadata of the individual table from which he can select, not any others. Also see Metadata Visibility Configuration. Best practices and common use cases
Querying for masked columnsUse the sys.masked_columns view to query for table-columns that have a masking function applied to them. This view inherits from the sys.columns view. It returns all columns in the sys.columns view, plus the is_masked and masking_function columns, indicating if the column is masked, and if so, what masking function is defined. This view only shows the columns on which there's a masking function applied.
Limitations and restrictionsA masking rule can't be defined for the following column types:
For users without the UNMASK permission, the deprecated READTEXT, UPDATETEXT, and WRITETEXT statements don't function properly on a column configured for Dynamic Data Masking. Adding a dynamic data mask is implemented as a schema change on the underlying table, and therefore can't be performed on a column with dependencies. To work around this restriction, you can first remove the dependency, then add the dynamic data mask and then re-create the dependency. For example, if the dependency is due to an index dependent on that column, you can drop the index, then add the mask, and then re-create the dependent index. Whenever you project an expression referencing a column for which a data masking function is defined, the expression will also be masked. Regardless of the function (default, email, random, custom string) used to mask the referenced column, the resulting expression will always be masked with the default function. Cross database queries spanning two different Azure SQL databases or databases hosted on different SQL Server Instances, and involve any kind of comparison or join operation on MASKED columns won't provide correct results. The results returned from the remote server are already in MASKED form and not suitable for any kind of comparison or join operation locally. Security Note: Bypassing masking using inference or brute-force techniquesDynamic Data Masking is designed to simplify application development by limiting data exposure in a set of pre-defined queries used by the application. While Dynamic Data Masking can also be useful to prevent accidental exposure of sensitive data when accessing a production database directly, it's important to note that unprivileged users with ad-hoc query permissions can apply techniques to gain access to the actual data. If there's a need to grant such ad-hoc access, Auditing should be used to monitor all database activity and mitigate this scenario. As an example, consider a database principal that has sufficient privileges to run ad-hoc queries on the database, and tries to 'guess' the underlying data and ultimately infer the actual values. Assume that we have a mask defined on the
This demonstrates that Dynamic Data Masking shouldn't be used as an isolated measure to fully secure sensitive data from users running ad-hoc queries on the database. It's appropriate for preventing accidental sensitive data exposure, but won't protect against malicious intent to infer the underlying data. It's important to properly manage the permissions on the database, and to always follow the minimal required permissions principle. Also, remember to have Auditing enabled to track all activities taking place on the database. Granular permissions introduced in SQL Server 2022Starting with SQL Server 2022 (16.x), you can prevent unauthorized access to sensitive data and gain control by masking it to an unauthorized user at different levels of the database. You can grant or revoke UNMASK permission at the database-level, schema-level, table-level or at the column-level to a user or database role. This enhancement provides a more granular way to control and limit unauthorized access to data stored in the database and improve data security management. ExamplesCreating a Dynamic Data MaskThe following example creates a table with three different types of dynamic data masks. The example populates the table, and selects to show the result.
A new user is created and granted the
SELECT permission on the schema where the table resides. Queries executed as the
The result demonstrates the masks by changing the data from
into
where the number in DiscountCode is random for every query result. Adding or editing a mask on an existing columnUse the ALTER TABLE statement to add a mask to an existing
column in the table, or to edit the mask on that column.
The following example changes a masking function on the
Granting permissions to view unmasked dataGranting the UNMASK permission allows
Dropping a Dynamic Data MaskThe following statement drops the mask on the
Granular permission examples
See alsoCREATE TABLE (Transact-SQL) FeedbackSubmit and view feedback for Additional resourcesAdditional resourcesIn this articleWhich of the following maintenance adds enhancement to an operational system to make the system easier to use?Adaptive maintenance adds enhancements to an operational system, so it will make easier to use the system. It's different with corrective maintenance, because adaptive maintenance is less urgent since business and technical changes may occur over some period of time.
Is the main line of defense between a local network or intranet and the Internet?A firewall is a system designed to prevent unauthorized access to or from a private network. You can implement a firewall in either hardware or software form, or in a combination of both. Firewalls prevent unauthorized internet users from accessing private networks connected to the internet, especially intranets.
Which of the following is a general category of system requirements?System requirements fall into five general categories: outputs, inputs, processes, performance, and controls.
|