Kim jest użytkownik? Niech odpowie baza: funkcja tożsamości w SQL Server

Autor: Adam Siemiątkowski, SoftwareStudio Group Sp. z o.o.

Funkcja tabelowa inline w SQL Server może zdecydować, czyje zamówienia zobaczy sesja, zanim zapytanie sięgnie do danych. Formularz wysyła numer kontrahenta, ale serwer traktuje go jak podpowiedź, a nie jak dowód. Opisujemy wzorzec z wdrożenia portalu awizacji Studio VSS.net w zakładzie produkcyjnym, w którym zapytania aplikacji leżą w bazie.

To przypadek jednego wdrożenia, a nie opis standardu produktu. Kod napisaliśmy od nowa na podstawie skryptu sprawdzonego na SQL Server 2022, z nazwami przykładowymi.

Funkcja tabelowa inline ustala tożsamość sesji, jeśli przyjmuje login konta i zawsze zwraca jeden wiersz z jedną kolumną. Wartość NULL oznacza pracownika, liczba oznacza kontrahenta przypisanego do konta, a -1 oznacza odmowę. Zapytania porównują z tym wynikiem dane, a parametr z formularza traktują jak podpowiedź.

Skąd serwer wie, czyje dane zwrócić

Parametr z formularza mówi tylko, o co prosi przeglądarka. Serwer może mu zaufać dopiero po porównaniu z tożsamością konta, którą ustala baza. W opisywanym wdrożeniu o zakresie danych decyduje więc login z sesji, a numer kontrahenta wpisany w formularzu jest podpowiedzią.

Z tego podziału wynika jedna reguła: login musi pochodzić z miejsca, którego użytkownik nie kontroluje. Funkcja go nie sprawdza, tylko przyjmuje jako argument. Od strony użytkownika podział widać w rolach i menu portalu awizacji: dostawca dostaje własne zamówienia, a dla konta pracownika zapytanie działa bez dodatkowego ograniczenia.

Gdy filtr trzeba dopisać do wielu zapytań zapisanych w repozytorium zapytań w bazie, pomaga technika z artykułu o zmianie zapytań zapisanych w bazie. Tu zostajemy przy samej funkcji.

Funkcja, która zawsze zwraca jeden wiersz

Funkcja przyjmuje login konta i zwraca zawsze dokładnie jeden wiersz z jedną kolumną liczbową o nazwie kontrahent. Jednowierszowość wynika z konstrukcji: jednowierszowy zbiór wartości jest złączany przez LEFT JOIN z tabelą kont. Działa to tylko wtedy, gdy login jest w tej tabeli unikalny, a w przykładzie jest kluczem głównym.

Identyfikator kontrahenta jest w kartotece kont tekstem, bo bywa wpisany ręcznie. Tabela przykładowa trzyma go więc w kolumnie varchar, a tabela zamówień w kolumnie bigint.

CREATE TABLE dbo.tabela_uzytkownikow
(
    login          varchar(50) NOT NULL PRIMARY KEY,
    aktywny        bit         NOT NULL,
    rola           varchar(30) NOT NULL,
    id_kontrahenta varchar(20) NULL
);
CREATE TABLE dbo.tabela_zamowien
(
    id_zamowienia  bigint IDENTITY(1,1) PRIMARY KEY,
    id_kontrahenta bigint      NOT NULL,
    numer          varchar(30) NOT NULL
);
INSERT dbo.tabela_uzytkownikow (login, aktywny, rola, id_kontrahenta) VALUES
    ('konto_pracownika',    1, 'pracownik', NULL),
    ('dostawca_a',          1, 'dostawca',  '101'),
    ('dostawca_bez_numeru', 1, 'dostawca',  NULL),
    ('dostawca_zly_numer',  1, 'dostawca',  '10x'),
    ('dostawca_wylaczony',  0, 'dostawca',  '101');
INSERT dbo.tabela_zamowien (id_kontrahenta, numer) VALUES
    (101, 'ZAK-26-0042'), (101, 'ZAK-26-0043'), (202, 'ZAK-26-0050');
GO

Przykład ilustracyjny, nazwy obiektów wymyślone.

