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

wpis w: implementacja, RODO, t-sql | 0

1. Dedykowany widok wykorzystujący funkcje pseudolosowe.

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

Do czego jest potrzebny ten widok? To jest obejście znanego problemu braku możliwości wykorzystania funkcji pseudolosowych w funkcjach skalarnych/tabelarycznych.
Przykładowo:

CREATE FUNCTION dbo.spGenerateNumber()
RETURNS varchar(100)
AS 
BEGIN
	RETURN newID()
END 
Próba kompilacji takiej funkcji kończy się komunikatem:
 Msg 443, Level 16, State 1, Procedure spGenerateNumber, Line 8
 Invalid use of a side-effecting operator 'newid' within a function.

2. Dedykowane funkcje tabelaryczne

Z założenia jedynym wykorzystywanym obiektem jest powyższy widok, pozostała implementacja nie wymaga dodatkowych struktur,tabel,dedykowanych funkcji i innych obiektów.
Każda z funkcji jest budowana schematycznie przy wykorzystaniu CTE, podobnie jest zapytania w pierwszej części artykułu. Dane generowane przez alias CTE zwracają wartość w postaci surowej i jeśli trzeba ją uzupełnić o poprawną sumę kontrolną odpowiednie podmiany postaci surowej na poprawną są w instrukcji select aliasu.
Funkcje przyjmują dwa parametry wejściowe @value i @algorithm. Ta ostatnia jest do poźniejszego wykorzystania w ramach rozwoju grupy algorytmów dla takiej samej domeny. Aktualne wersje funkcji nie korzystają z wartości wejściowej, ale można będzie to zmodyfikować na następnych iteracjach w ramach rozwoju, tak by wykorzystywany kod np. w procedurach składowanych nie ulegał zmianie ze względu liczbę argumentów funkcji.

Generowanie numerów PESEL

  CREATE FUNCTION dbo.spGenerateValueOfPESEL (@value varchar(11),@algorithm int=1)

  RETURNS TABLE
  AS 

 RETURN
 WITH generator AS
 (
   SELECT  
   RIGHT('0'+Convert(varchar(2),ABS(Checksum(c.CryptUID) % 99) + 1,2),2)
   +RIGHT('0'+Convert(varchar(2),ABS(Checksum(c.CryptUID) % 12) + 1,2),2) 
   +RIGHT('0'+Convert(varchar(2),ABS(Checksum(c.CryptUID) % 28) + 1,2),2) 
   +RIGHT(c.Crypt1,5) -- pozostala czesc
 
   as PESEL
   from dbo.VGenerateCrypts c
 )

 SELECT @value as InValue -- to co wejsciu
 
 ,left(PESEL,10)+
   
 convert(varchar(1), ((
   Convert(int,substring(PESEL,1,1))*9
   +Convert(int,substring(PESEL,2,1))*7
   +Convert(int,substring(PESEL,3,1))*3
   +Convert(int,substring(PESEL,4,1))*1
   +Convert(int,substring(PESEL,5,1))*9
   +Convert(int,substring(PESEL,6,1))*7
   +Convert(int,substring(PESEL,7,1))*3
   +Convert(int,substring(PESEL,8,1))*1
   +Convert(int,substring(PESEL,9,1))*9
   +Convert(int,substring(PESEL,10,1))*7
   )
    % 10)) AS OutValue 
	
	,@algorithm AS algorithm  
	 FROM generator;

Generowanie EMAIL

   CREATE FUNCTION [dbo].[spGenerateValueOfEMAIL] (@value varchar(100),@algorithm int=1)
   
   RETURNS TABLE
   
   AS
   
	RETURN

	 with generator AS
	  (

      
		  select @value as InValue 
	  
	  
			,RIGHT( LEFT('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',ABS(BINARY_CHECKSUM(c.CryptUID)%35) + 1 ),1) 
			+RIGHT( LEFT('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',ABS(BINARY_CHECKSUM(c.CryptUID)%30) + 1 ),1) 
			+RIGHT( LEFT('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',ABS(BINARY_CHECKSUM(c.CryptUID)%29) + 1 ),1) 
			+RIGHT( LEFT('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',ABS(BINARY_CHECKSUM(c.CryptUID)%45) + 1 ),1) 
			+RIGHT( LEFT('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',ABS(BINARY_CHECKSUM(c.CryptUID)%36) + 1 ),1)
			+'@'
			+RIGHT( LEFT('ABCDEFGHIJKLMNOPQRSTUVWXYZ',ABS(BINARY_CHECKSUM(c.CryptUID)%33) + 1 ),1) 
			+RIGHT( LEFT('ABCDEFGHIJKLMNOPQRSTUVWXYZ',ABS(BINARY_CHECKSUM(c.CryptUID)%15) + 1 ),1) 
			+RIGHT( LEFT('ABCDEFGHIJKLMNOPQRSTUVWXYZ',ABS(BINARY_CHECKSUM(c.CryptUID)%8) + 1 ),1)
			+'.PL'
	  
		   AS OutValue


		  ,@algorithm AS algorithm 
	   
		   FROM dbo.VGenerateCrypts c
	  )
	  SELECT InValue,OutValue,algorithm
 
	 FROM generator;

