Generowanie losowych odpowiedników dla danych wrażliwych dla RODO . Część 1

Wyobraźmy sobie sytuację, że mamy w bazie danych mnóstwo wrażliwych informacji, numery PESEL, adresy e-mail, numery telefonów, numery dokumentów tożsamości, konta bankowe.

Te dane należy tak anonimizować, by  uzyskany rezultat nie dawał  możliwości  odtworzenia oryginalnej wartości i jednocześnie spełniał, jeżeli trzeba reguły poprawności danego wzorca. Dotyczy to m.in. długości, formatu i sumy kontrolnej.

Zacznijmy na przykładzie numeru dowodu osobistego w formacie ABC123456.  Czy jest on poprawny ?

Dla tych, którzy przyzwyczaili się do sumy kontrolnej na ostatnim miejscu wzorca dziwne może być to, że suma kontrolna to pierwsza z cyfr (czwarty element).

declare @Numer varchar(20)
declare @numerPoprawny varchar(20)
set @Numer='ABC123456'


select @numerPoprawny= (
left(@Numer,3)
			+
			convert(varchar(1),((7*ascii(substring(@Numer,1,1))-55)+
			(3*ascii(substring(@Numer,2,1))-55)+
			(1*ascii(substring(@Numer,3,1))-55)+
			-- tu jest suma kontrolna 
			7*convert(int,(substring(@Numer,5,1)))+
			3*convert(int,(substring(@Numer,6,1)))+
			1*convert(int,(substring(@Numer,7,1)))+
			7*convert(int,(substring(@Numer,8,1)))+
			3*convert(int,(substring(@Numer,9,1))) ) % 10)
			-- tu jest suma kontrolna
			+
			right(@Numer,5)
			) 

select @Numer as [@Numer],@numerPoprawny as [@numerPoprawny]

Powyższe zapytanie zwróci jako poprawny numer dowodu wartość ABC523456, czyli 5 jest poprawną cyfrą kontrolną, a nie 1.

1. Na początku wygenerujmy  1 mln sztucznych dowodów osobistych.

use AdventureWorks2012

IF OBJECT_ID('AdventureWorks2012.dbo.DokumentyOsobiste') is null
BEGIN
create table dbo.DokumentyOsobiste
(
lp int not null primary key,
numerDokumentu varchar(50),
NumerDokumentuPoprawny varchar(50)
)
END

ELSE
BEGIN
   truncate TABLE dbo.DokumentyOsobiste
END  


 ;WITH
  Poziom0 as (select 1 as C union all select 1), --2 rows
  Poziom1 as (select 1 as C from Poziom0 as A, Poziom0 as B),--4 rows
  Poziom2 as (select 1 as C from Poziom1 as A, Poziom1 as B),--16 rows
  Poziom3 as (select 1 as C from Poziom2 as A, Poziom2 as B),--256 rows
  Poziom4 as (select 1 as C from Poziom3 as A, Poziom3 as B),--65536 rows
  Poziom5 as (select 1 as C from Poziom4 as A, Poziom4 as B),--4294967296 rows
  Dokumenty  as (select row_number() over(order by C) as Lp, 
	 char(right(CAST(CRYPT_GEN_RANDOM(4) AS INT),1)+65)
	+char(right(CAST(CRYPT_GEN_RANDOM(4) AS INT),1)+65)
	+char(right(CAST(CRYPT_GEN_RANDOM(4) AS INT),1)+65)
	+RIGHT(CAST(CRYPT_GEN_RANDOM(4) AS INT),6) as NumerDokumentu
  
   from Poziom5)


    insert into   dbo.DokumentyOsobiste (lp,NumerDokumentu,NumerDokumentuPoprawny)

    SELECT lp,NumerDokumentu,
		(
			left(NumerDokumentu,3)
			+
			convert(varchar(1),((7*ascii(substring(NumerDokumentu,1,1))-55)+
			(3*ascii(substring(NumerDokumentu,2,1))-55)+
			(1*ascii(substring(NumerDokumentu,3,1))-55)+
			-- tu jest suma kontrolna 
			7*convert(int,(substring(NumerDokumentu,5,1)))+
			3*convert(int,(substring(NumerDokumentu,6,1)))+
			1*convert(int,(substring(NumerDokumentu,7,1)))+
			7*convert(int,(substring(NumerDokumentu,8,1)))+
			3*convert(int,(substring(NumerDokumentu,9,1))) ) % 10)
			-- tu jest suma kontrolna
			+
			right(NumerDokumentu,5)
			) as NumerDokumentuPoprawny
        FROM Dokumenty
        WHERE lp <= 1000000-- limit