Sama funkcja mieści się w jednym zapytaniu i powstaje poleceniem CREATE OR ALTER FUNCTION, które wymaga SQL Server 2016 SP1 lub nowszego.

CREATE OR ALTER FUNCTION dbo.tf_kontrahent_sesji (@login varchar(50))
RETURNS TABLE
AS RETURN
SELECT kontrahent = CASE
           WHEN u.login IS NULL
                THEN CAST(-1 AS bigint)
           WHEN NULLIF(LTRIM(RTRIM(u.id_kontrahenta)), '') IS NOT NULL
                THEN ISNULL(TRY_CONVERT(bigint, LTRIM(RTRIM(u.id_kontrahenta))),
                            CAST(-1 AS bigint))
           WHEN u.rola IN ('dostawca', 'przewoznik')
                THEN CAST(-1 AS bigint)
           ELSE CAST(NULL AS bigint)
       END
FROM (VALUES (1)) AS v(x)
LEFT JOIN dbo.tabela_uzytkownikow AS u
       ON u.login = @login AND u.aktywny = 1;
GO

Przykład ilustracyjny, nazwy obiektów wymyślone.

Wiersz FROM (VALUES (1)) AS v(x) tworzy zbiór z jednym wierszem, który LEFT JOIN zachowuje także wtedy, gdy konta nie znaleziono. Warunek u.aktywny = 1 stoi w ON, a nie w WHERE, więc konto wyłączone wygląda dla funkcji tak samo jak brak konta.

Funkcja jest tabelowa inline, a nie skalarna. Pozwala to wywołać ją w podzapytaniu, w EXISTS albo jako (SELECT ...) wewnątrz ISNULL, i zmienić zapytanie w miejscu. Według dokumentacji wdrożenia optymalizator rozwija ją do planu zapytania. Inną funkcję tego rodzaju, wywoływaną przez OUTER APPLY, pokazuje artykuł o TOP 1 bez ORDER BY.

Trzy znaczenia wyniku i odmowa w razie wątpliwości

Wynik ma trzy możliwe znaczenia i zawsze jedną kolumnę. NULL oznacza pracownika, czyli zachowanie zapytania bez zmian. Liczba oznacza identyfikator kontrahenta przypisanego do konta, a wartość -1 oznacza odmowę i nie pasuje do żadnego wiersza.

Gdy konta nie da się rozpoznać, funkcja zwraca odmowę, a nie pełny dostęp.

Odmowa pojawia się w czterech sytuacjach: gdy konto jest nieznane lub wyłączone, gdy identyfikator nie jest liczbą, gdy rola dostawcy nie ma identyfikatora i gdy login jest pusty. Identyfikator funkcja oczyszcza z odstępów i konwertuje przez TRY_CONVERT, więc wartość nieliczbowa, na przykład 10x, daje -1, a nie błąd.

Rolę dostawcy rozpoznaje w opisywanym wdrożeniu lista ról zapisana w funkcji. W przykładzie jest to IN ('dostawca', 'przewoznik'), a każdą nową rolę trzeba do tej listy dopisać. To wymóg wzorca, a nie wada: lista leży w jednym miejscu i tam się ją utrzymuje.

Filtr EXISTS w zapytaniach odczytu i zapisu

Zapytanie odczytu dopisuje do WHERE jeden warunek: istnieje wiersz funkcji, w którym kontrahent jest NULL albo równa się identyfikatorowi kontrahenta w rekordzie. Pracownik przechodzi przez IS NULL, a kontrahent widzi tylko wiersze ze swoim identyfikatorem. Wartość -1 nie pasuje do żadnego wiersza.

Ten sam warunek dopisuje się do UPDATE i DELETE, więc zmiana i usunięcie obejmują wyłącznie rekordy właściciela. Przy zapisie wartości z formularza obowiązuje druga reguła. Dla kontrahenta liczy się identyfikator z konta, a parametr z formularza jest użyty tylko wtedy, gdy funkcja zwróciła NULL.

DECLARE @login varchar(50) = 'dostawca_a';

