W serii wpisów opisujących migracje zmian nadszedł czas aby zająć się elementami związanymi z uprawnieniami. Często w swojej karierze miałem okazję zetknąć się z sytuacją przyznawania uprawnień wprost użytkownikom. O ile oczywiście nie ma w tym nic złego to osobiście bardzo nie lubię takiego podejścia. Zdecydowanie bardziej wolę dzielić funkcjonalności według ról i wiązać z nimi zarówno uprawnienia jak i użytkowników. Dzięki temu schemat uprawnień pozostaje niewrażliwy na jakiekolwiek zmiany użytkowników.

Ponieważ więc role lubię o wiele bardziej niż użytkowników (ale to zabrzmiało 🙂 ) dlatego to właśnie im poświęcę niniejszy wpis. Do tych drugich wrócimy innym razem (żeby nie było, że jestem faktycznie uprzedzony – użytkownicy również dostaną cały oddzielny wpis 😉 ).

Zanim zaczniemy musimy sobie nieco przygotować warsztat pracy. Wykorzystam tabele utworzone w poprzednim wpisie (dla wygody powtórzę jednak tamten kod tworzący tabele, odpowiednio dla każdej z baz) oraz nowe procedury utworzone na szybko (aby móc podpiąć uprawnienia). Dodajmy sobie również kilka ról i powiązanych z nimi uprawnień.

 
use [poligon];

create table [dbo].[uzytkownicy]
(
	[idUzytkownik] int identity (1, 1) primary key clustered,
	[imie] nvarchar(100) not null,
	[nazwisko] nvarchar(200) not null,
	[email] varchar(200) not null
);

go

create procedure [dbo].[getUzytkownicy] as
begin
	set nocount on;

	select * from [uzytkownicy];
end

go

create role [db_uzytkownicy_reeder] authorization [db_owner];
create role [db_uzytkownicy_writer] authorization [db_owner];
create application role [approle_uzytkownicy_reader] with default_schema = [dbo], password = 'AnyPassword1';
create application role [approle_uzytkownicy_writer] with default_schema = [dbo_owner], password = 'AnyPassword2';

grant select on [dbo].[uzytkownicy] to [db_uzytkownicy_reeder];
grant execute on [dbo].[getUzytkownicy] to [db_uzytkownicy_reeder];
grant select, insert, update on [dbo].[uzytkownicy] to [db_uzytkownicy_writer];

grant select on [dbo].[uzytkownicy] to [approle_uzytkownicy_reader];
grant execute on [dbo].[getUzytkownicy] to [approle_uzytkownicy_reader];
grant select, insert, delete on [dbo].[uzytkownicy] to [approle_uzytkownicy_writer];

go

use [poligon_dev];

create table [dbo].[uzytkownicy]
(
	[idUzytkownik] int identity (1, 1) primary key clustered,
	[imie] nvarchar(100) not null,
	[nazwisko] nvarchar(200) not null,
	[email] varchar(200) not null,
	[data_utworzenia] datetime2 default (getdate())
);

go

create procedure [dbo].[getUzytkownicy] as
begin
	set nocount on;

	select * from [uzytkownicy];
end

go

create procedure [dbo].[getMaileUzytkownikow] as
begin
	set nocount on;

	select [email] from [uzytkownicy] ;
end

go

create role [db_uzytkownicy_reader] authorization [db_owner];
create role [db_uzytkownicy_writer] authorization [db_owner];
create application role [approle_uzytkownicy_reader] with default_schema = [dbo], password = 'AnyPassword1';
create application role [approle_uzytkownicy_writer] with default_schema = [dbo], password = 'AnyPassword2';

grant select on [dbo].[uzytkownicy] to [db_uzytkownicy_reader];
grant execute on [dbo].[getUzytkownicy] to [db_uzytkownicy_reader];
grant execute on [dbo].[getMaileUzytkownikow] to [db_uzytkownicy_reader];
grant select, insert, update, delete on [dbo].[uzytkownicy] to [db_uzytkownicy_writer];

