Audytowanie dostępu
Śledź, kto może wyświetlać poszczególne dane
Audytowanie dostępu to bezpłatna lekcja SQL Academy na CoddyKit. To lekcja 4 z 4. Możesz przeczytać całą lekcję poniżej za darmo — a potem ćwiczyć ją interaktywnie w przeglądarce z wbudowanym edytorem kodu i tutorem AI dostępnym 24/7. To część ścieżki edukacyjnej SQL Academy, a Twój postęp synchronizuje się między webem a aplikacją CoddyKit. Kurs SQL Academy zawiera 4 lekcji w sumie.
Dlaczego audytować dostęp
Wiedza o tym, kto uzyskał dostęp do jakich danych i kiedy, jest fundamentem bezpieczeństwa baz danych. Audyt tworzy wiarygodny ślad zdarzeń, dzięki któremu można wykrywać nieautoryzowany dostęp, badać incydenty i spełniać wymagania zgodności, takie jak GDPR, HIPAA lub SOC 2.
W tej lekcji pokazano, jak projektować tabele audytu, automatycznie rejestrować zdarzenia dostępu za pomocą triggerów, korzystać z wbudowanych funkcji logowania PostgreSQL oraz odpytywać ślad audytowy, aby odpowiedzieć na pytanie: kto może zobaczyć jakie dane?
Projektowanie tabeli dziennika audytu
Pierwszym krokiem jest dedykowana tabela, która rejestruje każde istotne zdarzenie. Dobry dziennik audytu przechowuje nazwę tabeli, typ operacji, stare i nowe wartości, użytkownika wykonującego działanie oraz dokładny znacznik czasu.
Poniższy przykład tworzy uniwersalną tabelę audit_log z użyciem kolumn JSONB do przechowywania migawek wierszy — jest ona wystarczająco elastyczna, aby obsłużyć dowolną tabelę bez zmian schematu.
CREATE TABLE audit_log (
id BIGSERIAL PRIMARY KEY,
event_time TIMESTAMPTZ NOT NULL DEFAULT now(),
db_user TEXT NOT NULL DEFAULT current_user,
app_user TEXT,
table_name TEXT NOT NULL,
operation TEXT NOT NULL CHECK (operation IN ('INSERT','UPDATE','DELETE','SELECT')),
row_id BIGINT,
old_data JSONB,
new_data JSONB
);Rejestrowanie bieżącego użytkownika
PostgreSQL udostępnia kilka wbudowanych funkcji pozwalających ustalić, kto wykonuje zapytanie. current_user zwraca nazwę roli obowiązującej po dowolnym SET ROLE. session_user zawsze zwraca pierwotną rolę logowania, niezależnie od przełączania ról.
W aplikacjach, które korzystają z jednej współdzielonej roli DB, ale przekazują użytkownika na poziomie aplikacji za pomocą SET LOCAL app.current_user, można odczytać to ustawienie za pomocą current_setting().
-- Who is the database user right now?
SELECT current_user,
session_user;
-- Read an application-level user injected by the app layer
SELECT current_setting('app.current_user', true) AS app_user;Pisanie funkcji triggera audytu
Funkcja triggera to najbardziej niezawodny sposób rejestrowania zdarzeń zmian danych, ponieważ uruchamia się automatycznie — żaden kod aplikacji nie może jej ominąć. Poniższa funkcja rejestruje każde polecenie INSERT, UPDATE i DELETE w tabeli, do której została przypisana, zapisując stare i nowe wartości wiersza jako JSONB.
Zwróć uwagę na użycie TG_TABLE_NAME (tabeli, która uruchomiła trigger) oraz row_to_json() do konwertowania wartości wierszy na format możliwy do zapisania.
CREATE OR REPLACE FUNCTION fn_audit_changes()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
INSERT INTO audit_log (
db_user,
app_user,
table_name,
operation,
row_id,
old_data,
new_data
) VALUES (
current_user,
current_setting('app.current_user', true),
TG_TABLE_NAME,
TG_OP,
COALESCE(NEW.id, OLD.id),
CASE WHEN TG_OP = 'INSERT' THEN NULL ELSE row_to_json(OLD)::JSONB END,
CASE WHEN TG_OP = 'DELETE' THEN NULL ELSE row_to_json(NEW)::JSONB END
);
RETURN NULL;
END;
$$;Dołączanie triggera do tabeli
Po utworzeniu funkcji triggera przypisuje się ją do każdej tabeli, którą ma obejmować audyt, za pomocą instrukcji CREATE TRIGGER. Użycie AFTER gwarantuje, że dane zostały faktycznie zapisane, zanim zostanie utworzony wpis w dzienniku. Klauzula FOR EACH ROW uruchamia trigger raz dla każdego zmodyfikowanego wiersza.
W tym przykładzie trigger zastosowano do hipotetycznej tabeli patients, aby automatycznie rejestrować każde polecenie INSERT, UPDATE i DELETE.
CREATE TRIGGER trg_audit_patients
AFTER INSERT OR UPDATE OR DELETE
ON patients
FOR EACH ROW
EXECUTE FUNCTION fn_audit_changes();Audytowanie zapytań SELECT
Triggery zmian danych przechwytują tylko operacje zapisu. Aby audytować odczyt danych, potrzebne jest inne podejście. Jedną z możliwości jest trigger na poziomie instrukcji AFTER SELECT (obsługiwany w PostgreSQL 14+ w określonych kontekstach). Częściej stosuje się jawne rejestrowanie odczytów wewnątrz funkcji lub widoku, który opakowuje poufną tabelę.
Poniższy przykład wykorzystuje funkcję opakowującą poufną tabelę i rejestrującą każdy odczyt przed zwróceniem wyników.
CREATE OR REPLACE FUNCTION get_patient_record(p_id INT)
RETURNS SETOF patients
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
-- Log the read access
INSERT INTO audit_log (db_user, app_user, table_name, operation, row_id)
VALUES (
current_user,
current_setting('app.current_user', true),
'patients',
'SELECT',
p_id
);
RETURN QUERY
SELECT * FROM patients WHERE id = p_id;
END;
$$;Wbudowane rejestrowanie w PostgreSQL
Plik postgresql.conf PostgreSQL oferuje zaawansowane rejestrowanie po stronie serwera, które nie wymaga kodu aplikacji. Ustawienie log_min_duration_statement rejestruje każde zapytanie przekraczające określony próg. Ustawienia log_connections i log_disconnections rejestrują, kto się loguje i wylogowuje.
Poniższe zapytanie wykorzystuje systemowy widok pg_stat_activity do wyświetlenia aktualnie aktywnych sesji — jest to lekka forma monitorowania dostępu w czasie rzeczywistym.
-- See who is currently connected and what they are running
SELECT pid,
usename AS db_user,
application_name,
client_addr,
state,
query_start,
LEFT(query, 80) AS current_query
FROM pg_stat_activity
WHERE datname = current_database()
ORDER BY query_start DESC;Wykonywanie zapytań do dziennika audytowego
Dziennik audytowy jest wartościowy tylko wtedy, gdy można go skutecznie odpytywać. Typowe pytania to: który użytkownik jako ostatni uzyskał dostęp do rekordu, co zmieniało się w wierszu w czasie oraz ile odczytów danych wrażliwych wykonano w ciągu ostatnich 24 godzin.
Poniższe zapytanie wyszukuje wszystkich użytkowników, którzy uzyskali dostęp do konkretnego rekordu pacjenta, sortując wyniki od najnowszego dostępu.
SELECT event_time,
db_user,
app_user,
operation,
old_data,
new_data
FROM audit_log
WHERE table_name = 'patients'
AND row_id = 42
ORDER BY event_time DESC
LIMIT 20;Wykrywanie podejrzanych wzorców dostępu
Po zebraniu danych audytowych można pisać zapytania wykrywające anomalie. Na przykład użytkownik, który nagle odczytuje znacznie więcej wierszy niż zwykle, lub ten sam wrażliwy rekord jest odczytywany wielokrotnie w krótkim przedziale czasu, może wskazywać na próbę eksfiltracji danych.
Poniższe zapytanie zlicza zdarzenia SELECT dla każdego użytkownika aplikacji z ostatniej godziny i wyróżnia osoby, które odczytały więcej niż 100 wierszy.
SELECT app_user,
COUNT(*) AS records_accessed
FROM audit_log
WHERE operation = 'SELECT'
AND event_time >= now() - INTERVAL '1 hour'
GROUP BY app_user
HAVING COUNT(*) > 100
ORDER BY records_accessed DESC;Ochrona samego dziennika audytowego
Dziennik audytowy, który można modyfikować, nie jest wiarygodny. Należy go zabezpieczyć tak, aby zwykli użytkownicy i role aplikacji nie mogły usuwać ani aktualizować wierszy. Najbezpieczniej przyznać roli aplikacji wyłącznie uprawnienie INSERT, a SELECT zarezerwować dla dedykowanej roli audytora.
Niezmienność można również wymusić za pomocą wyzwalacza, który zgłasza wyjątek, jeśli ktokolwiek spróbuje zaktualizować lub usunąć wiersz dziennika audytowego.
-- Only the app role may insert; nobody may update or delete
REVOKE ALL ON audit_log FROM PUBLIC;
GRANT INSERT ON audit_log TO app_role;
GRANT SELECT ON audit_log TO auditor_role;
-- Trigger to block any tampering
CREATE OR REPLACE FUNCTION fn_protect_audit()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
RAISE EXCEPTION 'audit_log rows are immutable';
RETURN NULL;
END;
$$;
CREATE TRIGGER trg_protect_audit
BEFORE UPDATE OR DELETE ON audit_log
FOR EACH ROW EXECUTE FUNCTION fn_protect_audit();Audytowanie decyzji zasad RLS
Gdy aktywne jest zabezpieczenie na poziomie wierszy (Row-Level Security, RLS), PostgreSQL po cichu ukrywa wiersze zamiast zgłaszać błędy. Ustalenie, czy użytkownik próbował odczytać wiersz, którego nie mógł zobaczyć, jest przez to trudne. Jedną z metod jest dodanie zezwalającej zasady, która zawsze wstawia rekord audytowy, zanim restrykcyjna zasada odfiltruje wiersze.
Poniższe zapytanie pokazuje, jak sprawdzić, które zasady RLS istnieją dla tabeli i jakich ról dotyczą.
-- View all RLS policies on the patients table
SELECT polname AS policy_name,
polcmd AS command,
polroles::TEXT AS applies_to,
polqual::TEXT AS using_expression,
polwithcheck::TEXT AS with_check_expression
FROM pg_policy
WHERE polrelid = 'patients'::REGCLASS
ORDER BY polname;Sprawdzenie wiedzy
Sprawdź swoją wiedzę na temat audytowania dostępu w SQL.
Podsumowanie lekcji
W tej lekcji poznali Państwo sposób tworzenia kompletnego systemu audytowania dostępu w PostgreSQL:
- Tabela dziennika audytowego — tabela oparta na JSONB, rejestrująca, kto, co i kiedy zrobił.
- Funkcja wyzwalacza — automatycznie rejestruje zdarzenia INSERT, UPDATE i DELETE dla każdej dołączonej tabeli, korzystając z
TG_TABLE_NAME,row_to_json()icurrent_user. - Audytowanie odczytów — opakowanie wrażliwych tabel w funkcje, które rejestrują zdarzenia SELECT przed zwróceniem danych.
- Wbudowane monitorowanie —
pg_stat_activitypokazuje aktywne sesje, a rejestrowanie po stronie serwera przechwytuje zapytania bez zmian w kodzie. - Wykrywanie anomalii — zapytania agregujące dziennik audytowy mogą wskazywać nietypową liczbę operacji dostępu.
- Niezmienność — odebranie uprawnień UPDATE/DELETE do tabeli dziennika audytowego i dodanie blokującego wyzwalacza zapobiega manipulacjom.
Dobrze zaprojektowana ścieżka audytowa jest najbardziej niezawodnym narzędziem do ustalenia, kto uzyskał dostęp do czego, i stanowi podstawę każdej analizy zgodności lub dochodzenia dotyczącego bezpieczeństwa.
Ucz się SQL dzięki korepetycjom AI — za darmo
Pisz i uruchamiaj kod w przeglądarce, otrzymuj natychmiastową pomoc od korepetytora AI dostępnego 24/7 i kontynuuj naukę w sieci lub w aplikacji.
- Kursy
- 46
- Lekcje
- 183
Często zadawane pytania
Czy lekcja „Audytowanie dostępu” jest bezpłatna?
Tak — pełny tekst „Audytowanie dostępu” jest dostępny za darmo tutaj w sieci. Aby ćwiczyć ją interaktywnie (wbudowany edytor kodu i tutor AI dostępny 24/7) i odblokować resztę kursu SQL Academy, przejdź na CoddyKit PRO. Kurs SQL Academy zawiera 4 lekcji w sumie.
Co nauczysz się w „Audytowanie dostępu”?
Śledź, kto może wyświetlać poszczególne dane Ćwiczysz SQL Academy z praktycznym kodem, który uruchamiasz bezpośrednio w przeglądarce, a tutor AI dostępny 24/7 odpowiada na Twoje pytania podczas pracy nad lekcją.
Czy potrzebuję doświadczenia, aby zacząć SQL Academy?
Nie wymagamy żadnego doświadczenia. SQL Academy w CoddyKit jest strukturyzowany dla początkujących i zaawansowanych użytkowników, więc możesz zacząć tutaj lub od początku i uczyć się w swoim tempie. To lekcja 4 z 4.
Ile czasu zajmuje lekcja „Audytowanie dostępu”?
Większość lekcji CoddyKit trwa około 5–10 minut. Każda lekcja to mały, interaktywny krok, dzięki czemu robisz systematyczne postępy i zawsze wracasz dokładnie do tego samego miejsca — na webie i w aplikacji.
Czy mogę pisać i uruchamiać kod w tej lekcji SQL Academy?
Tak. Każda lekcja SQL Academy zawiera wbudowany edytor kodu, więc piszesz i uruchamiasz prawdziwy kod bezpośrednio w przeglądarce i od razu otrzymujesz sprzężenie zwrotne od AI — bez konfiguracji na komputerze.
Wszystkie lekcje w tym kursie
- Role i uprawnienia
- Zasady bezpieczeństwa na poziomie wierszy
- Uprawnienia na poziomie kolumn
- Audytowanie dostępu