SELECT z.id_zamowienia, z.numer
FROM dbo.tabela_zamowien AS z
WHERE EXISTS (SELECT 1
              FROM dbo.tf_kontrahent_sesji(@login) AS d
              WHERE d.kontrahent IS NULL OR d.kontrahent = z.id_kontrahenta);

DECLARE @z_formularza varchar(20) = '202';
SELECT ISNULL((SELECT kontrahent FROM dbo.tf_kontrahent_sesji(@login)),
              TRY_CONVERT(bigint, @z_formularza)) AS kontrahent_do_zapisu;
GO

Przykład ilustracyjny, nazwy obiektów wymyślone.

W prawdziwej aplikacji login podaje serwer, a nie formularz; tu ustawia go zmienna @login. Zapytanie z ISNULL zwraca 101 dla konta dostawca_a, choć formularz przysłał 202.

Parametry liczbowe wchodzą do takich zapytań jako TRY_CONVERT(bigint, '{N}') w cudzysłowie, a nie jako goły znacznik {N}. Wartość, której nie da się sparsować, daje NULL i puste wyniki, a nie błąd konwersji. Według dokumentacji wdrożenia w treści zapytania nie może być klamer poza znacznikiem {N}, bo serwer składa ją przez string.Format.

SQL Server ma też własne zabezpieczenia na poziomie wiersza z polityką bezpieczeństwa, opisane w artykule o Power BI nad bazą WMS. Wzorzec z funkcją działa na poziomie zapytań i polityki nie wymaga.

Strażnik na początku zapytania i procedury z loginem w parametrze

Zapytania wykonywalne, które zmieniają dane, zaczynają się od strażnika. Strażnik sprawdza właściciela rekordu, a przy odmowie wpisuje wiersz diagnostyczny do tabeli odmów i kończy zapytanie instrukcją RETURN. Poniższy szkic jest naszym uproszczeniem wzorca.

CREATE TABLE dbo.tabela_odmow
(
    id    int IDENTITY(1,1) PRIMARY KEY,
    tytul varchar(50)  NOT NULL,
    login varchar(50)  NOT NULL,
    kiedy datetime2(0) NOT NULL DEFAULT SYSDATETIME()
);
GO
DECLARE @login varchar(50) = 'dostawca_a';
DECLARE @id_zamowienia bigint = 3;

IF EXISTS (SELECT 1
           FROM dbo.tf_kontrahent_sesji(@login) AS d
           WHERE d.kontrahent IS NOT NULL
             AND NOT EXISTS (SELECT 1
                             FROM dbo.tabela_zamowien AS z
                             WHERE z.id_zamowienia = @id_zamowienia
                               AND z.id_kontrahenta = d.kontrahent))
BEGIN
    INSERT dbo.tabela_odmow (tytul, login) VALUES ('odmowa', @login);
    RETURN;
END;
GO

Szkic wzorca, napisany na potrzeby artykułu. Nazwy obiektów wymyślone.

Dla operacji, których konto kontrahenta w ogóle nie powinno wywoływać, strażnik jest krótszy: sprawdza tylko, czy wynik funkcji jest różny od NULL, i od razu kończy zapytanie.

Dwie procedury składowane dostają login jako parametr i ustalają z funkcji identyfikator kontrahenta. Pierwsza nadpisuje nim parametr kontrahenta przysłany z klienta, gdy wynik nie jest NULL. Druga nie ma takiego parametru i zawęża odczyt oraz zmianę pozycji do wierszy tego kontrahenta. Szkic procedury z odmową wygląda tak.

CREATE OR ALTER PROCEDURE dbo.zamowienie_dla_konta
    @login         varchar(50),
    @id_zamowienia bigint
AS
BEGIN
    SET NOCOUNT ON;
    IF NOT EXISTS (SELECT 1
                   FROM dbo.tabela_zamowien AS z
                   WHERE z.id_zamowienia = @id_zamowienia
                     AND EXISTS (SELECT 1
                                 FROM dbo.tf_kontrahent_sesji(@login) AS d
                                 WHERE d.kontrahent IS NULL
                                    OR d.kontrahent = z.id_kontrahenta))
    BEGIN
        SELECT CAST(0 AS bit) AS ok,
               CAST('Brak uprawnien do zamowienia' AS varchar(100)) AS komunikat;
        RETURN;
    END;
    SELECT CAST(1 AS bit) AS ok,
           CAST(z.numer AS varchar(100)) AS komunikat
    FROM dbo.tabela_zamowien AS z
    WHERE z.id_zamowienia = @id_zamowienia;