W posobny sposób można uzyskać inne typy danych wrażliwych

Przykładowy generator numerów PESEL

;WITH
 Poziom0 as (select 1 as C union all select 1), --2 rows
 Poziom1 as (select 1 as C from Poziom0 as A, Poziom0 as B),--4 rows
 Poziom2 as (select 1 as C from Poziom1 as A, Poziom1 as B),--16 rows
 Poziom3 as (select 1 as C from Poziom2 as A, Poziom2 as B),--256 rows
 Poziom4 as (select 1 as C from Poziom3 as A, Poziom3 as B),--65536 rows
 Poziom5 as (select 1 as C from Poziom4 as A, Poziom4 as B),--4294967296 rows
 PESELE  as (select row_number() over(order by C) as Lp,
   RIGHT('0'+Convert(varchar(2),ABS(Checksum(NewID()) % 99) + 1,2),2)  -- rok
   +RIGHT('0'+Convert(varchar(2),ABS(Checksum(NewID()) % 12) + 1,2),2) -- miesiac
   +RIGHT('0'+Convert(varchar(2),ABS(Checksum(NewID()) % 28) + 1,2),2) --dzien
   +RIGHT(CAST(CRYPT_GEN_RANDOM(4) AS INT),5) -- pozostala czesc
   as PESELSurowy
  
  from Poziom5)
  select lp,left(PESELSurowy,10)+
   
 convert(varchar(1), ((
   Convert(int,substring(PESELSurowy,1,1))*9
   +Convert(int,substring(PESELSurowy,2,1))*7
   +Convert(int,substring(PESELSurowy,3,1))*3
   +Convert(int,substring(PESELSurowy,4,1))*1
   +Convert(int,substring(PESELSurowy,5,1))*9
   +Convert(int,substring(PESELSurowy,6,1))*7
   +Convert(int,substring(PESELSurowy,7,1))*3
   +Convert(int,substring(PESELSurowy,8,1))*1
   +Convert(int,substring(PESELSurowy,9,1))*9
   +Convert(int,substring(PESELSurowy,10,1))*7
   )
    % 10))
   as PESEL ,
    (
        (Convert(int,substring(PESELSurowy,1,1))*9
       +Convert(int,substring(PESELSurowy,2,1))*7
       +Convert(int,substring(PESELSurowy,3,1))*3
       +Convert(int,substring(PESELSurowy,4,1))*1
       +Convert(int,substring(PESELSurowy,5,1))*9
       +Convert(int,substring(PESELSurowy,6,1))*7
       +Convert(int,substring(PESELSurowy,7,1))*3
       +Convert(int,substring(PESELSurowy,8,1))*1
       +Convert(int,substring(PESELSurowy,9,1))*9
       +Convert(int,substring(PESELSurowy,10,1))*7
       )
    % 10) as SumaKontrolna
    
   from PESELE
  where lp<=1000000;--limit 