Generowanie dowodu osobistego

 CREATE  FUNCTION [dbo].[spGenerateValueOfIDCard] (@value varchar(100),@algorithm int=1)


Returns table



as

RETURN
(
with generator AS
(
select 

@value as InValue, 
char(right(CAST(c.Crypt1 AS INT),1)+65)
+char(right(CAST(c.Crypt2 AS INT),1)+65)
+char(right(CAST(c.Crypt3 AS INT),1)+65)
+c.Crypt4
 as IDCardNumber,
 
 @algorithm as algorithm 

from dbo.VGenerateCrypts c
)
select  InValue, 

left(IDCardNumber,3)
+
convert(varchar(1),((7*ascii(substring(IDCardNumber,1,1))-55)+
(3*ascii(substring(IDCardNumber,2,1))-55)+
(1*ascii(substring(IDCardNumber,3,1))-55)+
7*convert(int,(substring(IDCardNumber,5,1)))+
3*convert(int,(substring(IDCardNumber,6,1)))+
1*convert(int,(substring(IDCardNumber,7,1)))+
7*convert(int,(substring(IDCardNumber,8,1)))+
3*convert(int,(substring(IDCardNumber,9,1))) ) % 10)
+
right(IDCardNumber,5) as OutValue ,algorithm
from  generator


)

Generowanie rachunku bankowego

  
CREATE  FUNCTION [dbo].[spGenerateValueOfPolishBankAccount] (@value varchar(50),@algorithm int=1)


RETURNS TABLE


as

RETURN
(
WITH generator AS
(
SELECT 

 @value AS InValue, 
RIGHT('0'+Convert(varchar(2),ABS(Checksum(c.CryptUID) % 99) + 1,2),2)
   +RIGHT('0'+Convert(varchar(2),ABS(Checksum(c.CryptUID) % 99) + 1,2),2)
   +RIGHT('0'+Convert(varchar(2),ABS(Checksum(c.CryptUID) % 98) + 1,2),2)
   +RIGHT('0'+Convert(varchar(2),ABS(Checksum(c.CryptUID) % 97) + 1,2),2)
   +RIGHT('0'+Convert(varchar(2),ABS(Checksum(c.CryptUID) % 96) + 1,2),2)
   +RIGHT('0'+Convert(varchar(2),ABS(Checksum(c.CryptUID) % 95) + 1,2),2)
   +RIGHT('0'+Convert(varchar(2),ABS(Checksum(c.CryptUID) % 94) + 1,2),2)
   +RIGHT('0'+Convert(varchar(2),ABS(Checksum(c.CryptUID) % 93) + 1,2),2)
   +RIGHT('0'+Convert(varchar(2),ABS(Checksum(c.CryptUID) % 92) + 1,2),2)
   +RIGHT('0'+Convert(varchar(2),ABS(Checksum(c.CryptUID) % 91) + 1,2),2)
   +RIGHT('0'+Convert(varchar(2),ABS(Checksum(c.CryptUID) % 90) + 1,2),2)
   +RIGHT('0'+Convert(varchar(2),ABS(Checksum(c.CryptUID) % 89) + 1,2),2)
   +RIGHT('0'+Convert(varchar(2),ABS(Checksum(c.CryptUID) % 88) + 1,2),2)
   AS Account,

 @algorithm AS algorithm 

from dbo.VGenerateCrypts c
)
select  InValue, 
-- poprawa sumy kontrolnej
right('00'+convert(varchar(2),98-convert(numeric(30,0),right(Account,24)+'252100')%97),2)+right(Account,24) as OutValue
,algorithm
FROM  generator

)

