Row-Level Security - ograniczanie ilości rekordów w oparciu o tabelę z uprawnieniami

0

Tak się prezentuje mój system uprawnień:
2017-05-25_2016.png

Ludzie składają zamówienia i biznes się kręci. W tabelce orders jest bardzo ważne pole zip_code, po który możemy rozpoznać na jakim obszarze odbyła się usługa. areas to obszary, a area_codes to tabela z kodami pocztowymi.
2017-05-25_2030.png

Powstało jednak wymaganie tego typu:

  • obszary mogą być dowolnie definiowane, tzn. może być Mokotów osobno i Ochota osobno (do każdego z nich jest przypisane wiele kodów pocztowych), ale Mokotów i Ochota razem mogą tworzyć nowy obszar pod tytułem Warszawa - Strefa 1. Stwierdziłem, że to świetny moment na użycie tworu zwanego nested set.

  • musi powstać możliwość nadawania użytkownikom uprawnień, które definiują zamówienia z której strefy użytkownik będzie widział na liście zamówień (np. użytkownik z uprawnieniem do Warszawa Strefa 1 widzi tylko te zamówienia, które polegają kodom pocztowym w obrębie Mokotowa i Ochoty - patrz tabelka niżej)

Problem pierwszy postanowiłem rozwiązań nested setem, dzięki czemu będzie można sobie dowolnie komponować te obszary.
Struktura tabeli prezentuje się teraz następująco:
2017-05-25_2019.png

Pierwszy punkt z głowy, teraz czas na drugi. Ponieważ jestem leniwy i chciałem wykorzystać swój system uprawnień stwierdziłem, że można proces tworzenia nowych zautomatyzować.

(Chciałem też załatwić wyciąganie tych zamówień jakimś sprytnym zapytaniem i uniknąć ifologi oraz dodawania warunków where osobno dla każdego uprawnienia.)

Podczas tworzenia nowego obszaru (co robi jakiś tam super koordynator) tworzy się automatycznie uprawnienie w tabelce permissions pod tytułem strefa.warszawa.strefa.1 czy coś takiego. Jest ono wiązane z nową strefą (areas.permission_id).

TL:DR
Chce wyciągnąć tylko takie zamówienia z tabeli orders, gdzie zip_code znajduje się w kodach pocztowych przypisanych do strefy, do której użytkownik ma uprawnienie.

Niestety mimo mojej usilnej walki z zapytaniem ni jak nie mogę sobie dać rady, żeby pobrać tylko te zamówienia, do których obszarów użytkownik ma uprawnienie. Można to zrobić na pałę po kolei wszystko wyciągając, ale mam wrażenie, że jest jakiś inne sposób tylko go nie widzę.

Jeżeli ktoś miał na tyle cierpliwości, żeby to przeczytać to super i mam nadzieję, że Ty drogi czytelniku coś zaproponujesz :) Chociaż znając życie metoda gumowej kaczuszki zadziała i zaraz wymyślę coś genialnego.

Używam mariadb w wersji, w której niestety nie ma jeszcze WITH :(

@Edit
Dołączam strzępy SQL, może komuś coś pomogą:

-- pobieramy obszary, które są najwyżej w hierarchii
SELECT
  a1.id,
  a1.lft,
  a1.rgt
FROM areas a1
WHERE a1.permission_id IN (6, 7); -- uprawnienie do warszawa strefa - 1 i strefa 2

-- załóżmy, że powyższe zapytanie zwraca Warszawa Strefa Pierwsza i Strefa Druga
-- więc musimy dociągnąć strefy podrzędne, oczywiście uprawnienia się
-- dziedziczą, jak ktoś ma uprawnienie do warszawa strefa pierwsza
-- to ma tez do wszystich "niżej", czyli po prost wyciągamy wszystkie dzieciaki

SELECT *
FROM orders
WHERE zip IN (
  SELECT zip
  FROM areas a
    JOIN area_codes ac ON a.id = ac.area_id
  WHERE
    a.lft BETWEEN 2 AND 7 -- strefa 1
    OR a.lft BETWEEN 8 AND 13 -- strefa 2
    OR a.id IN (49, 50) -- id strefy 1 i strefy 2
)
1

tu http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ masz jak wylistować wszystkie elementy drzewa razem z głębokością uprawnienia (ważne). Skoro pisałeś sam uprawnienia to teraz napisz skrypt, który Ci zwróci wszystkie uprawnienia dla zadanego usera do stref. Jak to będziesz miał to łączysz po permission_id pierwsze zapytanie z drugim (drugie jako left join bo zapewne rekordów z pierwszego będzie więcej). No i teraz trzeba odrzucić te rekordy, które nie mają uprawnienia lub ich rodzic nie ma uprawnienia. Jakbyś dał DDL tabel i przykładowe dane (im ich więcej tym lepiej) to by było znacznie prościej, najlepiej na http://sqlfiddle.com/

EDIT miał być komentarz ale się nie zmieścił

wiesz jak dasz DDLa i dane to można będzie coś wymyślić bo bez danych to ciężko stwierdzić jak masz uprawnienia zapisywane. Np. jeśli user należy do dwóch ról (rolaA i rolaB), sam user nie ma uprawnienia do uprawX ale rolaA ma a rolaB nie ma to czy user ma to uprawnienie czy nie? Co jest ważniejsze uprawnienie usera czy roli, czy może być tak, że uprawnienie usera znosi uprawnienie roli albo jedna rola znosi uprawnienie drugiej roli. Czy user może mieć kilka ról przypisanych. Same uprawnienia to dość rozległy temat i bez konkretnych założeń jak działają u Ciebie komuś obcemu będzie trudno napisać zapytanie, które zwróci uprawnienia dla jakiejś gałęzi (BTW czy uprawnienia masz hierarchiczne?) i wszystkich jej dzieci (tu np. dla wszystkich elementów odnoszących się do stref)

0

W sumie o uprawnienia najmniej się tu rozchodzi, bo w zasadzie założyłem (i zapomniałem napisać), że są dane. Działają one tak, że uprawnienia użytkownika, to suma wszystkich uprawnień przypisanych do ról użytkownika + tych które są bezpośrednio powiązane z użytkownikiem. No i jeszcze zachodzi tam szalone dziedziczenie, bo role mają poziomy, np. admin ma poziom 10, to ma też uprawnienia z każdej roli poniżej poziomu 10... np. zwykłego usera, koordynatora itd., ale to mało istotne w tym kontekscie.

W tym wypadku, tak jak wspomniałem, są one dane, ponieważ jak widać ich wyciąganie to nie zwykły select, więc podczas logowania do systemu są cache'owane, żeby nie męczyć bazy co każdy request. Wykombinowałem takie zapytanie:

EXPLAIN SELECT *
FROM orders
WHERE zip IN (
  SELECT zip
  FROM areas a1 -- żeby zwalidować uprawnienia
    JOIN areas a2 ON a2.lft BETWEEN a1.lft AND a1.rgt -- self join wyciągający właściwe rekordy + ich dzieci, zakladamy ze jak ktos ma uprawnienie do nadrzednej strefy, to ma tez do wszystkich podrzednych
    JOIN area_codes ac ON a2.id = ac.area_id 
  WHERE a1.permission_id IN (6, 7, 14) -- tego nie musimy wyciagac, bo juz znamy
);

po wstępnych testach muszę powiedzieć, że dostaje to co chce, zobaczymy co dalej.

1 użytkowników online, w tym zalogowanych: 0, gości: 1