grant select on [dbo].[uzytkownicy] to [approle_uzytkownicy_reader];
grant execute on [dbo].[getUzytkownicy] to [approle_uzytkownicy_reader];
grant execute on [dbo].[getMaileUzytkownikow] to [approle_uzytkownicy_reader];
grant select, insert, update, delete on [dbo].[uzytkownicy] to [approle_uzytkownicy_writer];

Być może niektórzy pierwszy raz spotykają się z rolami aplikacyjnymi. Tutaj nie będziemy się nad nimi specjalnie rozwodzić ale chciałbym potraktować temat ról bardziej kompleksowo i dlatego postanowiłem je uwzględnić (szczególnie, że z punktu widzenia poruszanego dzisiaj tematu nie wprowadzą żadnych rewelacji). Gdyby jednak ktoś chciał się dowiedzieć o nich czegoś więcej to można zajrzeć choćby tutaj.

Napiszę jeszcze kilka słów komentarza o tym co powyższy skrypt właściwie zrobił. W każdej z baz utworzyliśmy po dwie role bazodanowe i dwie aplikacyjne oraz dołączyliśmy do nich uprawnienia. W bazie produkcyjnej (tej bez DEV w nazwie) celowo popełniłem literówkę w nazwie jednej z ról (jest db_uzytkownicy_reeder zamiast db_uzytkownicy_reader jak w drugiej bazie). Celowo również „zapomniałem” o kilku uprawnieniach w dwóch rolach. Dzięki temu wynik porównania w obu bazach powinien dać nam jakieś widoczne rezultaty (zgodnie z sędziwym żartem branżowym, że jeśli poprosimy doświadczonego programistę o znalezienie żyrafy w Afryce to ten na wszelki wypadek dostarczy tam wcześniej chociaż jedną). 

Przy badaniu różnic w rolach musimy uwzględnić co najmniej dwa aspekty – obecność ról w obu bazach oraz powiązane z nimi uprawnienia. Powiązaniem ról z użytkownikami zajmiemy się innym razem (co ja poradzę, że użytkownicy są o wiele ciekawsi kiedy powiąże się ich z rolami 🙂 ).

Najprostszym sposobem na pozyskanie informacji o rolach w bazie danych jest pobranie zawartości widoku systemowego sys.database_principals. Wynik zapytania (polecam wykonać samodzielnie) zwróci nam między innymi nazwy ról, ich typ, domyślną nazwę schematu, informacje o właścicielu oraz bardzo przydatną informację o tym czy rola jest wbudowana. Jeśli interesuje kogoś pełen opis wyniku to można zapoznać się z nim między innymi na tej stronie.

Poniżej przygotowałem zapytanie w bazie poligon, które moim zdaniem powinno być wystarczające do pozyskania najważniejszych informacji. Oczywiście moglibyśmy porównywać wszystko (jeśli ktoś będzie czuł taką potrzebę to modyfikacja skryptów nie będzie specjalnie trudna) ale osobiście zwykle nie czułem potrzeby analizowania różnic w zakresie większym niż nazwy i uprawnienia. Wynika to zapewne z faktu, że zwykle przygotowuję sobie skrypty tworzące potrzebne obiekty i relacje a to narzędzie pokazuje mi tylko czy muszę je uruchomić i w jakim zakresie.

 
select [dp].[name],
	   [dp].[principal_id],
	   [dp].[type],
	   [dp].[type_desc],
	   [dp].[default_schema_name],
	   [dp].[owning_principal_id],
	   [dpo].[name] as [owning_principal_name]
from [poligon].[sys].[database_principals] as [dp]
left join [poligon].[sys].[database_principals] as [dpo] on [dpo].[principal_id] = [dp].[owning_principal_id]
where [dp].[is_fixed_role] = 0
  and [dp].[type] != 'S'
  and [dp].[name] != 'public'

Pola type oraz type_desc zawierają w sumie tę samą informację, ale dla lepszej czytelności postanowiłem zachować na tę chwilę oba. W wyniku tego zapytania otrzymamy mniej więcej taki widok (zależy od tego czy w międzyczasie nie dodaliśmy sobie innych ról w tej bazie).