Generowanie imion

  CREATE  function [dbo].[spGenerateValueOfFirstName] (@value varchar(50),@algorithm int=1)
   
   RETURNS TABLE
   
   AS
   
	RETURN

	 with generator AS
	  (

      
		  select @value as InValue 
	  
			,RIGHT( LEFT('ABCDEFGHIJKLMNOPQRSTUVWXYZ',ABS(BINARY_CHECKSUM(c.CryptUID)%26) + 1 ),3) 
			+RIGHT( LEFT('ABCDEFGHIJKLMNOPQRSTUVWXY',ABS(BINARY_CHECKSUM(c.CryptUID)%25) + 1 ),3) 
			+RIGHT( LEFT('ABCDEFGHIJKLMNOPQRSTUVWXYZ',ABS(BINARY_CHECKSUM(c.CryptUID)%24) + 1 ),3) 
			+RIGHT( LEFT('ABCDEFGHIJKLMNOPQRSTUVWXYZ',ABS(BINARY_CHECKSUM(c.CryptUID)%23) + 1 ),3) 
			+RIGHT( LEFT('ABCDEFGHIJKLMNOPQRSTUVWXYZ',ABS(BINARY_CHECKSUM(c.CryptUID)%22) + 1 ),3)
			+RIGHT( LEFT('ABCDEFGHIJKLMNOPQRSTUVWXYZ',ABS(BINARY_CHECKSUM(c.CryptUID)%21) + 1 ),3) 
			+RIGHT( LEFT('ABCDEFGHIJKLMNOPQRSTUVWXYZ',ABS(BINARY_CHECKSUM(c.CryptUID)%20) + 1 ),3) 
			+RIGHT( LEFT('ABCDEFGHIJKLMNOPQRSTUVWXYZ',ABS(BINARY_CHECKSUM(c.CryptUID)%19) + 1 ),3)
			+RIGHT( LEFT('ABCDEFGHIJKLMNOPQRSTUVWXYZ',ABS(BINARY_CHECKSUM(c.CryptUID)%18) + 1 ),3) 
			+RIGHT( LEFT('ABCDEFGHIJKLMNOPQRSTUVWXYZ',ABS(BINARY_CHECKSUM(c.CryptUID)%17) + 1 ),3) 
			+RIGHT( LEFT('ABCDEFGHIJKLMNOPQRSTUVWXYZ',ABS(BINARY_CHECKSUM(c.CryptUID)%16) + 1 ),3)
		   as OutValue,
		   ABS(Checksum(c.CryptUID) % 25)+5 as Length

		  ,@algorithm as algorithm 
	   
		   from dbo.VGenerateCrypts c
	  )
	  select InValue,left(OutValue,Length) as OutValue,algorithm
 
	 from generator;

Generowanie kodu pocztowego

 CREATE FUNCTION dbo.spGenerateValueOfPostalCode(@value varchar(11),@algorytm int=1)


  RETURNS TABLE
  AS

 RETURN
 WITH generator AS
 (
   select  
   RIGHT('0'+Convert(varchar(2),ABS(Checksum(c.CryptUID) % 38) + 1,2),1)+
   +RIGHT('0'+Convert(varchar(2),ABS(Checksum(c.CryptUID) % 36) + 1,2),1)
   +'-'
   +RIGHT('0'+Convert(varchar(2),ABS(Checksum(c.CryptUID) % 34) + 1,2),1)
   +RIGHT('0'+Convert(varchar(2),ABS(Checksum(c.CryptUID) % 32) + 1,2),1)
   +RIGHT('0'+Convert(varchar(2),ABS(Checksum(c.CryptUID) % 30) + 1,2),1)

   as PostalCode 
   from dbo.VGenerateCrypts c
 )

 select @value as InValue, 
		PostalCode as OutValue, 
		@algorytm as algorytm 
	 from generator;

3. Generowanie danych testowych (TODO)

use AdventureWorks2012

GO

CREATE PROCEDURE dbo.spGenerateRandomTestData
AS

BEGIN

IF OBJECT_ID('AdventureWorks2012.dbo.RandomTestData') is  null
BEGIN

 CREATE TABLE dbo.RandomTestData
 (lp int not null Primary Key,
 PESEL varchar(11) not null,
 EMAIL varchar(50) null, 
 DowodOsobisty varchar(50)  null,
 NRB varchar(50)  null,
 IMIE varchar(50)  null,
 KodPocztowy varchar(6)  null
 )


END

truncate table dbo.RandomTestData


 ;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
  Dane  AS (SELECT ROW_NUMBER() OVER(ORDER BY C) AS Lp, 
	 '' AS Wartosc
 
   FROM Poziom5)

   insert into  dbo.RandomTestData

	(lp,PESEL,EMAIL,DowodOsobisty,NRB,IMIE,KodPocztowy) 

   select lp,pes.OutValue as PESEL,email.OutValue,dowod.OutValue,nrb.OutValue,name.OutValue,kod.OutValue from dane d
   CROSS apply dbo.spGenerateValueOfPESEL(d.Wartosc,1)  pes
   CROSS apply dbo.spGenerateValueOfEMAIL(d.Wartosc,1)  email
   CROSS apply dbo.spGenerateValueOfIDCard(d.Wartosc,1) dowod
   CROSS apply dbo.spGenerateValueOfPolishBankAccount (d.Wartosc,1) nrb
   CROSS apply dbo.spGenerateValueOfFirstname(d.Wartosc,1)  name
   CROSS apply dbo.spGenerateValueOfPostalCode(d.Wartosc,1) kod 
   WHERE lp <= 1000000-- limit


RETURN (0)

END 

4. Wykorzystanie funkcji tabelarycznych do zmiany wygenerowanych danych testowych (TODO)

Dodaj komentarz

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *

This site uses Akismet to reduce spam. Learn how your comment data is processed.