Data masking/ Data Scrambling/ Data Obfuscation – is the task for hiding actual data before sharing it with Users, UAT Testers, Vendors, Developers or anyone else who MUST NOT have access to production data or real time data. A DBA must proactively take care of this task if no one informed about the requirements. Sensitive personal or financial information must be masked. Talking to stake holders proactively is always good before you start data masking.
How to achieve this?
There are many ways of achieving. However, this is the simplest approach of masking data. Masking data requirements differ all the time. You need to be careful about your approach.
With this approach, in nutshell, you must create a function which replaces the actual string with same size strings or replaces some characters with others. You must create a procedure which accepts Table Name and Column Name with in arguments. Then it’s purely your choice how would you like to proceed. You may create Insert trigger, Update Trigger or SQL job or OS Job to call SP for any Event etc.
Steps to Mask:
Step 1 : Create the Function [dbo].[ ufnMaskRealData] on the targeted Database
———–Copy from here —————————–
/****** Object: UserDefinedFunction [dbo].[ufnMaskRealData] Script Date: 01/05/2015 12:10:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Author: Sachin Diwakar
— Create date: 24 Dec 204
— Description: Once called within select statement or SP – replaces the characters with the one listed in replace statment.
— =============================================
create function [dbo].[ufnMaskRealData]
(
@String varchar(255)
)
returns varchar(255)
as
BEGIN
select @String = replace(replace (replace (replace(replace( replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(@String,’o’,’e’),’a’,’o’),’i’,’a’),’u’,’i’),’t’,’p’),’c’,’k’),’d’,’t’),’e’,’x’),’o’,’r’),’l’,’s’), ‘1’,’3′),’2′,’4′),’3′,’5′),’4′,’6′), ‘0’,’9′), ‘9’,’1′),’h’,’p’), ‘k’,’n’)
return @String
END
—-go
———————-Copy end —————————————-
Step 2: Create the SP [dbo].[ uspDataMask] on the targeted Database
———–Copy from here —————————–
/****** Object: StoredProcedure [dbo].[uspDataMask] Script Date: 01/05/2015 12:13:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Author: Sachin Diwakar
— Create date: 23 Dec 204
— Description: This SP masks the data in column which is passed as an argument
— Example – Exec [dbo].[uspDataMask] TableName, ColumnToBeMasked
— =============================================
CREATE PROCEDURE [dbo].[uspDataMask]
@objectName as sysname, @colname as sysname
AS
BEGIN
Declare @sql as varchar(max)
set @sql = ‘update ‘ + @objectName + ‘ set ‘ + @colname + ‘= dbo.[ufnMaskRealData] (‘+ @colname +’)’
exec (@sql)
–PRINT @sql
END
GO
————–Copy ends here ————–
Step 3: Based on requirements, you can create below trigger on the table(s) and columns(s) that you would like to be masked.
———–Copy from here —————————–
/****** Object: Trigger [dbo].[trgDataMasktblAddress] Script Date: 01/05/2015 12:15:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Author: Sachin Diwakar
— Create date: 24 Dec 2014
— =============================================
CREATE TRIGGER [dbo].[trgDataMaskOnYourTable1]
ON [dbo].[ YourTable1]
AFTER INSERT
AS
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;
begin tran t1
exec [uspDataMask] ‘dbo.YourTable’, ‘YourCol8′
exec [uspDataMask] ‘dbo.YourTable1′, ‘YourCol7′
exec [uspDataMask] ‘dbo.YourTable2′, ‘YourCol6′
exec [uspDataMask] ‘dbo.YourTable3′, ‘YourCol5′
exec [uspDataMask] ‘dbo.YourTable4′, ‘YourCol4′
exec [uspDataMask] ‘dbo.YourTable5′, ‘YourCol3′
exec [uspDataMask] ‘dbo.YourCol16′, ‘YourCol2′
commit tran t1
END
————–Copy ends here ————–
Additionally you can create a job to mask the data as and when required. To do so, you just need to create a SQL server job and call the SP USPDataMask with the table and column name that you would like to mask.
exec [uspDataMask] ‘dbo.YourTable1, ‘YourCol1′
Hide Your Real Data - Data masking/ Data Scrambling/ Data Obfuscation
