Verschleiern / Maskieren / Verschlüsseln von persönlichen Informationen

Question

Ich bin auf der Suche nach einer selbstentwickelten Möglichkeit, Produktionsdaten für die Verwendung in Entwicklung und Test zu verschlüsseln. Ich habe ein paar Skripte erstellt, die zufällige Sozialversicherungsnummern erzeugen, Geburtsdaten verschieben, E-Mails verschlüsseln usw. Aber beim Versuch, Kundennamen zu verschlüsseln, bin ich gegen eine Wand gestoßen. Ich möchte die echten Namen beibehalten, damit wir sie weiterhin verwenden oder suchen können, also scheidet die Erzeugung zufälliger Buchstaben aus. Bisher habe ich versucht, eine temporäre Tabelle mit allen Nachnamen in der Tabelle zu erstellen und dann die Kundentabelle mit einer zufälligen Auswahl aus der temporären Tabelle zu aktualisieren. Etwa so:

DECLARE @Names TABLE (Id int IDENTITY(1,1),[Name] varchar(100))

/* Scramble the last names (randomly pick another last name) */
INSERT @Names SELECT LastName FROM Customer ORDER BY NEWID();
WITH [Customer ORDERED BY ROWID] AS
(SELECT ROW_NUMBER() OVER (ORDER BY NEWID()) AS ROWID, LastName FROM Customer)
UPDATE [Customer ORDERED BY ROWID] SET LastName=(SELECT [Name] FROM @Names WHERE ROWID=Id)

Dies hat im Test gut funktioniert, ist aber bei größeren Datenmengen (>20 Minuten für 40K Zeilen) völlig unbrauchbar.

Wie würden Sie Kundennamen verschlüsseln, ohne die echten Namen und die Bedeutung der Produktionsdaten zu verändern?

UPDATE : Es passiert immer wieder, dass man versucht, alle Informationen in den Beitrag zu packen, und dabei etwas Wichtiges vergisst. Diese Daten werden auch in unseren öffentlich zugänglichen Verkaufs- und Demoumgebungen verwendet. Einige der Antworten entsprechen dem, was ich zu tun versuche, nämlich die Namen zu "tauschen", aber meine Frage ist wörtlich, wie man in T-SQL codiert?

Answer 1

Verwenden Sie stattdessen eine temporäre Tabelle, und die Abfrage ist sehr schnell. Ich habe gerade 60K Zeilen in 4 Sekunden abgefragt. Ich werde diese Abfrage in Zukunft verwenden.

DECLARE TABLE #Names 
(Id int IDENTITY(1,1),[Name] varchar(100))

/* Die Nachnamen verschlüsseln (zufällig einen anderen Nachnamen wählen) */

INSERT #Names
  SELECT LastName 
  FROM Customer 
  ORDER BY NEWID();
WITH [Customer ORDERED BY ROWID] AS
(SELECT ROW_NUMBER() OVER (ORDER BY NEWID()) AS ROWID, LastName FROM Customer)

UPDATE [Customer ORDERED BY ROWID] 