END;
GO

Szkic wzorca, napisany na potrzeby artykułu. Nazwy obiektów wymyślone.

Odmowa zwraca ten sam układ kolumn co powodzenie, czyli znacznik niepowodzenia i komunikat. W jednej z procedur komunikat mówi o braku uprawnień do zamówienia, a w drugiej cudza pozycja jest traktowana jak nieznaleziona. Właściciela zamówienia porównuje się po numerze zamówienia razem z kodem pozycji, gdy sam kod nie jest unikalny.

Granice wzorca i pułapki

Funkcja jest jedną warstwą kontroli obok pozostałych i nie zastępuje ich. Konto z rolą spoza listy w funkcji i bez identyfikatora kontrahenta dostaje NULL, więc jest traktowane jak pracownik. Dlatego listę ról trzeba aktualizować przy każdej nowej roli.

Skrypty z artykułu tworzą tabele bez sprawdzania istnienia, więc uruchamia się je na pustej bazie testowej. Login NULL nie pasuje do żadnego konta, więc funkcja zwraca -1, a nie błąd.

Jak sprawdzić, że nowe zapytanie przechodzi przez funkcję

Skrypt wdrożeniowy kończy się zapytaniem kontrolnym na kontach przykładowych. Poniższe robi to na sześciu kontach, w tym jednym nieistniejącym, i liczy widoczne zamówienia.

SELECT t.konto, d.kontrahent,
       (SELECT COUNT(*)
        FROM dbo.tabela_zamowien AS z
        WHERE EXISTS (SELECT 1
                      FROM dbo.tf_kontrahent_sesji(t.konto) AS d2
                      WHERE d2.kontrahent IS NULL OR d2.kontrahent = z.id_kontrahenta)) AS widoczne_zamowienia
FROM (VALUES ('konto_pracownika'), ('dostawca_a'), ('dostawca_bez_numeru'),
             ('dostawca_zly_numer'), ('dostawca_wylaczony'), ('nie_ma_takiego')) AS t(konto)
CROSS APPLY dbo.tf_kontrahent_sesji(t.konto) AS d;
GO

Przykład ilustracyjny, nazwy obiektów wymyślone.

Oczekiwany wynik zapytania kontrolnego na kontach przykładowych.

Cztery konta bez prawidłowego identyfikatora widzą zero zamówień, a nie błąd. Nowe zapytanie przechodzi przez funkcję wtedy, gdy te same konta dają w nim zakresy zgodne z tabelą.

Przykład: administrator opisuje zapytanie, które ma pokazać Dostawcy Testowemu jego zamówienia, na przykład ZAK-26-0042 i ZAK-26-0043. Numer kontrahenta ustala funkcja z loginu sesji, a zamówienia filtruje EXISTS. Konto z nieliczbowym numerem nie dostaje błędu konwersji, tylko pusty wynik.

Cztery pytania przed budową własnej funkcji tożsamości

Przed przeniesieniem wzorca do własnej bazy odpowiedz na cztery pytania.

• Login: skąd aplikacja bierze login, który trafia do funkcji, i w którym miejscu jest on ustalany.
• Odmowa: co zwraca funkcja, gdy konta nie ma albo jest nieaktywne, i gdy konto ma uszkodzony identyfikator, oraz czy każdy z tych przypadków kończy się odmową.
• Role: czy lista ról rozpoznawanych jako dostawca jest utrzymywana w jednym miejscu i kto ją aktualizuje przy nowej roli.
• Zakres: które zapytania i procedury muszą przejść przez funkcję, a które w ogóle nie powinny być dostępne dla konta kontrahenta.

Inne wzorce z tej samej bazy opisują artykuły o jednym mailu na zdarzenie i o ocenie atestu w T-SQL.