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

    No Comments

    W poprzedniej części  przygotowaliśmy szkic  procedury składowanej implementującej prosty mechanizm wykrywania danych osobowych.

    To czego zabrakło, to wykorzystanie funkcji tabelarycznych  do wykrywania tych danych, które posiadają sumę kontrolną i same wyrażenia regularne mogą nie wystarczyć.

    Zajmijmy się   identyfikatorami PESEL, NIP, REGON. Zainteresowanych budową  takich identyfikatorów odsyłam do powszechnie dostępnych źródeł (m.in WIkipedia).

    Poniżej przedstawiono kod trzech prostych funkcji tabelarycznych napisanych w Transact SQL.

    PESEL

    NIP

    REGON

    Przykłady wywołania

    Wynik działania

     

    RAWPESEL PESEL isValid
    00000000001 00000000000 0

    RAWREGON REGON isValid
    000000001 000000000 0

    RAWNIP NIP isValid
    0000000001 0000000000 0

    Każda z tych funkcji jest zbudowana na podstawie tego samego schematu.
    Na poczatku pobieramy na wejściu danę do weryfikacji, następnie pobieramy cześć danych bez sumy kontrolnej i wyliczany na jej podstawie brakującą sumę kontrolną. Jeśli tak spreparowany identyfikator jest tożsami z wejściowym i dodatkowo składa się z samych cyfr, możemy uznać, ze mamy do czynienia z poprawnymi danymi. W ten sposón wyznaczana jest kolumna isValid.

    Jak można je zastosować ?

    Przydatne strony:

    http://walidator.testerzy.pl/

    http://zylla.wipos.p.lodz.pl/ut/nip-rego.html

    Categories: implementacja, RODO, t-sql

    Szyfrowanie danych dla RODO – droga na skróty

    No Comments

    SQL Server ma wbudowane dwie funkcje, które pozwalają na szyfrowanie i odszyfrowanie danych w sposób odwracalny.
    Czy to nie wystarczy do zapewnienia bezpieczeństwa i realizacji wymogów RODO ?

    Przykładowy kod:

    Wynik działania

    clearText :  Tajna wiadomosc do zaszyfrowania

    encrypted  : 0x010000007662E7BC0D6744BEE84DDD103891A2B21EEDE7DD99FEDD8E11D18F81FEA37028B5ED9060EC65FC9A19CD8ABDED32CA5D974D214D4D12109C495B2F2045F2B7F1A06A3D327EA6765C25EC8C8802E94A064ABCB41B91433E99

    decrypted : Tajna wiadomosc do zaszyfrowania

     

    Niestety w takim przypadku mamy do czynienia z pseudonimizacją. Dla tych, dla których to pojęcie jest nowe, proponuję na początek krótkie wprowadzenie

    http://blog.e-odo.pl/category/reforma-ochrony-danych-osobowych/anonimizacja-i-pseudonimizacja/

    Categories: implementacja, RODO, t-sql

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

    No Comments

    1. Dedykowany widok wykorzystujący funkcje pseudolosowe.

    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:

    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

    Generowanie EMAIL

    Generowanie dowodu osobistego

    Generowanie rachunku bankowego

    Generowanie imion

    Generowanie kodu pocztowego

    3. Generowanie danych testowych (TODO)

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

    Categories: implementacja, RODO, t-sql

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

    1 Comment

    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).

    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.

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

    Przykładowy generator numerów PESEL

    Generator polskich numerów NRB.

    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

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

    CryptUID Crypt1 Crypt2 Crypt3 Crypt4 CryptYear CryptMonth CryptDay
    F463175E-DD3C-479E-834F-BB850552FF56 189347 549814 23651 661922 10 5 11

    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.

    Categories: implementacja, RODO, t-sql

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

    3 komentarze

    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

    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.

     

    Sampling danych

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

    Przykładowy kod na demonstracyjnej bazie danych

    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

    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

    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:

    SchemaName TableName NumberOfRows ColumnName DataValue RuleName
    Person EmailAddress 19977 EmailAddress miguel46@adventure-works.com EMAIL:^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+$

     

    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

    Categories: RODO, t-sql