Skoro mamy już informacje odnośnie ról to teraz musimy zainteresować się jeszcze powiązaniem ich z uprawnieniami. W tym celu musimy powiązać role uzyskane za pomocą powyższego zapytania z widokami sys.database_permissions, który zawiera informacje o uprawnieniach oraz sys.objects, z którego odczytamy jakiego obiektu uprawnienia dotyczą. Kompletne zapytanie może wyglądać jak poniżej.

 
select [dp].[name],
	   [dp].[type],
	   [dp].[type_desc],
	   [ob].[name],
	   [ob].[type],
	   [ob].[type_desc],
	   [dpe].[type],
	   [dpe].[permission_name],
	   [dpe].[state],
	   [dpe].[state_desc]
from [poligon].[sys].[database_principals] as [dp]
left join [poligon].[sys].[database_permissions] as [dpe] on [dpe].[grantee_principal_id] = [dp].[principal_id]
left join [poligon].[sys].[objects] as [ob] on [ob].[object_id] = [dpe].[major_id]
where [dp].[is_fixed_role] = 0
  and [dp].[type] != 'S'
  and [dp].[name] != 'public'

Pozwoliłem sobie i tym razem pokazywać zarówno typy w wersji prostej jak i opisowej.

Udało nam się pozyskać kolekcję każdej z ról wraz z przypisanymi do niej uprawnieniami do wszystkich obiektów. Warte komentarza jest moim zdaniem jeszcze zwrócenie uwagi na użycie złączeń typu left join zamiast inner join w zapytaniu. Dlaczego właśnie tak? Ponieważ może nam się pojawić rola, która nie ma żadnych uprawnień. Zdecydowanie chcielibyśmy aby pojawiła się nam w wyniku (przy złączeniu inner join pojawiły by się tylko role z co najmniej jednym powiązanym uprawnieniem). Właściwie możemy sobie dodać taką rolę do bazy poligon i wykonać ostatnie zapytanie jeszcze raz.

 
use poligon;
create role [db_uzytkownicy_viewer] authorization [db_owner];

Teraz przy pomocy podobnych mechanizmów, jak we wcześniejszych wpisach z tej serii, postaramy się zestawić różnice w obu bazach danych. Kluczowym elementem będzie odpowiednie przygotowanie sobie danych – informacje dotyczące każdej bazy wydzielimy sobie do osobnego bloku CTE. Ograniczymy również liczbę kolumn aby zapewnić lepszą czytelność.

 
with [role_prod] as ( select [dp].[name] as [role_name],
							 [dp].[type_desc] as [role_type],
							 [ob].[name] as [object_name],
							 [ob].[type_desc] as [object_type],
							 [dpe].[permission_name] as [permission_type],
							 [dpe].[state_desc] as [permission_state]
					  from [poligon].[sys].[database_principals] as [dp]
					  left join [poligon].[sys].[database_permissions] as [dpe] on [dpe].[grantee_principal_id] = [dp].[principal_id]
					  left join [poligon].[sys].[objects] as [ob] on [ob].[object_id] = [dpe].[major_id]
					  where [dp].[is_fixed_role] = 0
						and [dp].[type] != 'S'
						and [dp].[name] != 'public'
					),
	 [role_dev]  as ( select [dp].[name] as [role_name],
							 [dp].[type_desc] as [role_type],
							 [ob].[name] as [object_name],
							 [ob].[type_desc] as [object_type],
							 [dpe].[permission_name] as [permission_type],
							 [dpe].[state_desc] as [permission_state]
					  from [poligon_dev].[sys].[database_principals] as [dp]
					  left join [poligon_dev].[sys].[database_permissions] as [dpe] on [dpe].[grantee_principal_id] = [dp].[principal_id]
					  left join [poligon_dev].[sys].[objects] as [ob] on [ob].[object_id] = [dpe].[major_id]
					  where [dp].[is_fixed_role] = 0
						and [dp].[type] != 'S'
						and [dp].[name] != 'public'
					),
	 [cmp_prod]  as ( select * from [role_prod] except select * from [role_dev] ),
	 [cmp_dev]   as ( select * from [role_dev] except select * from [role_prod] )
