SQL Server Iterating through a table and scrubbing sensitive data

Question

I have a table that contains the names of tables and fields that need to be scrubbed.

I need to loop through this table and scrub the field name in each table. I would also like any suggestions on a scrubbing algorithm.

Thank you!

Answer 1

Hi you should really update your request to say data obfuscating.

But that aside this is how you can do it. First create a function in whatever this database is (Don't alter this part load it in as is):

CREATE FUNCTION Character_Mask
(
    @OrigVal varchar(max),
    @InPlain int,
    @MaskChar char(1)
)
RETURNS varchar(max)
WITH ENCRYPTION
AS
BEGIN
    DECLARE @PlainVal varchar(max);
    DECLARE @MaskVal varchar(max);
    DECLARE @MaskLen int;

    SET @PlainVal = RIGHT(@OrigVal,@InPlain);
    SET @MaskLen = (DATALENGTH(@OrigVal) - @InPlain);
    SET @MaskVal = REPLICATE(@MaskChar, @MaskLen);
    Return @MaskVal + @PlainVal; 
END
GO

GRANT EXECUTE ON dbo.Character_Mask
    TO Sensitive_high, Sensitive_medium, Sensitive_low;
GO

Once you have that in your database, you can start using it in views:

CREATE VIEW dbo.MyObbedData
AS
SELECT
    dbo.Character_Scramble(c.FirstName) AS Ob_First_Name,
    dbo.Character_Scramble(c.LastName) AS Ob_LastName,
    dbo.Character_Scramble(c.Email) AS Ob_Email,
    dbo.Character_Scramble(u.UserName) AS Ob_UserName
FROM
    dbo.Customer c
    INNER JOIN dbo.User u
        ON c.UserID = u.UserID
GO

GRANT SELECT ON dbo.MyObbedData
    TO Sensitive_high, Sensitive_medium;
GO

The Character_Mask code was taken from here, it can do heaps more and if you want to read up on it here is the LINK

Caz