SET LastName=(SELECT [Name] FROM #Names WHERE ROWID=Id)

DROP TABLE #Names

Answer 2

Der folgende Ansatz funktionierte für uns, lassen Sie uns sagen, wir haben 2 Tabellen Kunden und Produkte:

CREATE FUNCTION [dbo].[GenerateDummyValues]
(
    @dataType varchar(100),
    @currentValue varchar(4000)=NULL
)
RETURNS varchar(4000)
AS
BEGIN
IF @dataType = 'int'
    BEGIN
        Return '0'
    END
ELSE IF @dataType = 'varchar' OR @dataType = 'nvarchar' OR @dataType = 'char' OR @dataType = 'nchar'
    BEGIN
        Return 'AAAA'
    END
ELSE IF @dataType = 'datetime'
    BEGIN
        Return Convert(varchar(2000),GetDate())
    END
-- you can add more checks, add complicated logic etc
Return 'XXX'
END

Die obige Funktion hilft bei der Generierung verschiedener Daten auf der Grundlage des eingehenden Datentyps.

Verwenden Sie nun für jede Spalte jeder Tabelle, in der das Wort "id" nicht vorkommt, die folgende Abfrage, um weitere Abfragen zur Bearbeitung der Daten zu erstellen:

select 'select ''update '' + TABLE_NAME + '' set '' + COLUMN_NAME + '' = '' +  '''''''' + dbo.GenerateDummyValues( Data_type,'''') + '''''' where id = '' + Convert(varchar(10),Id) from INFORMATION_SCHEMA.COLUMNS, ' + table_name + ' where RIGHT(LOWER(COLUMN_NAME),2) <> ''id'' and TABLE_NAME = '''+ table_name + '''' + ';' from  INFORMATION_SCHEMA.TABLES;

Wenn Sie die obige Abfrage ausführen, werden Aktualisierungsabfragen für jede Tabelle und jede Spalte dieser Tabelle generiert, zum Beispiel:

select 'update ' + TABLE_NAME + ' set ' + COLUMN_NAME + ' = ' +  '''' + dbo.GenerateDummyValues( Data_type,'') + ''' where id = ' + Convert(varchar(10),Id) from INFORMATION_SCHEMA.COLUMNS, Customers where RIGHT(LOWER(COLUMN_NAME),2) <> 'id' and TABLE_NAME = 'Customers';
select 'update ' + TABLE_NAME + ' set ' + COLUMN_NAME + ' = ' +  '''' + dbo.GenerateDummyValues( Data_type,'') + ''' where id = ' + Convert(varchar(10),Id) from INFORMATION_SCHEMA.COLUMNS, Products where RIGHT(LOWER(COLUMN_NAME),2) <> 'id' and TABLE_NAME = 'Products';

Wenn Sie nun die oben genannten Abfragen ausführen, erhalten Sie endgültige Aktualisierungsabfragen, die die Daten Ihrer Tabellen aktualisieren werden.

Sie können dies auf jeder SQL-Server-Datenbank ausführen, egal wie viele Tabellen Sie haben, es wird Abfragen für Sie generieren, die weiter ausgeführt werden können.

Ich hoffe, das hilft.

Answer 3

Eine weitere Seite zur Generierung von gefälschten Datensätzen, mit einer Option für T-SQL-Ausgabe: https://mockaroo.com/

Answer 4

Hier ist eine Möglichkeit, ROT47 zu verwenden, die umkehrbar ist, und eine andere, die zufällig ist. Sie können zu beiden eine PK hinzufügen, um zu den "unverschlüsselten" Versionen zurückzukehren

declare @table table (ID int, PLAIN_TEXT nvarchar(4000))
insert into @table
values
(1,N'Some Dudes name'),
(2,N'Another Person Name'),
(3,N'Yet Another Name')

--split your string into a column, and compute the decimal value (N) 
if object_id('tempdb..#staging') is not null drop table #staging
select 
    substring(a.b, v.number+1, 1) as Val
    ,ascii(substring(a.b, v.number+1, 1)) as N
    --,dense_rank() over (order by b) as RN
    ,a.ID
into #staging
from (select PLAIN_TEXT b, ID FROM @table) a
    inner join
         master..spt_values v on v.number < len(a.b)
where v.type = 'P' 

--select * from #staging

--create a fast tally table of numbers to be used to build the ROT-47 table.

;WITH
    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
    cteTally(N) AS 
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
    )

--Here we put it all together with stuff and FOR XML
select 
    PLAIN_TEXT
    ,ENCRYPTED_TEXT =
        stuff((
        select
            --s.Val
            --,s.N
            e.ENCRYPTED_TEXT
        from #staging s
        left join(
        select 
            N as DECIMAL_VALUE
            ,char(N) as ASCII_VALUE
            ,case 
                when 47 + N <= 126 then char(47 + N)
                when 47 + N > 126 then char(N-47)
            end as ENCRYPTED_TEXT
        from cteTally
        where N between 33 and 126) e on e.DECIMAL_VALUE = s.N
        where s.ID = t.ID
        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 0, '')
from @table t

--or if you want really random
select 
    PLAIN_TEXT
    ,ENCRYPTED_TEXT =
        stuff((
        select
            --s.Val
            --,s.N
            e.ENCRYPTED_TEXT
         from #staging s
        left join(
        select 
            N as DECIMAL_VALUE
            ,char(N) as ASCII_VALUE
            ,char((select ROUND(((122 - N -1) * RAND() + N), 0))) as ENCRYPTED_TEXT
        from cteTally
        where (N between 65 and 122) and N not in (91,92,93,94,95,96)) e on e.DECIMAL_VALUE = s.N
        where s.ID = t.ID
        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 0, '')
from @table t

Answer 5

Ich bin selbst auf das gleiche Problem gestoßen und habe eine alternative Lösung gefunden, die vielleicht auch für andere funktioniert.

Die Idee ist, MD5 auf den Namen anzuwenden und dann die letzten 3 Hexadezimalziffern zu nehmen, um sie in eine Namenstabelle zu übertragen. Sie können dies getrennt für Vor- und Nachname tun.

3 Hexadezimalziffern stehen für Dezimalzahlen von 0 bis 4095. Wir brauchen also eine Liste mit 4096 Vornamen und 4096 Nachnamen.

Also conv(substr(md5(first_name), 3),16,10) (in MySQL Syntax) wäre ein Index von 0 bis 4095, der mit einer Tabelle verbunden werden könnte, die 4096 Vornamen enthält. Das gleiche Konzept könnte auf Nachnamen angewendet werden.

Die Verwendung von MD5 (im Gegensatz zu einer Zufallszahl) garantiert, dass ein Name in den Originaldaten immer demselben Namen in den Testdaten zugeordnet wird.

Eine Liste der Namen finden Sie hier:

https://gist.github.com/elifiner/cc90fdd387449158829515782936a9a4