select coalesce([p].[role_name], [d].[role_name]) as [role_name],
	   [p].[role_type] as [b1_role_type],
	   [p].[object_name] as [b1_object_name],
	   [p].[object_type] as [b1_object_type],
	   [p].[permission_type] as [b1_permission_type],
	   [p].[permission_state] as [b1_permission_state],
	   [d].[role_type] as [b2_role_type],
	   [d].[object_name] as [b2_object_name],
	   [d].[object_type] as [b2_object_type],
	   [d].[permission_type] as [b2_permission_type],
	   [d].[permission_state] as [b2_permission_state]
from [cmp_prod] as [p]
full outer join [cmp_dev] as [d] on [p].[role_name] = [d].[role_name] and [p].[role_type] = [d].[role_type] and
	coalesce([p].[object_name], '') = coalesce([d].[object_name], '') and
	coalesce([p].[object_type], '') = coalesce([d].[object_type], '') and
	coalesce([p].[permission_type], '') = coalesce([d].[permission_type], '')

Zapytanie jest dosyć złożone więc postaram się opisać jego kolejne elementy. Najpierw opiszę poszczególne bloki CTE.

W blokach role_prod oraz role_dev pobierane są informacje odnośnie ról i powiązanych z nimi uprawnień w poszczególnych baz danych. Łatwo można zauważyć, iż są to okrojone nieco wersje wcześniejszego zapytania.

Rolą bloków cmp_prod oraz cmp_dev jest pobranie z każdej kolekcji danych (pobranych z bloków role_prod oraz role_dev) tylko tych rekordów, których nie ma w drugiej bazie. Jeśli wyniki zapytań z role_prod i role_dev zawierałyby identyczne rekordy to bloki cmp_prod oraz cmp_dev usuną je z dalszych „rozważań”. Dzięki temu będziemy mieć do dyspozycji dane o różnicach dla każdej z baz.

W ostatnim kroku łączymy wyniki z bloków cmp_prod oraz cmp_dev w taki sposób aby w pierwszej kolumnie zachować nazwę roli a w kolejnych wszelkie różnice pomiędzy nimi w obu bazach. Przede wszystkim musimy zapewnić, że tożsama jest zarówno nazwa roli jak i jej rodzaj. W dalszej części staramy się łączyć nazwę i typ obiektu oraz rodzaj uprawnienia – wszystkie trzy elementy na raz stanowią łącznie unikalną definicję konkretnego uprawnienia. Dla danego uprawnienia w tej samej roli (w każdej z baz) różny może być tylko stan uprawnienia – dlatego nie pojawia się, jako jedyny, w warunku złączenia full outer join.

Teraz zaprezentujmy sobie rezultat naszego zapytania. Łatwo można zweryfikować, że faktycznie widać na nim różnice, które zasygnalizowałem na początku wpisu 🙂

Jak czytać uzyskane wyniki? Kolumna role_name informuje nas o nazwie roli. Wszystkie pozostałe kolumny posiadają prefiksy b1 oraz b2. Nie chciałem posługiwać się w wyniku oznaczeniami prod i dev. Zamiast tego wybrałem po prostu b1 (jako baza nr 1) oraz b2 (analogicznie jako baza nr 2). W kolumnach role_type mamy zapisane tak na prawdę dwie informacje. Jedną z nich jest typ roli a drugą, pokazaną w sposób być może mało oczywisty, to czy rola o podanej nazwie jest obecna w danej bazie. Dalsze pola są już bardzo czytelne – zawierają informacje o obiekcie (i jego typie) którego dotyczy uprawnienie, rodzaju tegoż uprawnienia oraz jego stanie.

Skoro mamy już działające zapytanie to możemy opakować je sobie w procedurę zapisaną w bazie master, podobnie jak w poprzednich przypadkach. Zachowując konwencję nazewniczą z poprzednich wpisów nazwałem tę procedurę dbo.migracje_sprawdzRole. Kod gotowej procedury zamieściłem w swoim repozytorium na GitHubie. Aby skorzystać z procedury należy wykonać poniższy kod.

 
-- Należy uważać na kolejność podawania nazw baz danych
exec master.dbo.migracje_sprawdzRole 'poligon', 'poligon_dev';

W kolejnym wpisie postaram się pokazać coś o wiele bardziej przydatnego – zajmiemy się odnajdywaniem różnic w modułach programowalnych.