Ostatnio napisałem kilka słów o swoim podejściu do różnego rodzaju ORM-ów. Jeśli więc w Twojej głowie pojawiła się myśl, że być może warto zainteresować się tymi nudnymi SQL-ami, to dziś opowiem Ci nieco więcej o warstwie, którą możesz zbudować między swoją aplikacją a tabelami w bazce.
W telegraficznym skrócie mogę Ci powiedzieć, że będzie o widokach, funkcjach i procedurach, a jeżeli używasz ORM-a, to także o korzyściach, jakie daje mapowanie ich na model obiektowy zamiast operowania bezpośrednio na tabelach. Wszystko dotyczy SQL Servera i Entity Frameworka, bo z nimi mam do czynienia najczęściej, ale całość wygląda bardzo podobnie w większości systemów baz relacyjnych i ORM-ów, jakie widziałem.
Widoki
Zacznijmy od początku, czyli od opisania wspomnianych konstrukcji. Na pierwszy ogień weźmy widoki. Definicja podana w dokumentacji jest bardzo prosta.
A view is a virtual table whose contents are defined by a query. Like a table, a view consists of a set of named columns and rows of data.
Możemy więc napisać mniej lub bardziej skomplikowane zapytanie i zapakować je w widok, by nie klepać kilku(nastu/dziesięciu/set) linii SQL-a za każdym razem, gdy będziemy się odwoływać do jakiegoś zbioru ładnie uporządkowanych danych. Widoki oczywiście możemy nazywać, daje nam to więc dosyć ciekawe możliwości porządkowania danych bez ich denormalizacji, bo zupełnie inaczej będziemy rozumieć klienta sklepu w kontekście rozliczeń, wysyłki produktu czy zebrania statystyk na potrzeby marketingowe.
Stwórz więc jakąkolwiek bazę i dodaj do niej kilka tabel. Pozwoliłem sobie uprościć ten model na potrzeby przykładu.
CREATE TABLE [dbo].[Customer] ( [Id] int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED, [FirstName] nvarchar(100) NOT NULL, [LastName] nvarchar(400) NOT NULL, [Email] nvarchar(254) NOT NULL, [Gender] tinyint NOT NULL, [ShippingAddress] nvarchar(max) NULL, [Phone] varchar(20) NULL ) INSERT INTO [dbo].[Customer] ([FirstName], [LastName], [Email], [Gender], [ShippingAddress], [Phone]) VALUES ('Test', 'Testowy', 'Test@test.com', 1, N'Testowa ulica w testowym mieście', '0048555555555'), (N'Przykład', N'Przykładowy', 'przyklad@przyklad.com', 1, N'Przykładowa ulica w przykładowym mieście', '0048666666666') CREATE TABLE [dbo].[CreditCard] ( [Id] int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED, [Number] varchar(19) NOT NULL, [ValidityDate] date NOT NULL, [OwnerId] int NOT NULL CONSTRAINT [Fk_CreditCard_Owner] FOREIGN KEY REFERENCES [dbo].[Customer] ([Id]) ) INSERT INTO [dbo].[CreditCard] ([Number], [ValidityDate], [OwnerId]) SELECT '1234-5678-9012-1234', '2018-01-31', [Id] FROM [dbo].[Customer]
Mamy więc nieco informacji o kliencie i jego kartach płatniczych. Gdybyśmy przesyłali dane do wysyłki paczki, potrzebowalibyśmy imienia, nazwiska, telefonu, e-maila i adresu. Można powiedzieć, że jest to praktycznie cała tabela, więc walimy SELECT * lub odpowiednik w ORM-ie (czyli podejście zwane czasem fachowo „encja na twarz i pchasz” polegające na całkowitym ignorowaniu metody Select) i lecimy.
Należy jednak pamiętać, że z biegiem czasu nasza tabela może spuchnąć i ulec fundamentalnym zmianom (które pokażę Ci za chwilę), w związku z tym będziemy wyciągać coraz więcej i więcej kolumn, a nasze potrzeby dotyczące przekazania danych do wysyłki nie ulegną zmianie. Sugeruję zatem napisać kawałek Selecta (czy to SQL-owego, czy czegoś, co Twój ORM przetłumaczy na projekcję).
SELECT [FirstName] + ' ' + [LastName] AS [FullName], [Phone], [ShippingAddress], [Email] FROM [dbo].[Customer] WHERE [Id] = 1
Aby to uprościć, uporządkować i nazwać, by ktokolwiek wiedział, co podmiot liryczny ma na myśli, możemy wszystko zapakować w ładny i zgrabny widok, który z góry określa, że mamy do czynienia z danymi do wysyłki. Przy okazji nasz widok umieśćmy w nowo utworzonej schemie shipping – to dosyć duży skrót myślowy, ale o schemie można myśleć jak o namespace’ie.
CREATE SCHEMA [shipping] GO CREATE VIEW [shipping].[CustomerShippingData] AS SELECT [Id] AS [CustomerId], [FirstName] + ' ' + [LastName] AS [FullName], [Phone], [ShippingAddress], [Email] FROM [dbo].[Customer]
A wywołanie go później to już poezja.
SELECT * FROM [shipping].[CustomerShippingData] WHERE [CustomerId] = 1
Większość ORM-ów pozwala na mapowanie widoków i oprócz samej czytelności oraz niezmienności daje nam to jeszcze jedną korzyść – bardzo dużą kontrolę nad zapytaniem, które faktycznie jest wysyłane do bazy. W przypadku bardziej skomplikowanych przypadków może to mieć bardzo duże znaczenie dla wydajności, a w takiej chmurze – bezpośrednio na ilość zasobów, jakich potrzebujemy i za jakie zapłacimy.
Funkcje i procedury
Odrobinę innym przypadkiem są funkcje i procedury. Choć widoki służą na ogół jako stosunkowo proste „nakładki” na tabele, to w nich możemy zawrzeć już całkiem sporo logiki i całkiem dużą część pracy zrzucić na silnik bazodanowy, co pozwala nam na pisanie chociażby rekurencyjnego gromadzenia danych, pracę z tymczasowymi tabelami czy zmiennymi tabelarycznymi.
O tym wszystkim w szczegółach pewnie napiszę w przyszłości, bo zarówno same funkcje, jak i procedury składowane to tematy na oddzielne posty. W tej chwili musisz wiedzieć, że tutaj zaczyna się prawdziwe programowanie w SQL-u i można tu zawrzeć naprawdę bardzo rozbudowane rzeczy, a w odróżnieniu od widoków możemy tu przekazać własne parametry.
Podstawowa różnica między obydwoma jest taka, że funkcje służą do przetworzenia jakichś danych i zwrócenia wyniku – funkcja nie może mieć żadnych skutków ubocznych dla stanu bazy. Procedury mają z kolei wykonać jakieś operacje, często związane właśnie ze zmianą stanu, po czym zwrócić kod oznaczający status (int). Przyjęło się, że „0” oznacza poprawne wykonanie procedury. Możliwe jest zwrócenie wielu innych informacji, ale o tym innym razem.
Przejdźmy zatem do tego, co to oznacza dla Ciebie. Zanim dotrzemy do sedna przyczyny, dla której warto umieszczać takie informacje po stronie bazy danych, napiszmy prostą procedurę dodającą klienta do naszej bazy.
CREATE SCHEMA [customers] GO CREATE PROCEDURE [customers].[AddCustomer] @firstName nvarchar(100), @lastName nvarchar(400), @email nvarchar(254), @gender tinyint, @shippingAddress nvarchar(max), @phone varchar(20) AS BEGIN INSERT INTO [dbo].[Customer] ([FirstName], [LastName], [Email], [Gender], [ShippingAddress], [Phone]) VALUES (@firstName, @lastName, @email, @gender, @shippingAddress, @phone) END
I po raz kolejny – wywołanie takiej procedury wygląda o wiele czytelniej niż zwykły insert.
EXEC [customers].[AddCustomer] @firstName = N'Rafał', @lastName = 'Hryniewski', @email = 'rafal@hryniewski.net', @gender = 1, @shippingAddress = 'Adres', @phone = '0048111111111'
Te przykłady są banalne, ale musisz pamiętać, że spora część operacji nie jest taka prosta i raczej nie składa się z jednego etapu. Nawet samo dodanie klienta może wymagać nieco więcej operacji niż zwykły insert. W funkcjach i procedurach możemy zrobić naprawdę sporo – ważne jest to, że pracuje wtedy baza danych, nie nasza aplikacja. A jakie z tego korzyści? Czytaj dalej.
Co z logiką biznesową?
Jeśli zdarzyło Ci się kiedyś nad tym zastanawiać, to w międzyczasie mógł Cię ogarnąć niepokój – przecież jesteśmy o krok od wrzucenia logiki biznesowej do bazy. Toć to herezja!
I tutaj się z Tobą całkowicie zgodzę. Wrzucenie zbyt wiele logiki do bazy będzie bardzo trudne w utrzymaniu. SQL-a można debugować, można go też testować, ale raczej nie powinniśmy tam wrzucać wszystkich reguł walidacji czy zmieniać stanu połowy aplikacji, bo wrzuciliśmy platynowego klienta albo inne dziwne mechanizmy.
Moim zdaniem na bazę można zrzucić całkiem sporo, ale jednak lepiej, żeby dotyczyło to pobierania danych niż ich zapisywania – zarządzanie stanem aplikacji mimo wszystko pozostawmy poza bazą. Proste operacje w stylu dodania klienta są jak najbardziej w porządku, ale jeśli wiążą się z tym operacje np. dodania punktów pracownikowi, który wspomnianego klienta pozyskał, to powinny one zostać obsłużone już w samej aplikacji.
Korzyści
Wróćmy jednak do naszej abstrakcji. Co nam daje załadowanie prostych operacji w funkcje i procedury, a wyników niektórych zapytań w widoki?
Jak już wspomniałem, często jest to kwestia kontroli nad tym, co się w bazie dzieje, co w wielu przypadkach może przyczynić się do znacznych wzrostów wydajności. Ale nie jest to jedyna zaleta takiego podejścia.
Produkty, nad którymi pracujemy, mogą być dosyć duże. Jeśli cały system korzysta z jednej bazy, to możemy dość szybko zorientować się, że np. z tabeli User korzysta w ten lub inny sposób pół aplikacji. Owszem, są architektury, które tego problemu nie mają. Owszem, doprowadzenie do tej sytuacji nie jest pożądane. Ale nie zawsze mamy do czynienia z greenfieldem i mnóstwem czasu na najlepsze praktyki.
Modyfikacje w locie
I tak docieramy do momentu, w którym ktoś informuje Cię, że klient prosi, byśmy zmienili kolejność wyświetlania imienia i nazwiska, gdy zwracamy dane do wysyłki.
Niby pierdoła, ale ktoś się zorientował, że w kraju naszego odbiorcy może to być uznawane za wielki nietakt. Potrzebne będzie wdrożenie. Oczywiście masz pecha i w Twoim projekcie CI/CD nie istnieje. Na dodatek w kraju klienta zbliżają się święta i trzeba to ogarnąć na wczoraj. Cóż, życie. To jak wyglądały Twoje plany na piątkowy wieczór?
Nic się nie martw. Przecież wszystko, co potrzebne do zmiany, masz zrobione po stronie bazy danych. Wystarczy, że zmodyfikujesz uprzednio utworzony widok.
ALTER VIEW [shipping].[CustomerShippingData] AS SELECT [Id] AS [CustomerId], [LastName] + ' ' + [FirstName] AS [FullName], [Phone], [ShippingAddress], [Email] FROM [dbo].[Customer]
Aplikacja nie przestanie działać nawet na chwilę, a cała operacja zajmie mniej niż sekundę. Problem rozwiązany, nie zmieniliśmy nic w kodzie samej aplikacji i ominął nas smutny deploy w piątkowy wieczór.
Schemabinding
Wszystko wygląda fajnie. Ale jak się w tym połapać i dopilnować, by aktualizować warstwy nad tabelami, gdy te ulegają zmianom?
Słowo klucz – schemabinding. Zmodyfikujmy nasz widok jeszcze raz, tym razem dodając opcję WITH SCHEMABINDING.
ALTER VIEW [shipping].[CustomerShippingData] WITH SCHEMABINDING AS SELECT [Id] AS [CustomerId], [LastName] + ' ' + [FirstName] AS [FullName], [Phone], [ShippingAddress], [Email] FROM [dbo].[Customer]
Nasz widok jest teraz powiązany z tabelą, z której korzysta, i baza nie pozwoli nam na modyfikację obiektów, od których jesteśmy w jakiś sposób zależni.
I tak możemy dodać kolumnę, której nasz widok nie używa.
ALTER TABLE [dbo].[Customer] ADD [Something] nvarchar(100)
Możemy też ją usunąć. Podobnie jak kolumnę Gender, z której nie korzystamy.
ALTER TABLE [dbo].[Customer] DROP COLUMN [Something] ALTER TABLE [dbo].[Customer] DROP COLUMN [Gender]
Ale jeśli spróbujemy chociaż tknąć kolumnę zawierającą imię, zostaniemy potraktowani błędem. Spróbuj tylko zrobić coś takiego.
ALTER TABLE [dbo].[Customer] ALTER COLUMN [FirstName] nvarchar(200)
A w odpowiedzi MS SQL zwróci Ci komunikat:
Msg 5074, Level 16, State 1, Line 1
The object ‘CustomerShippingData’ is dependent on column ‘FirstName’.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN FirstName failed because one or more objects access this column.
Aby w jakikolwiek sposób zmodyfikować kolumnę, od której zależne są Twój widok i Twoja procedura, musisz je usunąć i po zamierzonych modyfikacjach utworzyć jeszcze raz, nawet jeśli ich kod nie zostanie zmodyfikowany.
Ciąg dalszy nastąpi?
I na tym dziś skończmy. Istnieje możliwość, że nigdy nie zajdzie konieczność, by w Twoim projekcie używać tego typu technik. Jednak jeśli pewnego dnia okaże się, że będzie Ci to potrzebne, to mam nadzieję, że będziesz o tych narzędziach pamiętać i pomyślisz o mnie ciepło.
A jeżeli takie modelowanie danych Ci się podoba i zastanawiasz się, jak wygląda pociągnięte naprawdę bardzo daleko, to rzuć okiem na pojęcie Anchor Modeling w Wikipedii. Już od jakiegoś czasu noszę się z zamiarem stworzenia na ten temat prezentacji i napisania kilku postów – wyraź zainteresowanie tym zagadnieniem w komentarzu, a może nieco się sprężę. ;)
Do przeczytania!
Witam
A już chciałem pisać “gdzie obiecany artykuł”. A tu tyle ciekawych rzeczy, “WITH SCHEMABINDING” o tym nie wiedziałem , kolejna ciekawostka, która się przyda.
Bardzo dobry tekst.
Proszę o więcej.
A czy może powiesz też coś o CTE, pomaga przy pisaniu rozbudowanych zapytań ale rekurencji napisanej w tym to nie ogarniam.
Cześć, miałem poślizg. Trochę dużo się u mnie ostatnio dzieje i nie wyrabiam się na zakrętach. Ale miło, że tęskniłeś :)
CTE to bardzo potężny i ciekawy temat sam w sobie. Myślę, że za jakiś czas coś się pojawi :)
A czy Anchor Modeling jest związany z grafami?
Po za tym to proszę o rozszerzenie tematu Anchor Modeling.
http://www.anchormodeling.com
Online Modeler,
Czekam na wyjaśnienie tematu ;-)
Ma części wspólne, a o grafach SQL Server 2017+ pewnie tez kiedyś będzie :)
Hey.
Przyznam, że to pierwszy Twój artykuł, z którym się zapoznałem i przyznam, że jest ciekawie. Muszę poczytać Twoje wcześniejsze wpisy, ponieważ w tym momencie mam pewne “wątpliwości”. Od kilku lat siedzę w czystych SQLach i każdy kolejny prosty SELECT, INSERT, UPDATE jest frustrujący. Powoli marzę o tym aby zrobić sobie coś pobocznego na ORMach aby nie pisać tych wszystkich prostych zapytań. Tylko jak czasami muszę napisać dosyć skomplikowany SQL to cieszę się, że robię to bez ORM. I teraz zaczyna się mój problem. Lepiej bawić się ORMami, czy z samymi SQLami czy połączyć oba rozwiązania. Może jak poczytam Twoje wcześniejsze wpisy to problem się rozwiąże.
Szacun za wiedzę!
Pisałem o tym już wcześniej, ale małe streszczenie – jeśli już masz zapiętego ORMa w projekcie to do prostych przypadków w zupełności wystarczy, jeśli nie masz i nie chcesz/nie możesz go wpinać, to wyboru niestety nie masz.
Ja lubię łączyć oba podejścia i brać co najlepsze z obu wtedy gdy potrzebuję, grunt to mieć świadomość co mniej więcej generuje ORM jeśli się go używa.
A wcześniejszych wpisów za dużo tutaj jeszcze nie ma :). Ale mam trochę SQLowych rzeczy na woim starym, trochę przykurzonym blogu (hryniewski.net).
Dzięki za SCHEMABINDING! Jestem od 4 lat w T-SQL’ach i nie znałem :)
Co do logiki biznesowej po stronie bazy danych to pracowałem w firmie gdzie tak to działało. Jedyny plus to bardzo szybkie wdrożenia, bo apka też była napisana pod te rozwiązanie. Szybko != dobrze, jedna zmiana siała spustoszenie gdzieś na drugim końcu łańcucha :) Można było nabrać sprawności w testowaniu i poruszaniu się w SQL!
Aktualnie moja klawiatura rwie się poza SQL i jak muszę coś wyciągać z bazy to mam dylemat. Czy zrobić to po stronie aplikacji, a może mała procka? Pasuje mi Twoje podejście – Odczyt = Baza, Prosty Zapis = Baza, Logika = Apka.
PS: Anchor Modeling brzmi nieźle!
Anytime! :)
Można zrobić całkiem sporo w samym SQLu, ale tak jak mówisz – coś może się sypnąć w zupełnie innym miejscu i upilnować to jest o wiele trudniej niż w powiedzmy C#.
A ORMy warto znać, chociażby po to, że jak w jakimś profilerze zobaczysz dziwne query, które trzeba zoptymalizować- to znajomość ORMa pozwala to trochę łatwiej namierzyć w kodzie.
Więcej, więcej, więcej :-)
Będzie :)
Zainteresowany! Dej więcej! ;-) Pozdrawiam :-D
Dam, obiecuję ;)
Zapowiada się ciekawa i przemyślana seria, a temat “Anchor Modeling” wzbudził moje zainteresowanie, bo klasyczne podejście do zmian, czyli skrypty migracyjne itp. zawsze powodują, że danego dnia jest -25% do chęci ;-).
Ciekawe co myślą na ten temat “klasycznie” DBA-ie? …muszę zapytać z jednego.
Anchor Model co prawda nie próbowałem na produkcji, ale w temat ostatnio wsiakam bo jest naprawdę ciekawy.
Pewnie powstanie niedlugo coś na ten temat. W planach mam sesję na ten temat, wiec można męczyć organizatorów grup lokalnych, jak zaprosza to chętnie przyjadę ;)
Get.Net za nami, a na najbliższy dopiero w przyszłym roku ale zaproponuję ciebie jako prelegenta, oczywiście jak nie zapomnę ewentualnie możesz do mnie napisać (pewnie Marcin może ci udostępnić maila).
…chyba sobie wbiję do kalendarza, żeby nie zapomnieć
Możesz tez dać mi znać jak będzie CFP. Chętnie się zgłoszę :)
Bardzo fajnie się czytało, przy okazji odświeżając wiedzę nabytą sprintem na studiach.
„With Schemabinding” chodziło za mną wiele razy, ale dopiero teraz na konkretnym przykładzie zrozumiałem, o co w tym wszystkim chodzi.
Co do wykonywania procedur i funkcji, jak to się ma z optymalizacją? Czy wywołanie przytoczonej procedury jest korzystniejsze czasowo od typowego inserta?
Pozdrawiam i proszę o więcej!
Odpal management studio, odpal kawałek kodu i podejrzyj plan zapytania, zapisz go jako procedurę i ja wywołaj z podglądem planu.
Najczęściej zobaczysz dokładnie to samo, da się co prawda to obejść i wymusić wykonywanie w sposób zoptymalizowany pod pewne wartości w parametrach, ale te sytuacje możemy pominąć.
Fajny artykuł, również nie wiedziałem o tym schemabinding a to całkiem przydatna rzecz!
Pisz więcej kolego!
Tak patrzę, ze tematowi schemabinding można się przyjrzeć bliżej :)
Czekam na kolejne wpisy :-) Pisz o anchor modeling!
U mnie w firmie mamy sposób na puchnięcie bazy danych, aby nie tworzyć i zmieniać struktury bazy danych dodajemy do każdej encji dodatkowe pole, które przetrzymuje dane w postaci XML. Pozwala to dynamicznie rozszerzać i modyfikować tabele bez obawy o problemy, używamy do tego własnego rozwiązania opartego o coś na wzór event sourcingu(odtwarzanie bazy w locie), więc kolejny skrypt nie zaburzy poprzedniej wersji w razie przykrej pomyłki podczas migracji.
Jestem ciekaw jak Ty to rozwiązujesz ?
BardZo nie lubię XMLa wiec podchodzę do tego nieco inaczej, ale fajnie byłoby usłyszeć więcej o twoim podejściu.
Ja danych staram się nie migrować i nie ruszać, zamiast tego wole pozostawiać dane już nieprzydatne i nieużywane w kolumnach lub dostawiać nowe z wartościami domyślnymi. A jeśli nad tym mamy solidna warstwę widoków to brak lub nadmiar danych nas aż tak nie boli.
No dobrze.
Są w artykule informacje o bazach danych.ale gdzie ta tytułowa abstrakcja?
W tym wypadku widoki, funkcje itd. Robią za abstrakcje ponieważ same w sobie nie przechowują (na ogół) żadnych danych. Mogą wiec pełnić role kontraktu właśnie.
Aha. Abstrakcja między bazą a bazą a nie miedzy aplikacją a bazą :) Co aplikację obchodzi czy dane są z widoku czy tabeli?
Sama treść interesującą i warta solidnego rozwinięcia. Szczególnie, że danych przybywa.
Jest to mały skrót myślowy :)
Aplikacji nie obchodzi to absolutnie nic, ale ciągnąc dane z widoku możesz na spokojnie po stronie bazy określić skąd dokładnie pochodzą i dla aplikacji jest to szczegół implementacyjny
Bardzo ciekawy artykuł. Ja co prawda jestem wyznawca czystego sql, ale może twoje podejście podpowie mi trochę nowych rozwiązań. Szczególnie by mnie interesowały zagadnienia związane z data science, funkcjami statystycznymi, funkcjami okienkowymi oraz machine learning związanym z big data. Aktualnie używam actian Vector i naprawdę polecam przyjrzeć się bliżej tej bazie danych. Chętnie poczytam również o anchor modeling.
Akurat DS i ML się nie zajmuję, ale kto wie, może coś Ci się przyda.
Anchor modeling to już w zasadzie pewniak, może nie będzie to następny post, ale będzie :)
Hej.
Mnie zastanawiają konkretne powody dlaczego umieszczanie logiki w procedurach/funkcjach jest złe ?
Akurat w firmie w której pracuje spora część logiki jest w procedurach, a wartwa aplikacji służy jedynie do wyświetlania zwracanych z procedury danych. Obróbka/ formatowanie oraz zmiany jakie mają być zapis danych odbywa się w procedurach.
Zaletą powyższego rozwiązania o którym chyba nie wspomniałeś jest, że jak przenosilismy system z WinForms na WPF bądź na stronę internetową, to logika była już przygotowana, i takie eksporty były dosyć łatwe :)
Bardzo podobają mi się Twoje artykuły, czekam na następne :D
Jak masz setki funkcji/procedur i jeszcze nie są podzielone na schemy to naprawdę ciężko się tym zarzadza, zwłaszcza jeśli baza zostanie zle zamodolewana.
Nie ma co ukrywać, w każdym projekcie jest trochę chujowego kodu, a w Management Studio nie zrobisz sobie F12 i nie poprzegladasz w sprawny sposób funkcji która jest wywołana w funkcji która jest użyta w procedurze i masz jeden dziwny edge case w którym to nie działa.
A ze ma swoje zalety to fakt, ale do przenoszenia kodu wolałbym jednak zamknąć DAL w jakiejś paczce.
Całkiem ciekawe podejście, praktykuję jeśli chodzi o stronę READ w aplikacji widoki. Natomiat ostatnio zaczyna mi to przeszkadzać ze względu na ilość tych widoków w aplikacji :) ciężko się tym zarządza.
Muszę sprawdzić opcję “With Schemabinding” bo uczciwie z niej jeszcze nie korzystałem.
Ps. Bardzo fajny aktykuł. Oby więcej :)
Do zarządzania polecam zawsze mieć aktualne diagramy i dzielić wszystko na schemy, wtedy jest trochę łatwiej gdy wiesz co jest gdzie, robi co i jakie tabele skrywa pod sobą.
PS. Dziękuje :)
Mega się czyta Twoje wpisy. Czekam na więcej! :)
Postaram się byś nie czekał zbyt długo :)
Dzięki!
Czesc.
Super post.
Wiekszosc przykladow ktore znajduje ladnie pokazuje jak latwo z poziomu aplikacji wyswietlic/dodac/usunac dane w bazie poprzez select/insert/update czy tez poprzez wywolanie procedury w bazie.
Ale nie znalazlem nigdzie dobrego przykladu jak ustawic security tak zeby applikacja mogla wykonac te zadania ale sam uzytkownik juz nie.
Przyklad:
Chce w aplikacji wyswietlic liste pracownikow. Wysylam wiec “select * from dbo.Employees” to bazy na serwerze (ms sql server).
Ale skoro aplikacja moze zrobic select na tabeli dbo.Employees to w jaki sposob powtrzymac uzytkownika przez bezposrednim polaczeniem sie z serwerem, baza i tabela poprzez inne narzedzie?
Wiem ze mozna stworzyc application roles ale wtedy chyba traci sie informacje ktory user rzeczywiscie dodal/zmodyfikowal dane.
Z gory dzieki za pomoc i czekam na kolejny arktykul :)
Cześć,
to temat, który chciałem poruszyć innym razem. Ja załatwiłbym sprawę uprawnieniami na poziomie schemy. Dla aplikacji możesz stworzyć login i użytkownika, które będzie wykorzystywać do dostępu do bazy (w connection stringu). Możesz tam zabrać wszystkie uprawnienia dostępu i ponadawać je ręcznie do schemy w której znajdują się Twoje funkcje, widoki itd. Jeśli wszystko ładnie zaplanujesz, to Twoja aplikacja może “nie wiedzieć” o czymkolwiek w bazie co nie jest funkcją, procedurą lub widokiem.
A zanim taki post powstanie to kikla przykładów znajdziesz tutaj: https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-schema-permissions-transact-sql?view=sql-server-2017
Dzięki za odpowiedź.
Już próbowałem to podejście ale natknąłem się na 2 problemy:
1. trzeba zapisać user name i password w connection stringu
2. baza nie wie kim jest rzeczywisty user na polecenie ktorego aplikacja wykonuje dane zadanie.
Próbowałem innego podejścia.
Stworzylem server login dla użytkownika ktoremu nadalem prawa do łączenia się z serwerem i nic więcej. Następnie użyłem trusted connection do połączenia sie z bazą, W ten sposób Windows credentials użytkownika są przekazywane do serwera i serwer wie czy dany użytkownik może połączyć się z serwerem.
Następnie stworzyłem database user bez loginu np. appuser. Temu użytkownikowi nadałem prawa select, execute, delete, itd. w bazie na poziomach scheme’y. Podobnie do tego co sugerowałeś w swojej odpowiedzi.
W tym momencie zaczyna się problem. Bo po zalogowaniu się na serwer powinna nastąpić zmiana contextu poprzez wykonanie EXECUTE AS appuser.
I o ile wywołuje stored proc to mogę umieścić to jako pierwszą linię kodu i wszystko gra. Ale jak i kiedy zmienic user context jeżeli wywołuję select * from … bezpośrednio z aplikacji?
Sorry za dlugi post ale chciałem jak najlepiej opisać sytuację i problem.
AD1. Nie trzeba. Możesz budować connection string dynamicznie i przekazywać do konstruktora contextu. A hasła przechowywać w jakimś bezpiecznym miejscu.
Np https://docs.microsoft.com/en-us/dotnet/api/system.security.cryptography.protecteddata?redirectedfrom=MSDN&view=netframework-4.7.2
Reszta ja Rafał napisał.
Wybacz długi czas odpowiedzi, zagrzebałem się trochę.
Mam nadzieję, że to co proponuje Jacek Ci pomogło. Jeśli nie to daj znać, a pomyślimy nad innym rozwiązaniem :).
Dzieki za odpowiedzi.
Niestety nie do konca moge zastosowac pomysl Jacka. Z tego co rozumiem to musialbym stworzyc usera dla aplikacji i na tym userze laczyc sie z serwerem. Tylko ze wtedy trace informacje o rzeczywistym uzytkowniku ktory wywolal kod. Szczegolnie chodzi o updejtowanie danych gdzie chcialbym logowac kto co zmienil.
Ale tez nie chce zeby uzytkownik mogl uzyc PowerQuery czy PowerBI do polaczenia sie bezposrednio z baza i sciagania danych.
Z jednej strony potrzebuje zeby mogli miec dostep do bazy poprzez applikacje a z drugiej nie chce zeby mogli miec dostep do danych poza aplikacja.
Wszystkie przyklady ktore znajduje skupiaja sie na tym jak dac uzytkownikowi dostep do bazy, stworzyc polaczenie, wywolac sql statement (np. select * from…), zmappowac to do objektu w aplikacji i wyswietlic dane. I to wszystko jest super.
Tylko ze nikt sie nie martwi tym ze ten sam uzytkownik otwiera Excela, robi connection do bazy i sciaga sobie dane bezposrednio do Excela :)
Dzieki za pomoc.
Czy dobrze rozumiem, że użytkownik nie powinien mieć możliwości połączenia się z bazą w żaden sposób. A jego login powinien posłużyć tylko i wyłącznie do przekazania z aplikacji?
Może sposobem byłoby zablokowanie dostępu do serwera dla tych użytkowników?
Jak chcesz logować zmiany to możesz w DAL w aplikacji. Poczytaj o auditing
Są fajne dodatki do EF. I reszta ja pisałem.
Rafal,
uzytkownik nie powinien miec bezposredniego dostepu do tabel chyba ze przez aplikacje.
Ale moze laczyc sie z baza za pomoca np. Power BI Desktop i odpalac okreslone stored procedure.
Wyglada ze to jest jednak grubszy temat. Chyba zrezygnuje z uzywania bezposrednio SQLa w aplikacji i opre sie na stored proc’ach. Chociaz to komplikuje utrzymywanie kodu.
Jacek,
wlasnie chcialem uniknac robienia calego auditu w aplikacji. Jezeli kazdy uzytkownik loguje sie na swoim loginie to wtedy mozna uzyc triggery na tabeli zeby logwac aktywnosc. Ale moze to sie okazac konieczne przeniesienie tego do aplikacji,
No nie wiem co jest prostsze. Pisanie trigerow czy ustawienie automatycznego audytu. Pomijając wydajność.
Tu chyba nie da się użyć żadnej magii. Albo user ma dostęp do obiektów bazy albo nie ma.
Możesz użytkownikom preparować jakieś ukryte części haseł żeby nie znali pełnych A część ukrytą znała tylko aplikacja ale to już inżynierskie kombinowanie.
Wlasnie mialem nadzieje na odrobine magii. Szczegolnie ze swieta sie zblizaja :)
Myslalem ze jest jakis myk na to ktorego nie znam. Ale widze ze jednak nie.
Trzeba bedzie powoli i mozolnie wbudowac te wszystkie zabezpieczenia w aplikacje.
Nie ma drogi na skroty :)
Dzieki za pomoc!
Dla mnie aplikacje internetowe to czarna magia, zwłaszcza te wymagające połączenia z bazami danych. Może ktoś poleci firmę, która wykonuje aplikacje mobilne na zamówienie, które można zintegrować z aplikacjami internetowymi za pomocą API? Chciałbym, aby robota została wykonana fachowo.
Wg mnie granica co trzymać po stronie bazy danych a co po stronie kodu jest dość jasna – po stronie kodu trzymamy logikę biznesu a po stronie bazy wszystko co tylko może ułatwić nam wyciąganie surowych danych. Widoki są super sprawą bo pozwala Ci nazwać pewną, nawet skomplikowaną SQLkę a to ułatwia poruszanie się po kodzie.