Data masking is a data security technique in which a dataset is copied but with sensitive data obfuscated. This benign replica is then used instead of the authentic data for testing or training purposes.
Static Data Masking
Every organization has some confidential data and sensitive information stored in production databases. Since there is always an incessant need to migrate this live data to lower environments for several developments and testing purposes, it becomes important to ensure suitable protection has been provided to this critical data while copying production databases to non-production environments.
In order to reproduce production issues on environments like Dev, Staging, Test, UAT, etc., data professionals tend to create test data by simply copying production data to these lower life cycle environments. The development team typically has unrestricted access to all the sensitive information with no encryption or masking on the production database restore on these environments. This easily accessible data put confidential data of the organization at risk.
SQL Server 2019 with SSMS 18.0 (preview 5 and higher) introduces a new security feature called Static Data Masking. Previously it was available for the Azure SQL DB only.
Applying Static Data Masking against a production database and then creating a backup of the database with the mask applied, followed by restoring this masked copy to non-production environments. It is basically a feature that helps users create a masked copy of a SQL database. Once data is statically masked, it is permanently replaced in the cloned database and we can’t change it. This feature is used for several purposes like sharing sensitive data, database development, database troubleshooting, analytics and business reporting.
Read the full article which explains all the steps in details for Static Data Masking: https://www.mssqltips.com/sqlservertip/5939/sql-server-static-data-masking-example/
Dynamic Data Masking
Application developers often required to access production data for troubleshooting purposes and preventing them from accessing sensitive data without affecting their troubleshooting process is vital. We can use Dynamic Data Masking for sensitive fields and hide those details from such users by keeping original data intake. We can allow different users with different roles to see masked fields differently. Amazing isn’t it.
Dynamic Data Masking is a security feature introduced in SQL Server 2016 that limits the access of unauthorized users to sensitive data at the database layer.
Another example is the call center employee who will access the customer’s information to help him in his request, but the critical financial data, such as the bank account number or the credit card full number, will be masked to that person.
Read the full article which explains all the steps in details for Dynamic Data Masking: https://www.sqlshack.com/dynamic-data-masking-in-sql-server/
Static Data Masking vs. Dynamic Data Masking
| Static Data Masking | Dynamic Data Masking |
| Happens on a copy of the database | Original data not retrievable |
| Mask occurs at the storage level | All users have access to the same masked data |
| Happens on the original database | Original data intact |
| Mask occurs on-the-fly at query time | Mask varies based on user permission |
That is All. I hope this will help !!!
Ramani Sandeep 