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

    Pyspark – podstawowe transformacje i akcje

    No Comments

    Transformacje tworzą nowe obiekty RDD , również na podstawie innego obiektu RDD.
    Silnik Apache Spark potrafi przeanalizować sekwencję transformacji.
    Akcje wykonują na obiektach RDD działania, których wynikiem nie jest RDD i działania te wymuszają wykorzystanie definicji RDD wraz z sekwencją transformacji.

    Proste przykłady w pySparku.

    Out[2]:

    Out[3]: pyspark.context.SparkContext

    Out[4]:[1, 2, 3, 4, 5, 6]
    Out[4]:6

    Out[5]:[1, 2, 3, 4, 5, 6]

    Out[5]:[2, 4, 6, 8, 10, 12]

    Out[6]:[1, 2, 3, 4, 5, 6]

    Out[6]:[2, 4, 6]

    Out[7]: [1, 2, 3, 4, 5, 6]

    Out[7]:[[1, 2], [2, 4], [3, 6], [4, 8], [5, 10], [6, 12]]

    Out[8]:[1, 2, 3, 4, 5, 6]

    Out[8]:[1, 3, 2, 4, 3, 5, 4, 6, 5, 7]

    Out[9]:[1, 2, 3, 4, 5, 6]

    Out[9]:720

    Out[10]:[2, 4, 6]

    Out[10]:[1, 3, 5]

    Out[10]:[2, 4, 6, 1, 3, 5]

    Out[11]:[1, 2, 3, 4, 5, 6, 3, 4, 5]

    Out[11]:[2, 4, 6, 1, 3, 5]

    Out[12]:[2, 4, 6]

    Out[12]:[1, 3, 5]

    Out[12]:[2, 4, 6, 1, 3, 5]

    Out[12]:[]

    Out[12]:[4, 2, 6]

    Out[12]:[(2, 1), (2, 3), (2, 5), (4, 1), (4, 3), (6, 1), (6, 3), (4, 5), (6, 5)]

    Categories: Apache, Python, Spark

    Apache Spark – pierwsze początki

    2 komentarze

    Poradnik:

    Jak uruchomić na komputerze z MS Windows lokalną instancję Apache Sparka pracującą na jednym nodzie z gotową obsługą skryptów w Pythonie (pyspark) i przykładowymi zbiorami danych.

    Na podstawie materiałów ze strony
    http://semantica.cs.lth.se/pyspark/#/pyspark-vm

    Dla tych, którzy na co dzień korzystają ze stacji roboczej MS Windows.

    Potrzebne oprogramowanie
    a) Virtual Box ( wirtualizator)
    https://www.virtualbox.org/wiki/Downloads
    Instalujemy najwyższą wersję , jaką aktualnie obsługuje Vagrant
    GUI VirtualBoxa nie będzie wogóle wykorzystywane.

    b) Vagrant (nakładka na wirtualizator)

    https://www.vagrantup.com/downloads.html

    Instalujemy obraz

    To chwilę trwa, cierpliwości, obraz  ponad 1 GB objętości.

    Sprawdzamy zainstalowane obrazy maszyn wirtualnych.

    Sprawdzamy status maszyn

    Uruchamiamy maszynę wirtualną

    Jak wygląda plik VagrantFile, warto na niego spojrzeć, w szczególności na numery portów, które są forwardowane.

    Uwagi, które mogą zmniejszyć frustrację:

    Jeśli zainstalujemy zbyt nową wersję VirtualBoxa Vagrant jej nie wykryje, a co więcej będzie komunikować nas o braku HyperV.

    Co ciekawe na komputerach z windows 7 pro  instalacja tego typu oprogramowania jest możliwa, ale tej wersji nie testowałem,

    pozostajemy przy oprogramowaniu Virtualbox.

    Dla dociekliwych:

    https://www.altkomakademia.pl/baza-wiedzy/qna/discussion/2763/instalacja-hyper-v-manager-w-windows-7-
    Na stacjach z Windows 7 standardowo zainstalowany jest powershell w versji 2.0. Wykonanie polecenia Vagrant up nie kończy się. Należy zaktualizować framework np. do wersji 4.0.

    Wersja PowerShella przed:

    Po instalacji ze strony Microsoftu

    https://www.microsoft.com/en-us/download/details.aspx?id=40855

    Wersja powershella po:

    W wersji VirtualBox (5.1) domyślnie nie ma dostępu do sieci zewnętrznej od środka maszyny, przez co nie można zaktulizować jej oprogramowania. Pomaga poprawa w pliku konfiguracyjnym Vagrantfile

    Należy dodać w pliku Vagranfile dodatkowe linie kodu

    Pierwsze kroki

    1. Uruchomienie skryptu Pythona w notatniku Jupiter

    Uruchamiany stronę http://localhost:8081

    Jako silnik wybieramy pyspark

    W pierwszej komórce wpisujemy

    Wyjście
    Out[1]:

    W drugiej komórce

    Wyjście:
    Out[2]: pyspark.context.SparkContext

    Przykładowy wygląd notatnika

    Pyspark

    2. Aktualizujemy dostępne pliki z danymi

    Uruchamiamy stronę http://localhost:8082
    Naciskamy przycisk “Update all” i czekamy cierpliwie na aktualizację, jeden z plików ma ponad 2GB danych.
    Po aktualizacji wygląda to tak

    Datasets

     

     

    Dostęp do UI Sparka jest  pod adresem

    http://localhost:4040

    localhost_4040

    Dostęp przez SSH

     

    Dostęp do maszyny przez SSH jest na porcie 2222 interfejsu lokalnego.

    ssh_session_pyspark

     

    Z pliku private_key należy wygenerować klucz prywatny , który będzie mógł być użyty przez PuttySSH.

    U mnie plik znajduje się w podkatalogu, w którym zainstalowano  przez Vagranta  wirtualną maszynę:

    tak wygenerowany klucz prywatny należy wskazać na zakladce Connection/SSH/Auth.

    ssh_auth_pyspark

    Po uruchomieniu połączenia i akceptacji klucza logujemy  się na użytkownika vagrant  z pustym hasłem.

    ssh_auth_loginvagrant

     

    Po zakończeniu pracy wystarczy wyłączyć maszynę wirtualną

    Categories: Apache, Big Data, Python, Spark