[cz. III] Always Encrypted od środka ? SQL Server Security

Ponownie kontynuuje tematykę Always Encrypted. W tym wpisie chce pokazać, co dokładniej jest wykonywane w momencie kliknięcia Finish kreatora który omawiałem ostatnio. Myślę, że można z tego wyciągnąć całkiem ciekawe wnioski.

Zaczynamy, od stworzenia małej tabelki z niewielką liczbą wierszy, dajmy na to 100000 pozycji.

Następnie tworzymy nową sesje Extented Events w celu przechwycenia poleceń wykonywanych przez kreator Always Column… Gdy jesteśmy gotowi, możemy przejść kreator w celu zaszyfowania danych w kolumnie, natomiast po wszystkim zatrzymać sesje Extended Events.

Poniższym zapytaniem „wydłubałem” to co interesujące.

Mając to co nas interesuję, postaram się omówić wyniki.

Krok po kroku

Pierwsze polecenie tworzy nową tabele z prefixem tmp_ms_xx i sufixem liczbowym. Jest to tabela „tymczasowa” do której trafią dane z tabeli źródłowej.  Od razu można zauważyć, że przeniesione zostały parametry z kreatora dotyczące kolumny, klucza szyfrującego oraz typu szyfrowania. Oprócz tego zostaje dodana nowa kolumna tceGuidCol o typie UNIQUEIDENTIFIER wraz z ograniczeniem unikalności.

Następnie dodawana jest identyczna kolumna tceGuidCol do tabeli źródłowej oraz wykonywany jest update w celu wypełnienia jej unikalnymi wartościami. Używana jest w tym celu funkcji NEWID().

Następnie wykonywane są poniższe SELECTy. Prawdopodobnie do celów kontrolnych. Przypuszczam tak chociażby po użyciu procedury sp_describe_parameter_encryption która zwraca informacje o kluczach szyfrujących oraz parametrach szyfrowania.

Wykonanie poniższego polecenia pozwala na wprowadzanie jawnie wartości do kolumn z właściwością IDENTITY.

Kolejną instrukcją jest dynamiczny SQL, który wylicza dla danych z każdej kolumny średnią liczbę bajtów. Nie dowiedziałem się jeszcze, jaki jest tego cel, ale jak będę coś widział na pewno się tym podzielę.

Następnie wykonywany jest masowy insert danych z bazy źródłowej do docelowej. W tym miejscu następuje wykorzystanie SqlBulkCopy.

Dla potwierdzenia, że jest to ta operacja, napisałem kawałek skryptu w PowerShell i przechwyciłem zdarzenia po stronie SQL Server.

Bez specjalnego porównywania widać że, polecenia są prawie identyczne.

Końcówka to już „tylko” usunięcie tabeli źródłowej. Zmiana nazwy tabeli tymczasowej oraz usunięcie constrainta oraz kolumny tceGuidCol1.

Podsumowanie

Kluczowe jest tworzenie tabeli „tymczasowej”. Jeśli nasza tabela źródłowa jest bardzo duża i mając na uwadze, że ta sama tabela po szyfrowaniu potrafi zająć 2-4 razy więcej, musimy mieć sporo wolnej przestrzeni dyskowej. Efektem ubocznym też będzie nieużyta przestrzeń w pliku danych.

Drugą sprawą jest to, że tabel źródłowa w pewnym jest usuwana więc niewyobrażam sobie podejść do tego tematu bez kopii zapasowej i dość sporego okna wdrożeniowego.

Z pasją poświęcam czas na zdobywanie wiedzy w zakresie szeroko rozumianej Data Platform. Zachwycony językiem skryptowym Windows PowerShell. Swoją wiedzę, doświadczenia i spostrzeżenia opisuję na blogu.

Leave a Reply

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *