Nadchodzi nowa regulacja dotycz\u0105ca ochrony danych osobowych (RODO) termin wdro\u017cenia\u00a0 to maj 2018, nie ma wi\u0119c wiele czasu.
\nRegulacja wnosi wiele obostrze\u0144 w stosunku do obecnie dzia\u0142aj\u0105cych przepis\u00f3w, ale i tak prac\u0119 organiczn\u0105 nale\u017cy zacz\u0105\u0107 od analizy danych przechowywanych na naszych serwerach.<\/p>\n
Na rynku jest sporo gotowych i drogich narz\u0119dzi, kt\u00f3re potrafi\u0105 wykry\u0107 dane za pomoc\u0105 wbudowanych regu\u0142.<\/p>\n
Postanowi\u0142em si\u0119 zmierzy\u0107 z takim zadaniem wykorzystuj\u0105c kod t-sql i zbudowa\u0107 proste narz\u0119dzie do Data Discovery, a w\u0142a\u015bciwie jego pierwszy szkic.<\/p>\n
<\/p>\n
Narz\u0119dzie b\u0119dzie wymaga\u0107 instalacji dodatkowej bazy danych np. o nazwie RODO, na kt\u00f3rej umie\u015bcimy kod procedury sk\u0142adowanej, dedykowane tabele i s\u0142owniki.<\/p>\n
Kod pozosta\u0142ych na instancji baz nie powinien ulec zmianie.<\/p>\n
Budowa procedury sk\u0142adowanej na dedykowanej instancji serwera.<\/p>\n
Procedura pobiera list\u0119 tabel i kolumn tekstowych dla ca\u0142ej instancji serwera sql.<\/p>\n
Ograniczono list\u0119 kolumn do\u00a0 typ\u00f3w char, nchar, varchar, nvarchar.<\/p>\n
Dodatkowo dla ka\u017cdej z tabeli\u00a0 od\u0142o\u017cono liczb\u0119 rekord\u00f3w i aktualnie ustawiony seed dla p\u00f3l typu autonumer (identity)<\/p>\n
Procedura jest sterowana parametrem konfiguracyjnym w postaci XML-a.<\/p>\n
Dynamicznych sql pobierane s\u0105 poszczeg\u00f3lne kolumny danej tabeli . Tu mo\u017cna zastosowa\u0107 limit liczby rekord\u00f3w lub limit procentowy, np 5% ca\u0142o\u015bci.<\/p>\n
Po za\u0142adowaniu do dedykowanej tabeli danych z ca\u0142ej instancji proces rozpoczyna analiz\u0119 metadanych i danych.<\/p>\n
a) nazewnictwo kolumn,<\/p>\n
zak\u0142adam na przyk\u0142ad , \u017ce pole char(11) lub varchar(11) o nazwie PESEL zawiera w sobie numer ewidencyjny<\/p>\n
b) wyra\u017cenia regularne<\/p>\n
Ze wzgl\u0119du na s\u0142abe wsparcie\u00a0 natywne z poziomu j\u0119zyka sql zastosowa\u0142em dedykowan\u0105 procedur\u0119 CLR.<\/p>\n
Bardzo efektywne do weryfikacji np. kodu pocztowego w postaci<\/p>\n
c) funkcje skalarne zwracaj\u0105ce warto\u015b\u0107\u00a0 1 (prawda) lub 0 (fa\u0142sz), parametrem wej\u015bciowym jest napis.<\/p>\n
Funkcje te mo\u017cna \u0142\u0105czy\u0107 wraz z wyra\u017ceniami regularnymi<\/p>\n
d) dedykowane s\u0142owniki<\/p>\n
Na pocz\u0105tek warto zacz\u0105\u0107 od s\u0142ownika imion i miejscowo\u015bci w Polsce.<\/p>\n
<\/p>\n
Kod przetestowano na instancji serwera sql 2012 w wersji developer.<\/p>\n
To z czym si\u0119 zmierzy\u0142em na pocz\u0105tku.<\/p>\n
Bazy mog\u0119 mie\u0107 r\u00f3\u017ane collation, wykorzysta\u0142em wymuszenie konwersji podczas operacji por\u00f3wnania<\/p>\n
COLLATE SQL_Latin1_General_CP1250_CI_AS<\/pre>\nBazy techniczne master,msdb,model i tempdb zosta\u0142y wykluczone<\/p>\n
Wykorzystano systemow\u0105 procedur\u0119 sp_MSforeachdb,
\n<\/strong>kt\u00f3ra jest wywo\u0142ywana dla ka\u017cdej bazy danych instancji serwera w taki spos\u00f3b, ze fragment dynamiczny kodu sql\u00a0 ‘?’ jest zamieniany na nazw\u0119 bazy.<\/p>\n<\/p>\n
if object_id('tempdb..#DaneOsoboweTabele') is not null\r\n\r\n\r\nbegin\r\n\u00a0\u00a0 drop table\u00a0 #DaneOsoboweTabele\r\nend \u00a0\r\nbegin \r\nCREATE TABLE #DaneOsoboweTabele(\r\n\u00a0\u00a0 \u00a0[lp] [int] NULL,\r\n\u00a0\u00a0 \u00a0[ServerName] [varchar](128) COLLATE SQL_Latin1_General_CP1250_CI_AS NULL,\r\n\u00a0\u00a0 \u00a0[DataBaseName] [sysname] NOT NULL,\r\n\u00a0\u00a0 \u00a0[SchemaName] [sysname] NOT NULL,\r\n\u00a0\u00a0 \u00a0[NumberOfRows] [bigint] NULL,\r\n\u00a0\u00a0 \u00a0[TableName] [sysname] NOT NULL,\r\n\u00a0\u00a0 \u00a0[ColumnName] [sysname] NULL,\r\n\u00a0\u00a0 \u00a0[Typ] [nvarchar](141) COLLATE SQL_Latin1_General_CP1250_CI_AS NULL,\r\n\u00a0\u00a0 \u00a0[status] [int] NULL,\r\n\u00a0\u00a0 \u00a0[IdentityColumnName] [sysname] COLLATE SQL_Latin1_General_CP1250_CI_AS NULL,\r\n\u00a0\u00a0 \u00a0[IdentitySeed] [sql_variant]\u00a0\u00a0\u00a0 NULL,\r\n\u00a0\u00a0 \u00a0[IdentityIncrement] [sql_variant]\u00a0\u00a0 NULL,\r\n\u00a0\u00a0 \u00a0[IdentityValue] [sql_variant]\u00a0\u00a0\u00a0 NULL\r\n) \r\n\r\ncreate clustered index idx1 on #DaneOsoboweTabele([DataBaseName],[SchemaName],[TableName])\r\n\r\nend \r\n\r\ndeclare @tableSQL NVARCHAR(4000)\r\n\r\nSET @tableSQL ='\r\n\r\nselect \r\nROW_NUMBER() OVER(order by DB_NAME(),st.name,ss.name,sc.name) as lp,\r\n@@servername as ServerName,\r\n''?'' as DataBaseName,\r\nss.name as SchemaName,\r\nsum(pa.rows) as NumberOfRows,\r\nst.name as TableName,\r\nsc.name as ColumnName,\r\nsst.name+'' (''+convert(varchar(10),sc.max_length)+'')'' as Typ,\r\n0,\r\nIC.name AS IdentityColumnName,\r\nIC.seed_value as\u00a0 IdentitySeed,\r\nIC.increment_value AS IdentityIncrement, \r\nIC.last_value as IdentityValue\r\n\r\nfrom [?].sys.columns sc with (nolock)\r\njoin [?].sys.tables st with (nolock)\r\non (st.object_id=sc.object_id)\r\njoin [?].sys.systypes sst with (nolock)\r\non (sst.xtype=sc.system_type_id and sst.status=0)\r\njoin [?].sys.schemas ss with (nolock)\r\non (ss.schema_id=st.schema_id)\r\n\r\nINNER JOIN [?].sys.partitions pa with (nolock)\r\nON pa.OBJECT_ID = st.OBJECT_ID AND pa.index_id IN (1,0)\r\nleft join [?].sys.identity_columns IC with (nolock)\r\non (ic.object_id=st.object_id)\r\nwhere\r\nst.type=''U''\r\nand ''?'' not in (''model'',''master'',''tempdb'',''msdb'')\r\nand sst.xtype\u00a0 in (167,231,175,239) -- nchar, nvarchar, \r\nand not exists\r\n\r\n(\r\n\u00a0\u00a0 select 1\u00a0 from\u00a0\u00a0 #DaneOsoboweTabele baza with (nolock)\r\n\u00a0\u00a0 where baza.DataBaseName=''?''\r\n\u00a0\u00a0 and baza.SchemaName\u00a0 COLLATE SQL_Latin1_General_CP1250_CI_AS = ss.name\r\n\u00a0\u00a0 and baza.TableName\u00a0 COLLATE SQL_Latin1_General_CP1250_CI_AS =st.name\r\n\r\n)\r\n\r\nGROUP by\r\nss.name ,\r\nst.name,sc.name,sc.max_length,sc.system_type_id,sst.name,\r\n-- identity\r\nIC.name ,\r\nIC.seed_value,\r\nIC.increment_value ,\r\nIC.last_value\r\norder by ss.name,st.name,sc.name'\r\n\r\nSET NOCOUNT OFF\r\n\r\ninsert into\u00a0 #DaneOsoboweTabele\r\n(\r\n\u00a0\u00a0 \u00a0[lp], \r\n\u00a0\u00a0 \u00a0[ServerName], \r\n\u00a0\u00a0 \u00a0[DataBaseName], \r\n\u00a0\u00a0 \u00a0[SchemaName] ,\r\n\u00a0\u00a0 \u00a0[NumberOfRows], \r\n\u00a0\u00a0 \u00a0[TableName] ,\r\n\u00a0\u00a0 \u00a0[ColumnName],\r\n\u00a0\u00a0 \u00a0[Typ] ,\r\n\u00a0\u00a0 \u00a0[status], \r\n\u00a0\u00a0 \u00a0[IdentityColumnName],\r\n\u00a0\u00a0 \u00a0[IdentitySeed] ,\r\n\u00a0\u00a0 \u00a0[IdentityIncrement], \r\n\u00a0\u00a0 \u00a0[IdentityValue]\r\n)\r\n\r\n\r\nEXEC sp_MSforeachdb @tableSQL\r\n\r\n\r\n\r\nselect * from\u00a0 #DaneOsoboweTabele\r\norder by [ServerName], [DataBaseName], [SchemaName] ,[TableName] ,[ColumnName]\r\n\r\n\r\nNa ko\u0144cu procesu zapisujemy dane z tabeli tymczasowej do tabeli trwalej np\r\n\r\nDaneOsoboweTabele<\/strong>\r\n\r\nW tym miejscu mamy list\u0119 kolumn w tabelach z poszczegolnych baz banych, kt\u00f3re nalezy podda\u0107 analizie\r\n\r\n\r\n<\/pre>\nSampling danych<\/h3>\n
Wykorzystano rozszerzenie t-sql\u00a0 znane od wersji 2005, czyli TABLESAMPLE<\/strong><\/p>\n
Przyk\u0142adowy kod na demonstracyjnej bazie danych<\/p>\n
use AdventureWorks2012\r\n-- wersja z liczb\u0105 rekord\u00f3w , pobieramy 1000 rekord\u00f3w losowo\r\nselect * from Person.Address TableSample( 1000 rows)\r\n-- wersja z procentem liczby rekord\u00f3w, pobieramy 1% losowo\r\nselect * from Person.Address TableSample( 1 percent)\r\n\r\n<\/pre>\nSapling danych zapisujemy do kolejnej tabeli trwa\u0142ej np.<\/p>\n
dbo.DaneOsoboweDane<\/p>\n
Czyli dla ka\u017cdego rekordu z\u00a0 DaneOsoboweTabele<\/p>\n
zapisujemy po kilkaset\/kilka tysi\u0119cy\/kilkana\u015bcie tysi\u0119cy rekord\u00f3w. Zale\u017cy to oczywi\u015bcie od liczby rekord\u00f3w w poszczeg\u00f3lnych tabelach<\/p>\n
Przyk\u0142adowy fragment kodu<\/p>\n
\tdeclare @id int\r\n\tdeclare @lp int\r\n\tdeclare @numberofRows bigint\r\n\tdeclare @databaseName varchar(255)\r\n\tdeclare @schemaName varchar(255)\r\n\tdeclare @tablename varchar(255)\r\n\tdeclare @columnname varchar(255)\r\n\tdeclare @identityColumnName varchar(255)\r\n\tdeclare @sql nvarchar(4000)\r\n\tdeclare @counter INT\r\n \r\n -- rekord do analizy\r\n select top 1 @id=id, @numberofRows=numberofrows,@databaseName=databasename, @schemaName=schemaName,@tablename=tablename,@columnname=columnname,@identityColumnName=identityColumnName from dbo.DaneOsoboweTabele where status=0\r\n order by id,lp,tablename,columnname\r\n\r\n -- nie pozwalamy na przekroczenie liczby powyzej granicznej\r\n if @numberofRows>@limitsampli\r\n set @numberofRows=@limitsampli\r\n set @sql=' insert into dbo.DaneOsoboweDane (id,DataValue,ReverseDataValue) '\r\n 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\r\n set @sql=@sql+ ' TABLESAMPLE ( '+convert(varchar(20),@numberofRows)+' '+' ROWS) '\r\n\r\n -- sprawdzamy czy nie ma juz danych w bazie dla danej kolumny danej tabeli danego schematu i danej bazy\r\n if not exists\r\n (\r\n select 1 from dbo.DaneOsoboweDane\r\n\t where id=@id and lp=@lp \r\n )\r\n begin \r\n -- kody dynamicznego sql-a\r\n\texec sp_executesql @sql\r\n end\r\n<\/pre>\nW tym momencie w tabeli DaneOsoboweDane mamy ju\u017c pr\u00f3bk\u0119 danych z ca\u0142\u0119j instancji serwera sql.<\/p>\n
Teraz to co nale\u017cy zrobi\u0107, to przeskanowa\u0107 ka\u017cd\u0105 grup\u0119 danych za pomoc\u0105\u00a0 np. wyra\u017ce\u0144 regularnych.<\/p>\n
Wyra\u017cenie regularne<\/h3>\n
Przyk\u0142adowe regu\u0142y:<\/p>\n
Kod pocztowy:<\/p>\n
^\\d{2}-\\d{3}$<\/p>\n
Numer IP:<\/p>\n
^\\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$<\/p>\n
E-mail:<\/p>\n
^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\\.[a-zA-Z0-9-.]+$<\/p>\n
<\/p>\n
<\/p>\n
Przyk\u0142adowe wywo\u0142anie procedury do wykrywania danych osobowych<\/p>\n
\r\n\r\ndeclare\u00a0 @KonFiguracjaXML xml\r\n\r\nset @KonFiguracjaXML='\r\n \r\n\r\n abc<\/NazwaKolumny>\r\n imie<\/NazwaKolumny>\r\n nazwisko<\/NazwaKolumny>\r\n ulica<\/NazwaKolumny>\r\n kodpocztowy<\/NazwaKolumny>\r\n pesel<\/NazwaKolumny>\r\n nip<\/NazwaKolumny>\r\n regon<\/NazwaKolumny>\r\n adres<\/NazwaKolumny>\r\n miejscowosc<\/NazwaKolumny>\r\n poczta<\/NazwaKolumny>\r\n<\/Kolumny>\r\n\r\n \r\n \r\n 2<\/Id>\r\n NIP<\/Name>\r\n ^(\\(d{3}-\\d{3}-\\d{2}-\\d{2})|(d{3}-\\d{2}-\\d{2}-\\d{3})$<\/Formula>\r\n 1<\/Priority>\r\n<\/Reg>\r\n\r\n \r\n 3<\/Id>\r\n PESEL<\/Name>\r\n ^\\d{11}$<\/Formula>\r\n 1<\/Priority>\r\n<\/Reg>\r\n\r\n \r\n 4<\/Id>\r\n REGON<\/Name>\r\n ^\\d{90}$<\/Formula>\r\n 1<\/Priority>\r\n<\/Reg>\r\n\r\n \r\n 5<\/Id>\r\n PostalCode<\/Name>\r\n ^\\d{2}-\\d{3}$<\/Formula>\r\n 1<\/Priority>\r\n<\/Reg>\r\n \r\n 6<\/Id>\r\n IPADDRESS<\/Name>\r\n ^\\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$<\/Formula>\r\n 1<\/Priority>\r\n<\/Reg>\r\n\r\n\r\n\r\n \r\n 8<\/Id>\r\n IBAN<\/Name>\r\n ^\\d{26}$<\/Formula>\r\n 0<\/Priority>\r\n<\/Reg>\r\n\r\n \r\n 9<\/Id>\r\n DOWOD1<\/Name>\r\n ^[A-Za-z]{3}\\d{6}$<\/Formula>\r\n 1<\/Priority>\r\n<\/Reg>\r\n\r\n \r\n 10<\/Id>\r\n DOWOD2<\/Name>\r\n ^[A-Za-z]{2}\\d{7}$<\/Formula>\r\n 1<\/Priority>\r\n<\/Reg>\r\n\r\n \r\n 11<\/Id>\r\n PolishCellularPhone<\/Name>\r\n ^\\+?[4][8]-?[0-9]{9}$<\/Formula>\r\n 0<\/Priority>\r\n<\/Reg>\r\n\r\n \r\n 12<\/Id>\r\n EMAIL<\/Name>\r\n ^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\\.[a-zA-Z0-9-.]+$<\/Formula>\r\n 0<\/Priority>\r\n<\/Reg>\r\n \r\n \r\n 13<\/Id>\r\n PHONE600<\/Name>\r\n ^60[0-9][0-9]{6}$<\/Formula>\r\n 0<\/Priority>\r\n<\/Reg>\r\n\r\n \r\n 14<\/Id>\r\n PHONE660<\/Name>\r\n ^66[0-5][0-9]{6}$<\/Formula>\r\n 0<\/Priority>\r\n<\/Reg>\r\n\r\n \r\n 15<\/Id>\r\n PHONE691<\/Name>\r\n ^69[1-8][0-9]{6}$<\/Formula>\r\n 0<\/Priority>\r\n<\/Reg>\r\n\r\n \r\n 16<\/Id>\r\n PHONE669<\/Name>\r\n ^66[7-9][0-9]{6}$<\/Formula>\r\n 0<\/Priority>\r\n<\/Reg>\r\n\r\n\r\n<\/Regex>\r\n\r\n\r\n<\/Konfiguracja>'\r\n\r\n\r\nexecute\u00a0\u00a0 dbo.spWyszukajDaneOsobowe \r\n@KonFiguracjaXML=@KonFiguracjaXML,\r\n@inDebug=1,\r\n@limitTabel =5000,\r\n@limitrekordow\u00a0 =10000,\r\n@limitsampli=2000,\r\n@limitprzetwarzania=5000,\r\n@tryb=0\r\n\r\n<\/pre>\n Celem dzia\u0142ania procedury powinien by\u0107 raport, kt\u00f3ry poka\u017ce w kt\u00f3rej kolumnie danej tabeli znajduj\u0105 si\u0119<\/p>\n
dane spe\u0142niaj\u0105ca zastosowane wy\u017cej kryteria<\/p>\n
Przyk\u0142adowy raport z bazy \u00a0AdventureWorks2012:<\/p>\n
\n\n
\n \n DataBaseName<\/pre>\n<\/td>\nSchemaName<\/td>\n TableName<\/td>\n NumberOfRows<\/td>\n ColumnName<\/td>\n DataValue<\/td>\n RuleName<\/td>\n<\/tr>\n \n \n AdventureWorks2012<\/pre>\n<\/td>\nPerson<\/td>\n EmailAddress<\/td>\n 19977<\/td>\n EmailAddress<\/td>\n miguel46@adventure-works.com<\/td>\n EMAIL:^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\\.[a-zA-Z0-9-.]+$<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n <\/p>\n
Krzysztof Pud\u0142owski<\/p>\n
Literatura:<\/p>\n
https:\/\/www.mssqltips.com\/sqlservertip\/1414\/run-same-command-on-all-sql-server-databases-without-cursors\/<\/a><\/p>\n
https:\/\/www.mssqltips.com\/sqlservertip\/1308\/retrieving-random-data-from-sql-server-with-tablesample\/<\/a><\/p>\n
https:\/\/technet.microsoft.com\/en-us\/library\/ff878119(v=sql.110).aspx<\/a><\/p>\n