Jednym z ciekawszych problemów z jakimi można się spotkać tworząc aplikacje z kontami użytkowników to kwestia ich uprawnień. Myślę, że nie pomylę się zbytnio twierdząc, że recept na to jest mniej więcej tyle ilu jest programistów i pianych przez nich rozwiązań.

Jakich cech moglibyśmy spodziewać się po w miarę przyzwoitym systemie uprawnień? Myślę, że istotne mogą być następujące punkty:

• łatwość dodawania zasobów podlegających uprawnieniom
• łatwość odbierania i nadawania uprawnień
• zapamiętanie historii zmian

Z doświadczenia wiem, że najwięcej problemów może sprawiać ostatni punkt. Przeważnie jeśli już natrafiałem na tego typu zastosowanie to zawierało ono osobną tabelę, do której kopiowane były dane historyczne. W takiej sytuacji konieczne jest oczywiście pilnowanie tego aby takie wpisy podczas zmiany wykonywać (np. poprzez zapisanie stanu sprzed zmiany). To czy używa się do tego logiki aplikacji czy np. triggera w bazie (np. na operacji UPDATE) ma już mniejsze znaczenie.

Czy można to jednak zrealizować prościej? A gdyby nie trzeba było używać drugiej tabeli i pilnować tworzenia zapisów archiwalnych? Jeśli drogi czytelniku jesteś tego ciekawy to zapraszam do dalszej lektury 🙂

Zanim przejdziemy do prezentacji rozwiązania przygotujemy sobie mały plac zabaw. Naszymi zabawkami będą tylko trzy niewielkie tabelki i jeden widok. Zaczniemy od tabeli zasobów i użytkowników.

create table dbo.zasoby 
(
	idZasobu int identity (1,1) primary key clustered,
	nazwa nvarchar(50)
);

create table dbo.uzytkownicy
(
	idUzytkownika int identity (1,1) primary key clustered,
	username sysname
);

Jak widać w tych tabelach nie ma szczególnej filozofii. Teraz zajmijmy się miejscem, w którym będziemy zapisywać nasze uprawnienia.

create table dbo.uprawnienia 
(
	idUprawnienia int identity (1,1) primary key clustered,
	idZasobu int not null foreign key references dbo.zasoby(idZasobu),
	idUzytkownik int not null foreign key references dbo.uzytkownicy(idUzytkownika),
	uprawnienie bit default(0),
	dodano datetime2(7) default (getdate()),
	dodal sysname default ORIGINAL_LOGIN()
);

W przypadku tej tabeli zadziało się kilka rzeczy, które mogą wymagać wyjaśnienia. Po pierwsze widać, że mamy połączenie zasobu z użytkownikiem, przy czym oba obiekty muszą istnieć. Gdybyśmy chcieli, moglibyśmy ponakładać reguły, które automatycznie usunęłyby wpisy dotyczące uprawnień usuwanego zasobu bądź użytkownika.

Druga ciekawa rzecz, która może nie być oczywista to założenie pola identyfikatora jako klucza głównego. Dlaczego nie założymy po prostu klucza na parze identyfikującej użytkownika i zasób? Powód jest bardzo prosty - w tabeli para ta wcale nie będzie unikalna, ale do tego wrócimy za chwilę.

Kolejne ciekawe pole to uprawnienie, które przyjmuje stan binarny 0 lub 1. Jego rola raczej powinna być oczywista - jeden ze stanów może oznaczać nadanie uprawnień a drugi ich odebranie (wybór który będzie który jest kwestią tak na prawdę umowną).

Ostatnie dwa pola uzupełnianie automatycznie będą przechowywać nazwę użytkownika (login w bazie wydaje się tutaj naturalny, przy założeniu oczywiście, że każdy uprawniony użytkownik posiada swój własny profil) oraz czas utworzenia wpisu. Czy nam się to do czegoś może przydać? Powiedziałbym, że z punktu widzenia historii pola te będą wręcz kluczowe!

Zanim wyjaśnię zasadę działania dodajmy sobie trochę rekordów.

insert into dbo.zasoby (nazwa) values 
(N'Pomieszczenie gospodarcze'),
(N'Sala konferencyjna'),
(N'Warsztat'),
(N'Pracownia');

insert into dbo.uzytkownicy (username) values
(N'Administrator budynku'),
(N'Pracownik warsztatu'),
(N'Handlowiec');

Jak widać zamiast użytkownikami posłużyłem się tak na prawdę nazwami ról . Z jednej strony dlatego, że nie chciało mi się wymyślać imion i nazwisk. Poza tym tak na prawdę zamiast użytkowników możemy wprowadzić dowolny inny zasób, zarówno osobowy jak i nieosobowy. Moglibyśmy np. powiązać w podobny sposób miejsca parkingowe z konkretnymi pojazdami.

Skoro już mamy zbudowane tabele z zasobami to czas nadać trochę uprawnień. I tak, niech administrator budynku ma uprawnienia do wszystkich pomieszczeń, pracownik warsztatu do warsztatu i pracowni a handlowiec do pomieszczenia gospodarczego. Przyjmijmy, że nadanie uprawnień oznacza wpisanie 1 w polu uprawnienia.

