Wykrywanie danych osobowych za pomocą T-sql – wersja dla ubogich

Nadchodzi nowa regulacja dotycząca ochrony danych osobowych (RODO) termin wdrożenia  to maj 2018, nie ma więc wiele czasu.
Regulacja wnosi wiele obostrzeń w stosunku do obecnie działających przepisów, ale i tak pracę organiczną należy zacząć od analizy danych przechowywanych na naszych serwerach.

Na rynku jest sporo gotowych i drogich narzędzi, które potrafią wykryć dane za pomocą wbudowanych reguł.

Postanowiłem się zmierzyć z takim zadaniem wykorzystując kod t-sql i zbudować proste narzędzie do Data Discovery, a właściwie jego pierwszy szkic.

Poniżej znajdziesz propozycje implementacji niektórych zagadnień, które przydadzą się do budowy twojego własnego rozwiązania.

Założenia

Narzędzie będzie wymagać instalacji dodatkowej bazy danych np. o nazwie RODO, na której umieścimy kod procedury składowanej, dedykowane tabele i słowniki.

Kod pozostałych na instancji baz nie powinien ulec zmianie.

Budowa procedury składowanej na dedykowanej instancji serwera.

Procedura pobiera listę tabel i kolumn tekstowych dla całej instancji serwera sql.

Ograniczono listę kolumn do  typów char, nchar, varchar, nvarchar.

Dodatkowo dla każdej z tabeli  odłożono liczbę rekordów i aktualnie ustawiony seed dla pól typu autonumer (identity)

Procedura jest sterowana parametrem konfiguracyjnym w postaci XML-a.

Dynamicznych sql pobierane są poszczególne kolumny danej tabeli . Tu można zastosować limit liczby rekordów lub limit procentowy, np 5% całości.

Po załadowaniu do dedykowanej tabeli danych z całej instancji proces rozpoczyna analizę metadanych i danych.

Zastosowane algorytmy

a) nazewnictwo kolumn,

zakładam na przykład , że pole char(11) lub varchar(11) o nazwie PESEL zawiera w sobie numer ewidencyjny

b) wyrażenia regularne

Ze względu na słabe wsparcie  natywne z poziomu języka sql zastosowałem dedykowaną procedurę CLR.

Bardzo efektywne do weryfikacji np. kodu pocztowego w postaci

c) funkcje skalarne zwracające wartość  1 (prawda) lub 0 (fałsz), parametrem wejściowym jest napis.

Funkcje te można łączyć wraz z wyrażeniami regularnymi

d) dedykowane słowniki

Na początek warto zacząć od słownika imion i miejscowości w Polsce.

Kod przetestowano na instancji serwera sql 2012 w wersji developer.

To z czym się zmierzyłem na początku.

Pobranie listy tabel i kolumn tekstowych w obrębie serwera

Bazy mogę mieć róźne collation, wykorzystałem wymuszenie konwersji podczas operacji porównania

COLLATE SQL_Latin1_General_CP1250_CI_AS

Bazy techniczne master,msdb,model i tempdb zostały wykluczone

Wykorzystano systemową procedurę sp_MSforeachdb,
która jest wywoływana dla każdej bazy danych instancji serwera w taki sposób, ze fragment dynamiczny kodu sql  ‘?’ jest zamieniany na nazwę bazy.

if object_id('tempdb..#DaneOsoboweTabele') is not null


begin
   drop table  #DaneOsoboweTabele
end  
begin 
CREATE TABLE #DaneOsoboweTabele(
    [lp] [int] NULL,
    [ServerName] [varchar](128) COLLATE SQL_Latin1_General_CP1250_CI_AS NULL,
    [DataBaseName] [sysname] NOT NULL,
    [SchemaName] [sysname] NOT NULL,
    [NumberOfRows] [bigint] NULL,
    [TableName] [sysname] NOT NULL,
    [ColumnName] [sysname] NULL,
    [Typ] [nvarchar](141) COLLATE SQL_Latin1_General_CP1250_CI_AS NULL,
    [status] [int] NULL,
    [IdentityColumnName] [sysname] COLLATE SQL_Latin1_General_CP1250_CI_AS NULL,
    [IdentitySeed] [sql_variant]    NULL,
    [IdentityIncrement] [sql_variant]   NULL,
    [IdentityValue] [sql_variant]    NULL
) 