Generator polskich numerów NRB.

  ;WITH
 Poziom0 as (select 1 as C union all select 1), --2 rows
 Poziom1 as (select 1 as C from Poziom0 as A, Poziom0 as B),--4 rows
 Poziom2 as (select 1 as C from Poziom1 as A, Poziom1 as B),--16 rows
 Poziom3 as (select 1 as C from Poziom2 as A, Poziom2 as B),--256 rows
 Poziom4 as (select 1 as C from Poziom3 as A, Poziom3 as B),--65536 rows
 Poziom5 as (select 1 as C from Poziom4 as A, Poziom4 as B),--4294967296 rows
 NRB  as (select row_number() over(order by C) as Lp,
 
   RIGHT('0'+Convert(varchar(2),ABS(Checksum(NewID()) % 99) + 1,2),2)
   +RIGHT('0'+Convert(varchar(2),ABS(Checksum(NewID()) % 99) + 1,2),2)
   +RIGHT('0'+Convert(varchar(2),ABS(Checksum(NewID()) % 99) + 1,2),2)
   +RIGHT('0'+Convert(varchar(2),ABS(Checksum(NewID()) % 99) + 1,2),2)
   +RIGHT('0'+Convert(varchar(2),ABS(Checksum(NewID()) % 99) + 1,2),2)
   +RIGHT('0'+Convert(varchar(2),ABS(Checksum(NewID()) % 99) + 1,2),2)
   +RIGHT('0'+Convert(varchar(2),ABS(Checksum(NewID()) % 99) + 1,2),2)
   +RIGHT('0'+Convert(varchar(2),ABS(Checksum(NewID()) % 99) + 1,2),2)
   +RIGHT('0'+Convert(varchar(2),ABS(Checksum(NewID()) % 99) + 1,2),2)
   +RIGHT('0'+Convert(varchar(2),ABS(Checksum(NewID()) % 99) + 1,2),2)
   +RIGHT('0'+Convert(varchar(2),ABS(Checksum(NewID()) % 99) + 1,2),2)
   +RIGHT('0'+Convert(varchar(2),ABS(Checksum(NewID()) % 99) + 1,2),2)
   +RIGHT('0'+Convert(varchar(2),ABS(Checksum(NewID()) % 99) + 1,2),2)
   as NRBsurowy
 
 
  from Poziom5)
 
  select lp,right('00'+convert(varchar(2),98-convert(numeric(30,0),right(NRBsurowy,24)+'252100')%97),2)+right(NRBsurowy,24) as NRB from NRB
  where lp<=1000000;
  

Budowa mechanizmu generowania danych jest szablonowa. Wykorzystywany jest CTE (Common Table Expression) w taki sposób, że
a) za pomocą kilku obiektów (aliasy PoziomX) można kontrolować liczbę zwracanych rekordów, ograniczenie dla pięciu poziomów jest liczbą rzędu 2 do potęgi 32 (ok 4 mld),
b) budowany jest napis o formacie zgodnych z danym typem numeru (długość, format) ale bez poprawnej sumy kontrolnej, nazywamy postacią surową
c)postać surowa jest dynamicznie podmieniana w taki sposób, że podmianiana jest część w tym miejsciu, gdzie występuje suma kontrolna, czasem jest to poczatek, środek lub koniec napisu.

2. Załóżmy, że te wygenerowane dokumenty/numery/konta są prawdziwe. Jak je zaktualizować, tak by wyglądały na poprawne i spełniały reguły? Ważne jest też to, by generować nowe wartości optymalnie i w miarę możliwości prostym kodem t-sql.
Zacznijmy od budowy pomocniczej perpektywy

CREATE VIEW dbo.VGenerateCrypts

AS

SELECT NEWID() AS CryptUID,
RIGHT(CAST(CRYPT_GEN_RANDOM(4) AS INT),6) as Crypt1,
RIGHT(CAST(CRYPT_GEN_RANDOM(4) AS INT),6) as Crypt2,
RIGHT(CAST(CRYPT_GEN_RANDOM(4) AS INT),6) as Crypt3,
RIGHT(CAST(CRYPT_GEN_RANDOM(4) AS INT),6) as Crypt4,
1 + CONVERT(INT, (99-1+1) * RAND()) as  CryptYear,
1 + CONVERT(INT, (12-1+1) * RAND()) as  CryptMonth,
1 + CONVERT(INT, (28-1+1) * RAND()) as  CryptDay

Widok zwraca jeden rekord, za każdym razie z innymi wartościami.

select * from dbo.VGenerateCrypts;

Do czego ma służyć ten widok ? Funkcja systemowa RAND() nie może zostać użyta wewnątrz funkcji skalarnej, takie mamy ograniczenie silnika. A takie właśnie funkcje będą podstawą procesu podmiany danych.O tym napiszę w drugiej części.