Uniwersalna procedura aktywacyjna Service Brokera

    No Comments

    Usługa Service Brokera, która pojawiła się wraz z wersja 2005 zadziwia mnie do tej pory.
    Od wielu lat producent niewiele w niej zmienia, co może świadczyć o dobrze przemyślanej architekturze.
    Z drugiej strony monitoring tej usługi dostarczony przez producenta pozostawia wiele do życzenia patrząc na to nawet z ergonomicznego puntku widzenia. Walka z wyłączającymi się kolejkami i komunikatami typu poison spędziła nie jeden sen z powiek. Jak sobie z tym najlepiej poradzić ?
    Zakładam, że czytelnik zna podstawową budowę Service Brokera i ma doświadczenie w jego stosowaniu w warunkach produkcyjnych. To o czym należy pamiętać, to świadomość, że wbudowano w serwer bazodanowy mechanizm, który potrafi obsłużyć za pomocą języka t-sql komunikację asynchroniczną bez pomocy zewnętrznych narzędzi. To niewątpliwie wielki plus funkcjonalny.
    Chciałbym skoncentrować się nad propozycją budowy szkicu uniwersalnej procedury aktywacyjnej, która obsługuje kolejkę targetową.
    Dokumentacja Microsoftu sprowadza się do budowy dedykowanej per kolejka procedury aktywacyjnej, gdzie spore fragmenty kodu są powtarzane i jest to nieco niezgodne z podejściem DRY.
    Przyjęte założenia:
    1. Przekazywane są jedynie komunikaty w formie XML
    2. Każdy typ komunikatu XML na podstawie jego root elementu będzie obsługiwany przez dedykowaną procedurę składowaną
    3. Każda z takich procedur ma standardową postać

    4. Wszystkie obiekty bazodanowe są obrębie jednej bazy.
    5. Wykorzystany zostanie dynamiczny sql.
    6. Procedura przyjmie minimalną liczbę argumentów i stanie się wewnętrzną procedurą aktywacyjną każdej z kolejek targetowych.

    Proponowany nagłówek:

    Powiązanie uniwersalnej procedury aktywacyjnej z przykładową kolejką

    Niech nasza kolejka nazywa się SampleTargetQueue.

    Przygotowujemy procedurę nakladkową definiowaną per kolejka

    Powiązanie kolejki docelowej z tak przygotowaną procedurą aktywacyjną.

    Szkic funkcjonalny uniwersalnej procedury aktywacyjnej

    1. Pobranie z kolejki pierwszego komunikatu XML

    2. Na podstawie root elementu XML ustalenie nazwy procedury składowanej odpowiedzialnej za logikę biznesową

    Proponowany kod transformacji :

    W tym przypadku wyznaczamy wartość nazwy procedury składowanej jako
    spInterfaceAccountV5getNumberGetAccountNumber

    3. Obsłużenie sytuacji wyjątkowych, komunikatów typu poison, XML-i niezgodnych ze schema parametru wejściowego procedury składowanej, wyłączonej kolejki, naruszenia więzów integralność.
    4. Wszystkie powyższe punkty powinny być realizowane w ramach jednej transakcji

    5. Zalogowane komunikaty błędów trafiają do jednej wspólnej tabeli.
    Propozycja takiej wspólnej tabeli

    Przykładowy fragment kodu uzupełniającego tabelę w przypadku wystąpienia błędu:

    Znaczenie pola status:

    wartość znaczenie czy do ponownego przetworzenia
    0 nowy wpis TAK
    1 wpis przetworzony z sukcesem NIE
    2 wpis przetworzony z błędem TAK
    3 nie wymaga przetworzenia NIE

     

    Proste zapytanie monitorujące błędne wpisy w tabeli

    Należy dodatkowo wprowadzić politykę retencji, gdzie po ustalonej liczbie dni, dane powinny być usuwane trwale, w szczegolności tam, gdzie status wskazuje na brak potrzeby ponownego przetworzenia.

     

     

    Literatura

    Configuring Service Broker for Asynchronous Processing

     

    Categories: implementacja, t-sql

    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

    Zanim błąd i szef zapukają do drzwi

    2 komentarze

    Czy zdażyło się wam odtwarzać po raz kolejny bazę danych po logicznym uszkodzeniu plików?

    Wlaściwie kiedy ostatnio sprawdzano ją dostępnymi narzędziami?
    Kiedy ostanio dokonano pełnej, różnicowej kopii bezpieczeństwa, jak często odkładane są logi transakcyjne?
    Czy baza ma poprawny model recovery ?
    Jeśli na chociaż jedno pytanie pojawiły się w głowie wątpliwości, to dobrze, że przed awarią…

     

    Pomocny skrypt

    Hipotetyczny rezultat powyższego zapytania

    database_name total_size_gb recovery_model_desc LastDBCCCHeckDB_RunDate
    XYZ 153.8079 SIMPLE 1900-01-01 00:00:00.000
    tempdb 58.6021 SIMPLE 1900-01-01 00:00:00.000
    HURT 49.2092 SIMPLE 1900-01-01 00:00:00.000
    ABC 46.6123 SIMPLE 1900-01-01 00:00:00.000
    ZZA 0.4893 FULL 1900-01-01 00:00:00.000
    model 0.1470 SIMPLE 1900-01-01 00:00:00.000
    master 0.0148 SIMPLE 1900-01-01 00:00:00.000
    msdb 5.8594 SIMPLE 2015-10-06 13:34:30.730

    Na co zwrócić uwagę ?

    Bazy systemowe (master, model,msdb,tempdb) mają ustawiony recovery model równy SIMPLE. Żadna z nich, poza msdb nie była nigdy sprawdzona narzędziem DBCC CheckDB ? To dobrze ?

    Jedyna baza w trybie FULL teź nigdy nie została sprawdzona.

    Do odważnych świat należy,ale nie do nieroztropnych.

    https://www.mssqltips.com/sqlservertip/4381/sql-server-dbcc-checkdb-overview/

    Categories: implementacja, migawka, 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