insert into uprawnienia (idZasobu, idUzytkownik, uprawnienie) values
(1, 1, 1), (2, 1, 1), (3, 1, 1), (4, 1, 1),
(3, 2, 1), (4, 2, 1),
(1, 3, 1);

Ciekawostką, która rzuca się na pierwszy rzut oka jest zapewne fakt, że utworzyliśmy tylko obiekty uprawnień, które nadają uprawnienia. Może na pierwszy rzut oka wygląda to dziwie, ale zapewniam, że wcale takie nie jest.

Jak to działa w praktyce? Otóż jeśli chcemy nadać bądź odebrać uprawnienie któremukolwiek podmiotowi do jakiegokolwiek zasobu musimy zawsze dodać nowy wpis. W tym rozwiązaniu NIGDY nie wykonujemy operacji UPDATE. Jeśli chcemy odebrać uprawnienia to dodajemy w naszym przypadku wpis z 0 w polu uprawnienia, a jeśli nadać to wpisujemy tam 1.

No dobrze, ale w efekcie mamy w tabeli totalny miszmasz. Skąd teraz mamy się dowiedzieć kto posiada do czego AKTUALNE uprawnienia, no i co z brakiem uprawnień do zasobu? Przecież jeśli pobierzemy sobie listę uprawnień handlowca to tam nie ma żadnej informacji o uprawnieniu dla zasobu warsztatu. Jak można sobie z tym poradzić? Istnieje kilka sposób, ale ja zaproponuję takie, które wydaje mi się najłatwiejsze. Dodamy sobie widok.

create view view_uprawnienia as 
with zasoby_uzytkownicy as (
	-- łączymy każdego użytkownika z każdym zasobem
	select idZasobu, idUzytkownika
	from dbo.zasoby as z
	inner join dbo.uzytkownicy as u on 1=1
), uprawnienia_uzytkonikow as (
	select row_number() over (partition by z.idZasobu, z.idUzytkownika order by u.dodano desc) as wiersz, 
	z.idZasobu, 
	z.idUzytkownika,
	coalesce(u.uprawnienie, 0) as uprawnienie,
	u.dodal,
	u.dodano
	from zasoby_uzytkownicy as z
	left join uprawnienia as u on z.idZasobu = u.idZasobu and z.idUzytkownika = u.idUzytkownik
)
select *
from uprawnienia_uzytkonikow
where wiersz = 1

Jak widać tworzenie wyniku zwracanego przez widok odbywa się etapowo. W pierwszej kolejności w CTE zasoby_uzytkownicy w sprytny sposób łączymy każdy podmiot z każdym zasobem. W drugim etapie dołączamy do wyniku tablę uprawnień, przy czym jeśli uprawnienie nie istnieje, wtedy wpisujemy tam wartość uprawnienia wynoszącą 0 (realizujemy to dzięki funkcji coalesce). Najważniesze w CTE uprawnienia_uzytkonikow jest jednak wykorzystanie partycjonowania z sortowaniem według daty dodania. Dzięki temu zabiegowi dla każdej identycznej pary łączącej zasób z użytkownikiem zostanie wyliczony numer wiersza na podstawie kolejności określonej przez malejące sortowanie po dacie dodania. Co to oznacza? Otóż w wierszu z numerem 1 zawsze będzie znajdować się najbardziej aktualna wartość uprawnienia! Dodatkowo zwróconą mamy też informację odnośnie tego kto i kiedy nadał uprawnienia (bądź wartości NULL jeśli uprawnienia nie zostały nadane jawnie). Przyjmy się wynikowi następującego zapytania.

select u.username, z.nazwa,  upr.uprawnienie
from view_uprawnienia as upr
inner join dbo.zasoby as z on z.idZasobu = upr.idZasobu
inner join dbo.uzytkownicy as u on u.idUzytkownika = upr.idUzytkownika
order by u.idUzytkownika, z.idZasobu

W efekcie naszym oczom ukaże się taki widok.

Jak widać odzwierciedla on bardzo dokładnie nasze założenia co do uprawnień. Spróbujmy teraz wprowadzić kilka zmian. Odbierzmy administratorowi dostęp do sali konferencyjnej dodając je jednocześnie handlowcowi (szkoda aby akurat on nie miał tam dostępu 🙂 ).

insert into uprawnienia (idZasobu, idUzytkownik, uprawnienie) values
(2, 1, 0), (2, 3, 1);

Zobaczmy czy nasze wcześniejsze zapytanie wykorzystujące widok zwróci poprawne wyniki.

Zgodnie z oczekiwaniami widok zachował się poprawnie zwracając nam aktualne uprawnienia. Gdybyśmy chcieli moglibyśmy bez problemu wyświetlić sobie historię uprawnień dla dowolnego użytkownika, zasobu czy połączenia ich obu.

Czy przedstawione rozwiązanie jest najlepsze? W pewnych zastosowaniach zapewne tak, ale na pewno nie zawsze i nie wszędzie. Mam jednak nadzieję, że jest na tyle interesujące aby być może kogoś zainspirować choćby do wykorzystywania bardziej zaawansowanych narzędzi TSQL niż cztery podstawowe operacje 😉