create clustered index idx1 on #DaneOsoboweTabele([DataBaseName],[SchemaName],[TableName])

end 

declare @tableSQL NVARCHAR(4000)

SET @tableSQL ='

select 
ROW_NUMBER() OVER(order by DB_NAME(),st.name,ss.name,sc.name) as lp,
@@servername as ServerName,
''?'' as DataBaseName,
ss.name as SchemaName,
sum(pa.rows) as NumberOfRows,
st.name as TableName,
sc.name as ColumnName,
sst.name+'' (''+convert(varchar(10),sc.max_length)+'')'' as Typ,
0,
IC.name AS IdentityColumnName,
IC.seed_value as  IdentitySeed,
IC.increment_value AS IdentityIncrement, 
IC.last_value as IdentityValue

from [?].sys.columns sc with (nolock)
join [?].sys.tables st with (nolock)
on (st.object_id=sc.object_id)
join [?].sys.systypes sst with (nolock)
on (sst.xtype=sc.system_type_id and sst.status=0)
join [?].sys.schemas ss with (nolock)
on (ss.schema_id=st.schema_id)

INNER JOIN [?].sys.partitions pa with (nolock)
ON pa.OBJECT_ID = st.OBJECT_ID AND pa.index_id IN (1,0)
left join [?].sys.identity_columns IC with (nolock)
on (ic.object_id=st.object_id)
where
st.type=''U''
and ''?'' not in (''model'',''master'',''tempdb'',''msdb'')
and sst.xtype  in (167,231,175,239) -- nchar, nvarchar, 
and not exists

(
   select 1  from   #DaneOsoboweTabele baza with (nolock)
   where baza.DataBaseName=''?''
   and baza.SchemaName  COLLATE SQL_Latin1_General_CP1250_CI_AS = ss.name
   and baza.TableName  COLLATE SQL_Latin1_General_CP1250_CI_AS =st.name

)

GROUP by
ss.name ,
st.name,sc.name,sc.max_length,sc.system_type_id,sst.name,
-- identity
IC.name ,
IC.seed_value,
IC.increment_value ,
IC.last_value
order by ss.name,st.name,sc.name'

SET NOCOUNT OFF

insert into  #DaneOsoboweTabele
(
    [lp], 
    [ServerName], 
    [DataBaseName], 
    [SchemaName] ,
    [NumberOfRows], 
    [TableName] ,
    [ColumnName],
    [Typ] ,
    [status], 
    [IdentityColumnName],
    [IdentitySeed] ,
    [IdentityIncrement], 
    [IdentityValue]
)


EXEC sp_MSforeachdb @tableSQL



select * from  #DaneOsoboweTabele
order by [ServerName], [DataBaseName], [SchemaName] ,[TableName] ,[ColumnName]


Na końcu procesu zapisujemy dane z tabeli tymczasowej do tabeli trwalej np

DaneOsoboweTabele

W tym miejscu mamy listę kolumn w tabelach z poszczegolnych baz banych, które nalezy poddać analizie

Sampling danych

Wykorzystano rozszerzenie t-sql  znane od wersji 2005, czyli TABLESAMPLE

Przykładowy kod na demonstracyjnej bazie danych

use AdventureWorks2012
-- wersja z liczbą rekordów , pobieramy 1000 rekordów losowo
select * from Person.Address TableSample( 1000 rows)
-- wersja z procentem liczby rekordów, pobieramy 1% losowo
select * from Person.Address TableSample( 1 percent)

Sapling danych zapisujemy do kolejnej tabeli trwałej np.

dbo.DaneOsoboweDane

Czyli dla każdego rekordu z  DaneOsoboweTabele

zapisujemy po kilkaset/kilka tysięcy/kilkanaście tysięcy rekordów. Zależy to oczywiście od liczby rekordów w poszczególnych tabelach

