Mastering Dynamic Data Masking in Microsoft Fabric: A Comprehensive Guide
Explore the essentials of Dynamic Data Masking in Microsoft Fabric. Learn how DDM safeguards sensitive data, its implementation in Fabric Warehouses, and practical applications for enhanced data security and privacy.
In the realm of data security and privacy, Dynamic Data Masking (DDM) stands as a pivotal feature in Microsoft Fabric. This article delves into DDM, its significance, and its practical implementation in a Microsoft Fabric Warehouse.
Understanding Dynamic Data Masking
Dynamic Data Masking is a feature designed to mask parts of data within warehouse tables. Its primary purpose is to limit the exposure of sensitive data to individuals who do not require access to unredacted information. For instance, an email address like ‘ruicarvalho@xyz.com’ can be masked to display only the first letter and the domain, such as ‘r…@xyz.com’, for users without unmasking permissions.
Fabric Scenario
In Fabric, we are looking at a Warehouse table with information on the User, username, password, email, date of birth, etc…
We, as admins, want to mask some of this data for other users that are viewing this data.
Role-Based Data Access
A crucial aspect of DDM in Microsoft Fabric is role-based data access. In the scenario, I´ve set up two users, an admin user and a viewer. It’s important to note that admin, member, or contributor roles can view unmasked data, while the viewer role cannot.
Implementing Masking Rules
As the admin user, who has full access to the warehouse, we need to mask sensitive data in the ‘employee’ table from the viewer.
Types of Masks and Their Application
Default Masking Rule: This rule is versatile and can be applied to various field types including text (like VARCHAR fields), numeric (such as INT, BIGINT, or FLOAT), and date fields (DATE or DATETIME). The default masking alters the data based on the field type. For example, in the example table Users, the PasswordHash column (a VARCHAR field) was masked with 'X's, making it impossible to see the password of each individual in the dataset. Similarly, the birth date column was masked to show a uniform date of January 1st, 1900, instead of the actual birth dates.
--Default DDM
ALTER TABLE [DW_WWI].[dbo].[Users]
ALTER COLUMN PasswordHash ADD MASKED WITH (FUNCTION = 'default()')
ALTER TABLE [DW_WWI].[dbo].[Users]
ALTER COLUMN DateOfBirth ADD MASKED WITH (FUNCTION = 'default()')
Email Mask: Tailored specifically for email addresses, this mask transforms the email field such that only the first letter and the domain suffix (like .com) are visible.
ALTER TABLE [DW_WWI].[dbo].[Users]
ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()')
Random Mask: Ideal for numeric fields where confidentiality is key, like salaries or income. The random mask generates a number within a specified range. In this example, we apply this DDM function to the Revenue field, where the actual revenue figures were replaced with random numbers between a defined range (100000–200000), thus concealing the real income figures.
--Random DDM
ALTER TABLE [DW_WWI].[dbo].[Users]
ALTER COLUMN Revenue ADD MASKED WITH (FUNCTION = 'random(100000, 200000)')
Custom String Mask: This mask allows for more tailored masking, where specific parts of a string can be exposed while the rest is masked. In the Users table, we can apply this to the Contact column where we will keep visible the first 3 characters of the user's phone number and mask the rest with X´s.
--Custom DDM
ALTER TABLE [DW_WWI].[dbo].[Users]
ALTER COLUMN Contact ADD MASKED WITH (FUNCTION = 'partial(3,"XXX-XXXX",0)')
Check DDM Rules
There´s a table called sys.masked_columns that has all the information about the columns that have some DDM rule applied.
SELECT c.name, tbl.name as table_name, c.is_masked, c.masking_function
FROM sys.masked_columns AS c
JOIN sys.tables AS tbl
ON c.[object_id] = tbl.[object_id]
WHERE is_masked = 1;
Drop DDM Rules
If you want to remove any of the DDM rules you applied, it´s very simple:
--DROP MASK
ALTER TABLE [DW_WWI].[dbo].[Users]
ALTER COLUMN Email DROP MASKED;
Conclusion
Dynamic Data Masking in Microsoft Fabric is a powerful tool for data security and privacy. By understanding and implementing DDM, organizations can ensure that sensitive data is adequately protected while still being accessible for necessary business operations. This step-by-step provides a practical and insightful guide to DDM, making it a great resource for data engineers and security professionals.
What’s more? For just $5 a month, become a Medium Member and enjoy the liberty of limitless access to every masterpiece on Medium. By subscribing via my page, you not only contribute to my work but also play a crucial role in enhancing the quality of my work. Your support means the world! 😊
Upskill across data engineering, data analysis, machine learning, and generative AI. Join the thousands who have elevated their career with Databricks training & certification.
️ Complete at least one of the self-paced courses learning pathways below between April 9 - April 30 to earn a 50% discount on any Databricks Certification plus a 20% discount on a Databricks Academy Labs annual subscription!
🧑
🧑
