- Zasada: raport nie czyta tabel produkcyjnych, tylko model raportowy, kontem z uprawnieniem SELECT na schemacie raportowym.
- Powód: ciężkie zapytania analityczne konkurowałyby z pracą terminali na tej samej bazie.
- Odcięcie: system podstawia identyfikator kontrahenta z konta użytkownika, a w modelu raportowym robi to polityka zabezpieczeń na poziomie wiersza.
- Ograniczenie: odcięcie po koncie wymaga, żeby baza widziała tożsamość odbiorcy raportu, więc nie działa przy imporcie przez jedno konto bramy.
Opisujemy architekturę i zabezpieczenia warstwy raportowej nad bazą systemu magazynowego z wdrożenia Studio WMS.net u operatora logistycznego, który obsługuje wielu właścicieli towaru. Artykuł dotyczy zasad, a nie konkretnego raportu. Kod napisaliśmy od nowa, z nazwami przykładowymi. Uruchomiliśmy go na SQL Server 2022 Express, a wyniki testów zgadzają się z podanymi liczbami.
Ogólne zasady raportowania z bazy SQL Server opisuje strona o raportowaniu danych z systemu, a Reporting Services i raporty doraźne omawia tekst o raportowaniu z bazy SQL Server. Dystrybucję gotowych zestawień opisuje strona o raportowaniu na Microsoft SQL Server. Tutaj zajmujemy się dwoma pytaniami z wdrożeń dla wielu właścicieli towaru: skąd raport ma brać dane i jak zadbać, żeby klient operatora widział tylko swoje.
Dlaczego raport nie czyta tabel produkcyjnych
Zasada architektury jest jedna i dotyczy każdego raportu. Między systemem a raportem stoi osobna warstwa danych, czyli model raportowy czytany przez bramę danych kontem tylko do odczytu. To rekomendacja architektoniczna autora, a nie opis istniejącego wdrożenia raportów.
Raport nigdy nie czyta tabel produkcyjnych: czyta model raportowy kontem tylko do odczytu.
Powody są dwa. Baza obsługuje wiele magazynów, a terminale pracują na niej na bieżąco, więc ciężkie zapytania analityczne konkurowałyby z pracą terminali o te same zasoby. Raport podłączony wprost do tabel produkcyjnych wymagałby też poprawek przy każdej zmianie struktury w systemie, bo każda zmiana nazwy lub typu kolumny łamie jego zapytania.
Podział ma konsekwencję dla innych artykułów tej witryny. Rozliczenie opisane w tekście o paletodobie w T-SQL wykonuje system na bazie produkcyjnej. Raport powinien czytać jego zatwierdzone wyniki z modelu raportowego, a nie z tabel, na których pracuje procedura.
Osobny model raportowy i konto tylko do odczytu
Model raportowy to osobna baza albo osobny schemat, zasilany cyklicznie danymi z bazy produkcyjnej. Zawiera tylko kolumny potrzebne do raportów. Dokument magazynowy niesie trzy znaczniki czasu: założenia, bufora i zatwierdzenia, więc zasilanie przyrostowe może iść po czasie zatwierdzenia. Sposób zasilania wybiera autor modelu.
Konto raportowe ma uprawnienie wyłącznie do odczytu modelu raportowego, czyli SELECT na schemacie raportowym, i nie ma dostępu do schematu produkcyjnego. Brama danych łączy usługę chmurową z bazą w sieci firmy i używa właśnie tego konta. W tabeli zebraliśmy cztery warstwy i to, kto się z nimi łączy.
| Warstwa | Kto się łączy | Uprawnienia |
|---|---|---|
| Baza produkcyjna | system i terminale | odczyt i zapis |
| Model raportowy | konto raportowe | tylko odczyt |
| Brama danych | usługa raportowa kontem raportowym | odczyt |
| Zbiór danych klienta | konto lub rola klienta | odczyt po filtrze |
Przykład architektury. Nazwy kont w kodzie poniżej są wymyślone.
Przykład zakłada wariant z jedną bazą i dwoma schematami: schemat o nazwie raport dla modelu raportowego oraz dbo_prod dla danych produkcyjnych. Konto raportowe jest kontem Windows, bo takiego konta zwykle używa brama. SELECT na schemacie raportowym daje mu odczyt, a jawne DENY na schemacie produkcyjnym odbiera dostęp także wtedy, gdy ktoś później nada szersze uprawnienia. Przy modelu w osobnej bazie wystarczy, że konto nie ma użytkownika w bazie produkcyjnej.
CREATE SCHEMA raport;
GO
CREATE LOGIN [DOMENA\konto_raportowe] FROM WINDOWS;
CREATE USER konto_raportowe FOR LOGIN [DOMENA\konto_raportowe];
GRANT SELECT ON SCHEMA::raport TO konto_raportowe;
DENY SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo_prod TO konto_raportowe;
GO
Przykład ilustracyjny. Nazwy konta i schematów są wymyślone.
W teście odczyt z modelu raportowego przez to konto się udaje, a odczyt ze schematu produkcyjnego kończy się komunikatem o odmowie uprawnienia SELECT.
Model zasila procedura, która wstawia zatwierdzone ruchy z czasem zatwierdzenia późniejszym niż ostatni zapisany znacznik. Górna granica leży minutę przed bieżącym czasem, żeby zatwierdzenia mające się dopiero zakończyć nie przepadły. To decyzja autora przykładu. Procedura nie czyta tabeli raportowej, bo po włączeniu polityki zabezpieczeń konto spoza roli i spoza tabeli przypisań widzi tam zero wierszy. Znacznik leży więc w osobnej tabeli bez polityki.
CREATE TABLE raport.fakt_ruchy (
id_ruchu bigint NOT NULL PRIMARY KEY,
czas_zapisu datetime2(0) NOT NULL,
czas_zatwierdzenia datetime2(0) NULL,
login_operatora varchar(50) NOT NULL,
magazyn varchar(5) NOT NULL,
oddzial varchar(5) NOT NULL,
id_wlasciciela bigint NOT NULL,
rodzaj_dokumentu varchar(3) NOT NULL,
kierunek char(1) NOT NULL,
numer_palety varchar(20) NOT NULL,
ilosc decimal(13,3) NOT NULL,
status char(1) NOT NULL
);
CREATE TABLE raport.znacznik_zasilania (
ostatni_czas datetime2(0) NOT NULL
);
INSERT INTO raport.znacznik_zasilania (ostatni_czas) VALUES ('2000-01-01');
GO
CREATE PROCEDURE raport.odswiez_fakt_ruchy
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
DECLARE @od datetime2(0) = (SELECT ostatni_czas FROM raport.znacznik_zasilania);
DECLARE @do datetime2(0) = DATEADD(MINUTE, -1, SYSDATETIME());
BEGIN TRANSACTION;
INSERT INTO raport.fakt_ruchy
(id_ruchu, czas_zapisu, czas_zatwierdzenia, login_operatora, magazyn, oddzial,
id_wlasciciela, rodzaj_dokumentu, kierunek, numer_palety, ilosc, status)
SELECT s.id_ruchu, s.czas_zapisu, s.czas_zatwierdzenia, s.login_operatora, s.magazyn, s.oddzial,
s.id_wlasciciela, s.rodzaj_dokumentu, s.kierunek, s.numer_palety, s.ilosc, s.status
FROM dbo_prod.ruchy AS s
WHERE s.status = 'Z'
AND s.czas_zatwierdzenia > @od
AND s.czas_zatwierdzenia <= @do;
UPDATE raport.znacznik_zasilania SET ostatni_czas = @do;
COMMIT TRANSACTION;
END;
GO
Przykład ilustracyjny. Źródłem jest tabela ruchów w schemacie produkcyjnym o tych samych kolumnach.
Jak wybrać wariant uruchomienia raportów
Raporty można uruchamiać na trzy sposoby. O wyborze decydują licencje oraz miejsce przechowywania danych. Osobne pytanie dotyczy swobody analizy: czy odbiorca ma budować własne zestawienia, czy czytać gotowe.
| Wariant | Kiedy się sprawdza |
|---|---|
| Power BI w chmurze z bramą danych | najszybsze uruchomienie, licencje po stronie firmy, która korzysta z raportów |
| Power BI Report Server na serwerze firmy | dane nie mogą opuścić jej infrastruktury |
| Raporty wewnątrz systemu | zestawienia konfigurowane bez programowania, z filtrem, grupowaniem, wykresem i eksportem do arkusza, oraz wydruki dokumentów na serwerze raportów |
Zestawienie trzech wariantów. Wybór zależy od licencji i miejsca przechowywania danych.
Trzeci wariant istnieje w opisywanym wdrożeniu jako część systemu. Dwa pierwsze to wybór architektury, a nie opis wdrożenia. Każdy z wariantów korzysta z tej samej zasady: raport czyta model raportowy, a nie bazę produkcyjną.
Jak system odcina dane klienta w zestawieniach
W opisywanym wdrożeniu konto użytkownika ma pole z identyfikatorem kontrahenta. Serwer udostępnia je zapytaniom jako zmienną zastrzeżoną, podstawianą przy wykonaniu. Zestawienia klienta mają warunek, że kontrahent równa się zmiennej konta, wpisany w treść zapytania.
Klient operatora nie wybiera kontrahenta i nie widzi pola wyboru. Nie zmieni go też parametrem adresu, bo warunek wykonuje się po stronie serwera. W opisywanym wdrożeniu taki warunek ma kilkadziesiąt zestawień klienta. Układ ról i modeli współpracy opisuje artykuł o portalu klienta WMS.
Odcięcie obejmuje pięć grup danych: dokumenty przyjęć i wydań (w buforze i zatwierdzone), zlecenia, asortyment, stan szczegółowy oraz odbiorców. W rejestrach z takim warunkiem klient operatora nie widzi danych innych właścicieli towaru. Niektóre rejestry zawężają dane dodatkowo do oddziału lub magazynu, a rejestr bez filtra magazynu pokazuje klientowi jego dokumenty ze wszystkich magazynów. Granicę odcięcia trzeba więc zdecydować świadomie: sam kontrahent albo kontrahent z magazynem.
Jak ten sam podział wykonać w T-SQL na poziomie wiersza
W warstwie raportowej odpowiednikiem warunku z zapytania rejestru jest zabezpieczenie na poziomie wiersza w SQL Server. Składa się z trzech elementów: tabeli przypisań, funkcji predykatu i polityki zabezpieczeń. Polityka działa na każde zapytanie do chronionej tabeli.
Tabela przypisań wiąże nazwę konta z identyfikatorem właściciela towaru. Klucz obejmuje obie kolumny, więc jedno konto może mieć kilku kontrahentów. Rola zarządcza reprezentuje konta, które widzą wszystko.
CREATE ROLE rola_zarzad_raportow;
CREATE TABLE raport.dostep_klienta (
nazwa_konta sysname NOT NULL,
id_wlasciciela bigint NOT NULL,
CONSTRAINT pk_dostep_klienta PRIMARY KEY (nazwa_konta, id_wlasciciela)
);
INSERT INTO raport.dostep_klienta (nazwa_konta, id_wlasciciela)
VALUES (N'konto_klient_a', 101),
(N'konto_klient_b', 102);
GO
Przykład ilustracyjny. Konta klientów A i B mają przypisanych właścicieli 101 i 102.
Funkcja predykatu jest funkcją tabelaryczną z klauzulą RETURNS TABLE i opcją WITH SCHEMABINDING. Zwraca jeden wiersz tylko wtedy, gdy bieżący użytkownik należy do roli zarządczej (sprawdza to funkcja IS_MEMBER) albo tabela przypisań wiąże jego nazwę, zwracaną przez USER_NAME, z wartością kolumny właściciela. Polityka nakłada ją na tabelę raportową. Powinna to być tabela albo widok z opcją SCHEMABINDING, bo zwykły widok polityki nie przyjmie.
CREATE FUNCTION raport.filtr_klienta (@id_wlasciciela bigint)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
SELECT 1 AS dostep
WHERE IS_MEMBER(N'rola_zarzad_raportow') = 1
OR EXISTS (SELECT 1
FROM raport.dostep_klienta AS d
WHERE d.nazwa_konta = USER_NAME()
AND d.id_wlasciciela = @id_wlasciciela);
GO
CREATE SECURITY POLICY raport.polityka_klienta
ADD FILTER PREDICATE raport.filtr_klienta(id_wlasciciela) ON raport.fakt_ruchy
WITH (STATE = ON);
GO
Przykład ilustracyjny. Predykat filtrujący dla tabeli raportowej, polityka włączona.
Test wykonujemy przez EXECUTE AS USER. Najpierw wstawiamy pięć ruchów: trzy dla właściciela 101 i dwa dla właściciela 102. Potem nadajemy kontom klientów oraz roli zarządczej uprawnienie SELECT na tabeli raportowej. Bez niego EXECUTE AS kończy się odmową dostępu, a do tabeli przypisań ani do funkcji predykatu konta klientów uprawnień nie potrzebują.
INSERT INTO raport.fakt_ruchy
(id_ruchu, czas_zapisu, login_operatora, magazyn, oddzial, id_wlasciciela,
rodzaj_dokumentu, kierunek, numer_palety, ilosc, status)
VALUES
(1, '2026-03-02 08:15:10', 'operator1', 'M1', 'O1', 101, 'PZ', 'P', 'PAL-000201', 100, 'Z'),
(2, '2026-03-02 08:40:42', 'operator1', 'M1', 'O1', 101, 'PZ', 'P', 'PAL-000202', 50, 'Z'),
(3, '2026-03-03 10:05:00', 'operator2', 'M1', 'O1', 101, 'WZ', 'R', 'PAL-000201', -100, 'Z'),
(4, '2026-03-04 11:20:00', 'operator1', 'M2', 'O2', 102, 'PZ', 'P', 'PAL-000203', 80, 'Z'),
(5, '2026-03-05 12:00:00', 'operator2', 'M2', 'O2', 102, 'WZ', 'R', 'PAL-000203', -80, 'Z');
CREATE USER konto_klient_a WITHOUT LOGIN;
CREATE USER konto_klient_b WITHOUT LOGIN;
CREATE USER konto_zarzad WITHOUT LOGIN;
ALTER ROLE rola_zarzad_raportow ADD MEMBER konto_zarzad;
GRANT SELECT ON raport.fakt_ruchy
TO konto_klient_a, konto_klient_b, rola_zarzad_raportow;
GO
Przykład ilustracyjny. Użytkownicy bez loginu wystarczą do testu przez EXECUTE AS.
EXECUTE AS USER = 'konto_klient_a';
SELECT COUNT(*) AS wiersze FROM raport.fakt_ruchy;
REVERT;
EXECUTE AS USER = 'konto_klient_b';
SELECT COUNT(*) AS wiersze FROM raport.fakt_ruchy;
REVERT;
EXECUTE AS USER = 'konto_zarzad';
SELECT COUNT(*) AS wiersze FROM raport.fakt_ruchy;
REVERT;
EXECUTE AS USER = 'konto_raportowe';
SELECT COUNT(*) AS wiersze FROM raport.fakt_ruchy;
REVERT;
Przykład ilustracyjny. Każde zapytanie zwraca liczbę wierszy widoczną dla danego konta.
Konto klienta A widzi trzy wiersze, a konto klienta B dwa. Konto z rolą zarządczą widzi wszystkie pięć. Konto raportowe, które nie ma przypisania ani roli, widzi zero wierszy, bo polityka domyślnie nie przepuszcza. Tak samo zachowuje się właściciel bazy odpytujący tabelę bez EXECUTE AS: nie jest ani w roli, ani w tabeli przypisań, więc dostaje zero wierszy.
Filtr dotyczy odczytu. Próba wstawienia wiersza przez konto raportowe kończy się odmową, bo konto nie ma uprawnienia INSERT.
EXECUTE AS USER = 'konto_raportowe';
INSERT INTO raport.fakt_ruchy
(id_ruchu, czas_zapisu, login_operatora, magazyn, oddzial, id_wlasciciela,
rodzaj_dokumentu, kierunek, numer_palety, ilosc, status)
VALUES (99, SYSDATETIME(), 'operator1', 'M1', 'O1', 101, 'PZ', 'P', 'PAL-000299', 1, 'Z');
REVERT;
Przykład ilustracyjny. Wynik to komunikat o odmowie uprawnienia INSERT.
Gdyby konto miało zapisywać do tabeli, filtr nie wystarczy. Drugą warstwą jest predykat blokujący, który zatrzymuje wstawienie wiersza z cudzym kontrahentem. W teście konto klienta A z nadanym uprawnieniem INSERT dostaje komunikat o konflikcie z predykatem blokującym, gdy próbuje zapisać ruch właściciela 102.
ALTER SECURITY POLICY raport.polityka_klienta
ADD BLOCK PREDICATE raport.filtr_klienta(id_wlasciciela)
ON raport.fakt_ruchy AFTER INSERT;
GO
Przykład ilustracyjny. Predykat blokujący po wstawieniu.
Wariant z kontekstem sesji ma sens wtedy, gdy jedno konto usługowe obsługuje wielu klientów przez aplikację pośrednią. Aplikacja ustawia identyfikator kontrahenta na początku sesji, a funkcja predykatu porównuje go z kolumną. Sam Power BI nie ustawia kontekstu sesji, więc to wariant dla aplikacji pośredniej, a nie dla bezpośredniego raportu. Opcja read_only blokuje późniejszą zmianę wartości w tej sesji, a bez ustawionego klucza funkcja nie zwraca żadnego wiersza.
CREATE FUNCTION raport.filtr_sesji (@id_wlasciciela bigint)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
SELECT 1 AS dostep
WHERE CAST(SESSION_CONTEXT(N'id_wlasciciela') AS bigint) = @id_wlasciciela;
GO
ALTER SECURITY POLICY raport.polityka_klienta
ALTER FILTER PREDICATE raport.filtr_sesji(id_wlasciciela) ON raport.fakt_ruchy;
GO
EXEC sp_set_session_context @key = N'id_wlasciciela', @value = 101, @read_only = 1;
SELECT COUNT(*) AS wiersze FROM raport.fakt_ruchy;
Przykład ilustracyjny. Predykat czyta SESSION_CONTEXT. Aplikacja pośrednia ustawia klucz, a odczyt widzi wtedy trzy wiersze.
Wariant z predykatem po koncie ma ograniczenie. Baza musi widzieć tożsamość odbiorcy raportu, a gdy zbiór danych jest importowany przez jedno konto bramy, widzi tylko to konto. Według dokumentacji Microsoft o logowaniu jednokrotnym przy trybie DirectQuery z włączonym logowaniem jednokrotnym zapytania wykonują się z tożsamością użytkownika Power BI, a odświeżanie z tym logowaniem używa tożsamości właściciela zbioru danych. Dla SQL Server logowanie jednokrotne opiera się na uwierzytelnianiu Kerberos. Odcięcie po USER_NAME() działa więc tylko w trybie bezpośrednim z logowaniem jednokrotnym. Przy imporcie rozdział klientów wymaga osobnego zbioru danych na klienta albo ról w modelu raportu.
Na jakich kolumnach opiera się raportowanie ruchów
Ruch magazynowy ma w opisywanym modelu danych siedem kolumn, na których opiera się raportowanie: login zapisującego, czas zapisu (znacznik bazy), magazyn, oddział, właściciela towaru, rodzaj dokumentu i kierunek, czyli przychód albo rozchód. Właściciel towaru jest na każdej pozycji dokumentu, więc służy jako klucz odcięcia. Miary typu operacje na login i godzinę oraz ruchy na właściciela i magazyn liczą się właśnie z tych kolumn. O tym, co z nich da się policzyć od ręki, a czego brakuje, pisze artykuł o wskaźnikach magazynu z danych WMS.
SELECT f.login_operatora,
CAST(f.czas_zapisu AS date) AS dzien,
DATEPART(HOUR, f.czas_zapisu) AS godzina,
COUNT(*) AS operacje
FROM raport.fakt_ruchy AS f
GROUP BY f.login_operatora, CAST(f.czas_zapisu AS date), DATEPART(HOUR, f.czas_zapisu);
Przykład ilustracyjny. Zapytanie pokazuje sposób liczenia, a nie dane klienta.
Login oznacza konkretnego pracownika tylko wtedy, gdy terminale pracują na kontach imiennych. Status wiersza, jeden z trzech (bufor, zatwierdzony albo usunięty), oraz jedną z dwóch dat, dokumentu albo na fakturę, trzeba w raporcie wybrać jawnie. Rozchód w buforze już pomniejsza dostępny stan, więc raport stanów i raport zatwierdzonych ruchów odpowiadają na różne pytania. Model z przykładu zawiera tylko ruchy zatwierdzone, więc raport dostępnego stanu wymagałby zasilania także rozchodami z bufora.
Układ, w którym wielu właścicieli towaru korzysta z jednego systemu, opisuje artykuł o WMS dla operatora 3PL.
Pytania przed budową warstwy raportowej
Przed zaprojektowaniem modelu raportowego odpowiedz na cztery pytania.
- Granica: czy klient ma widzieć dane jednego kontrahenta ze wszystkich magazynów, czy tylko z wybranego.
- Tożsamość: czy baza ma widzieć konto odbiorcy raportu, czy tylko konto bramy.
- Zasilanie: jak często odświeżać model i po którym znaczniku czasu.
- Status: czy raport ma pokazywać bufor i rozchody rezerwujące stan, czy wyłącznie ruchy zatwierdzone.