Przykładowy fragment kodu

	declare @id int
	declare @lp int
	declare @numberofRows bigint
	declare @databaseName varchar(255)
	declare @schemaName varchar(255)
	declare @tablename varchar(255)
	declare @columnname varchar(255)
	declare @identityColumnName varchar(255)
	declare @sql nvarchar(4000)
	declare @counter INT
  
  -- rekord do analizy
  select top 1 @id=id, @numberofRows=numberofrows,@databaseName=databasename, @schemaName=schemaName,@tablename=tablename,@columnname=columnname,@identityColumnName=identityColumnName from  dbo.DaneOsoboweTabele  where status=0
  order by id,lp,tablename,columnname

  -- nie pozwalamy na przekroczenie liczby powyzej granicznej
  if @numberofRows>@limitsampli
    set @numberofRows=@limitsampli
  set @sql=' insert into dbo.DaneOsoboweDane (id,DataValue,ReverseDataValue) '
  set @sql=@sql+' select top ( '+convert(varchar(20),@numberofRows)+' )  '+ str(@id)+' as id ,left(' +@columnname+',400)  as ' +@columnname+' , right(REVERSE('+@columnname+'),400) as ' +@columnname+' from '+@databasename+'.'+@schemaName+'.'+@tablename
  set @sql=@sql+ ' TABLESAMPLE ( '+convert(varchar(20),@numberofRows)+' '+' ROWS) '

  -- sprawdzamy czy nie ma juz danych w bazie dla danej kolumny danej tabeli  danego schematu i danej bazy
  if not exists
  (
     select 1 from dbo.DaneOsoboweDane
	 where id=@id and lp=@lp 
  )
  begin 
    -- kody dynamicznego sql-a
	exec sp_executesql  @sql
  end

W tym momencie w tabeli DaneOsoboweDane mamy już próbkę danych z całęj instancji serwera sql.

Teraz to co należy zrobić, to przeskanować każdą grupę danych za pomocą  np. wyrażeń regularnych.

Wyrażenie regularne

Przykładowe reguły:

Kod pocztowy:

^\d{2}-\d{3}$

Numer IP:

^\b(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0 -9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\b$

E-mail:

^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+$

Przykładowe wywołanie procedury do wykrywania danych osobowych

declare  @KonFiguracjaXML xml

set  @KonFiguracjaXML='


abc
imie
nazwisko
ulica
kodpocztowy
pesel
nip
regon
adres
miejscowosc
poczta




2
NIP
^(\(d{3}-\d{3}-\d{2}-\d{2})|(d{3}-\d{2}-\d{2}-\d{3})$
1



3
PESEL
^\d{11}$
1



4
REGON
^\d{90}$
1



5
PostalCode
^\d{2}-\d{3}$
1


6
IPADDRESS
^\b(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0 -9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\b$
1





8
IBAN
^\d{26}$
0



9
DOWOD1
^[A-Za-z]{3}\d{6}$
1



10
DOWOD2
^[A-Za-z]{2}\d{7}$
1



11
PolishCellularPhone
^\+?[4][8]-?[0-9]{9}$
0



12
EMAIL
^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+$
0

 

13
PHONE600
^60[0-9][0-9]{6}$
0



14
PHONE660
^66[0-5][0-9]{6}$
0



15
PHONE691
^69[1-8][0-9]{6}$
0



16
PHONE669
^66[7-9][0-9]{6}$
0






'


execute   dbo.spWyszukajDaneOsobowe 
@KonFiguracjaXML=@KonFiguracjaXML,
@inDebug=1,
@limitTabel =5000,
@limitrekordow  =10000,
@limitsampli=2000,
@limitprzetwarzania=5000,
@tryb=0

Celem działania procedury powinien być raport, który pokaże w której kolumnie danej tabeli znajdują się

dane spełniająca zastosowane wyżej kryteria

Przykładowy raport z bazy  AdventureWorks2012:

DataBaseName

AdventureWorks2012

Krzysztof Pudłowski

Literatura:

https://www.mssqltips.com/sqlservertip/1414/run-same-command-on-all-sql-server-databases-without-cursors/

https://www.mssqltips.com/sqlservertip/1308/retrieving-random-data-from-sql-server-with-tablesample/

https://technet.microsoft.com/en-us/library/ff878119(v=sql.110).aspx

https://github.com/DevNambi/sql-server-regex