Lookup Table driven Dynamic Data Masking

Dynamic Data Masking (DDM) is a security feature used in relational database management systems (RDBMS) to control the visibility of sensitive data to different users. It helps organizations protect sensitive information from unauthorized access by dynamically replacing sensitive data with masked (obfuscated) values, thus preventing unauthorized users from viewing the actual sensitive information

The primary purpose of dynamic data masking is to provide an additional layer of security by limiting the exposure of sensitive data to users who do not have the necessary privileges to see the full data. This is particularly useful in scenarios where multiple users or roles access the same database but have different levels of authorization.

 

Steps to Dynamically Mask Data Using Lookup Table and Table Trigger

  1. Create a Table to store the Columns That need to be masked
  1. Using this table we can store the name of the table, column, and the masking function we want to perform.
  2. ismasked column will be updated for the row in the table when the column is successfully masked
  3. Create a Table Trigger which would be triggered after an insert operation.
  1. We have created a temporary table with identity column id that stores the data which is inserted
  2. We then take the insert count and store it in @maxi variable and set @i variable initially equal to 1
  3. We then iterate through the temporary table using the identity column and set variables like 

@schemaName, @TableName, @ColumnName, @maskfunction for that iteration

  1. Later, we create a Dynamic SQL statement to apply the masking function for the particular column 
  2. Then we update the ismasked flag to TRUE once that column is masked in ‘PIIDataMaskingMetadata’
  3. We also have implemented TRY CATCH Mechanism for error handling in case masking fails.
  4. When we insert data in PIIDataMaskingMetadata’ table table level trigger which created gets triggered and we get the following output
  5. We can test masking by creating a user with no masking privilege to check if data is masked or not

  1. Select statement output when we execute it as ‘MaskingTestUser’ user

Addend Analytics is a Microsoft Gold Partner based in Mumbai, India, and a branch office in the U.S.

Addend has successfully implemented 100+ Microsoft Power BI and Business Central projects for 100+ clients across sectors like Financial Services, Banking, Insurance, Retail, Sales, Manufacturing, Real estate, Logistics, and Healthcare in countries like the US, Europe, Switzerland, and Australia.

Get a free consultation now by emailing us or contacting us.

Email Us Or Contact Us