Obfuscating character columns

Question

Obfuscating character columns

VoldemarG

Hall of Fame

Points: 3883
More actions
November 21, 2018 at 8:36 am

#373082

Is anyone aware of any resources/websites/T-SQL code that will allow me to obfuscate ALL columns in ALL tables within entire database
obfuscate all columns that are named 'AgentLastName' ?
Likes to play Chess

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

VoldemarG Hall of Fame Points: 3883 More actions · Answer 1

Currently i am using the below T-SQL, one column of one table at a time. But need to obfuscate 200 tables, same column name in all 200. Same datatype.

declare @AlLChars varchar(100) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'
update AgentMaster set MY_COLUMN = (
SELECT RIGHT( LEFT(@AlLChars,ABS(BINARY_CHECKSUM(NEWID())%35) + 1 ),1) + RIGHT( LEFT(@AlLChars,ABS(BINARY_CHECKSUM(NEWID())%35) + 1 ),1) +
RIGHT( LEFT(@AlLChars,ABS(BINARY_CHECKSUM(NEWID())%35) + 1 ),1) + RIGHT( LEFT(@AlLChars,ABS(BINARY_CHECKSUM(NEWID())%35) + 1 ),1) + RIGHT( LEFT(@AlLChars,ABS(BINARY_CHECKSUM(NEWID())%35) + 1 ),1)
)

select top 500 MY_COLUMN, * from AgentMaster order by MY_COLUMN --My column is Varchar(50)

Likes to play Chess

Thom A SSC Guru Points: 99133 More actions · Answer 2

If the columns all have the same name, and definition, why not just use some dynamic SQL?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

pietlinden SSC Guru Points: 63509 More actions · Answer 3

You mean by querying the sys.all_columns table and using dynamic SQL to automate it?

Thom A SSC Guru Points: 99133 More actions · Answer 4

Otherwise, RedGate offer a tool called Data Masker.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

VoldemarG Hall of Fame Points: 3883 More actions · Answer 5

But that requires 2 loops , tables and columns, a lot of T-SQL writing and testing... 🙁

Likes to play Chess

Thom A SSC Guru Points: 99133 More actions · Answer 6

VoldemarG - Wednesday, November 21, 2018 8:47 AM
But that requires 2 loops , tables and columns, a lot of T-SQL writing and testing... 🙁

Loops? it would require none. And lots of T-SQL? Hardly. Your statement above isn't that long, so i would expect the final version to probably be about double that in size.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

ScottPletcher SSC Guru Points: 101122 More actions · Answer 7

If you're on SQL 2016, you should use SQL's native data masking capability. It will easily and efficiently do exactly what you want.

If that won't work, you could do the old-school approach of a view that doesn't include that column. Or, another way to try, you could use DENY at the column level.

The old-school way might involve, for example, renaming the existing table, then making a view of the same name as the original table that contains every column except AgentLastName. Anyone coming in from that view would never have access to the column in any form since it doesn't exist.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

pietlinden SSC Guru Points: 63509 More actions · Answer 8

(Wait, so you can update directly?)

Found this: https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/encrypt-a-column-of-data?view=sql-server-2017

Wouldn't you have to run this on each column?
UPDATE HumanResources.Employee SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'), NationalIDNumber);

So create a query to return all tables containing said column, then use dynamic SQL create the statement, then execute one at a time in the loop?

Jonathan AC Roberts SSCoach Points: 18362 More actions · Answer 9

Output the results to text, and generate a script:
declare @sSqlTemplate as nvarchar(MAX)= 'declare @AlLChars varchar(100) = ''ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'' update AgentMaster set MY_COLUMN = ( SELECT RIGHT( LEFT(@AlLChars,ABS(BINARY_CHECKSUM(NEWID())%35) + 1 ),1) + RIGHT( LEFT(@AlLChars,ABS(BINARY_CHECKSUM(NEWID())%35) + 1 ),1) + RIGHT( LEFT(@AlLChars,ABS(BINARY_CHECKSUM(NEWID())%35) + 1 ),1) + RIGHT( LEFT(@AlLChars,ABS(BINARY_CHECKSUM(NEWID())%35) + 1 ),1) + RIGHT( LEFT(@AlLChars,ABS(BINARY_CHECKSUM(NEWID())%35) + 1 ),1)); GO ' SELECT REPLACE(@sSqlTemplate, 'AgentMaster', QUOTENAME(C.TABLE_SCHEMA) + '.' + QUOTENAME(C.TABLE_NAME)) FROM INFORMATION_SCHEMA.COLUMNS C WHERE C.COLUMN_NAME = 'MY_COLUMN'

You might need to change the query options:

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 10

Hmmm... if an individual AgentName appears in all 200 databases in multiple tables and multiple times in those tables, wouldn't you want the obfuscation to be exactly the same for that particular AgentName to be exactly the same everywhere it was used so you could test aggregations and joins, etc, etc?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

VoldemarG Hall of Fame Points: 3883 More actions · Answer 11

VoldemarG

Hall of Fame

Points: 3883

November 21, 2018 at 5:16 pm

#2014307

hmm...

Likes to play Chess