Procedury składowane i wyzwalacze

(1)

ITA-101 Bazy Danych

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 9 Wersja 1.0

Procedury składowane i wyzwalacze

Spis treści

Procedury składowane i wyzwalacze ... 1

Informacje o module ... 2

Przygotowanie teoretyczne ... 3

Przykładowy problem ... 3

Podstawy teoretyczne... 3

Przykładowe rozwiązanie ... 8

Porady praktyczne ... 13

Uwagi dla studenta ... 14

Dodatkowe źródła informacji... 14

Laboratorium podstawowe ... 16

Problem 1 (czas realizacji 10 min) ... 16

Laboratorium rozszerzone ... 21

(2)

Informacje o module

Opis modułu

W tym module znajdziesz informacje na temat sposobu działania oraz podstaw tworzenia procedur składowanych. Dowiesz się, na czym polega różnica pomiędzy zwykłym zapytaniem T-SQL a procedurą składowaną oraz co to jest kompilacja i rekompilacja procedury. Poznasz również specjalny rodzaj procedury składowanej – wyzwalacz.

Cel modułu

Celem modułu jest zapoznanie czytelnika z procedurami składowanymi oraz wyzwalaczami w środowisku bazodanowym Microsoft SQL Server 2008 oraz zaprezentowanie, jak wykorzystać je w przykładowym projekcie.

Uzyskane kompetencje

Po zrealizowaniu modułu będziesz:

• wiedział, jak napisać własną procedurę składowaną

• umiał rozbudowywać gotowe procedury składowane

• umiał tworzyć proste wyzwalacze

• umiał zarządzać wyzwalaczami

• umiał wykorzystywać wyzwalacze do optymalizacji i automatyzacji działania SQL Server 2008

Wymagania wstępne

Przed przystąpieniem do pracy z tym modułem powinieneś:

• potrafić samodzielnie stworzyć bazę danych wraz z jej obiektami (patrz: moduł 4)

• znać podstawową składnię języka T-SQL

• umieć napisać własne zapytania w języku T-SQL (patrz: moduły 5 i 6)

• znać zaawansowane mechanizmy języka T-SQL (patrz: moduł 8)

• wiedzieć, na czym polega bezpieczeństwo w bazach danych (patrz:

moduł 10)

Mapa zależności modułu

Zgodnie z mapą zależności przedstawioną na rys. 1, przed przystąpieniem do realizacji tego modułu należy zapoznać się z materiałem zawartym w modułach 3, 5, 6, 8 i 10.

(3)

Przygotowanie teoretyczne

Przykładowy problem

Podczas wytwarzania bazy danych wiąże ze sobą grupę osób, do której należą projektant bazy danych, administrator systemu zarządzania bazą danych, administrator bazy danych oraz programista bazy danych. Bardzo ważne jest, żeby wszyscy biorący udział w wytwarzaniu systemu bazodanowego współdziałali ze sobą na różnych etapach. Najwięcej wspólnego z programowaniem w T-SQL ma programista bazy danych. Od niego w dużej mierze zależy, czy wytworzone funkcje, procedury składowane, wyzwalacze i inne element będą działały we właściwy sposób. To on jest odpowiedzialny za przygotowanie odpowiednich elementów programowych, które następnie będzie wykorzystywał projektant aplikacji i programista aplikacji dostępowej.

Kolejnym zadaniem jakie stoi przed programistą i administratorem bazy danych jest stworzenie odpowiedniej liczby wyzwalaczy, które są pewnym rodzajem procedur składowanych, z tymże wywoływanych niejawnie. Należy pamiętać, iż użycie procedur składowanych prowadzi do powstania kolejnej warstwy separującej użytkownika końcowego od fizycznych danych, tym samym powodując maskowanie fizycznej struktury bazy. Z tego powodu bardzo ważne jest przemyślane i odpowiednie przygotowanie zbioru procedur składowanych.

Podstawy teoretyczne Procedury składowane

Procedura składowana (ang. stored procedure) jest nazwanym zbiorem poleceń w języku SQL, który jest przechowywany na serwerze baz danych i jest kompilowany przy pierwszym wykonaniu.

Procedury wnoszą do środowiska bazodanowego przetwarzanie warunkowe i możliwości programistyczne.

W systemie zarządzania bazami danych wykonanie dowolnego fragmentu kodu języka SQL wiąże się z pewnym ciągiem czynności – począwszy od sprawdzenia składni, aż po kompilację i wykonanie.

Proces wykonywania zapytania przez SQL Server

Proces wykonania pojedynczego zapytania w języku T-SQL w Microsoft SQL Server 2008 dzieli się na następujące etapy:

1. Sprawdzenie i rozdzielenie kodu na fragmenty - dokonywany jest podział kodu na fragmenty nazywane symbolami. Proces ten nazywamy często nazywany jest analizą leksykalną.

2. Sprawdzenie kodu pod względem poprawności semantycznej i syntaktycznej – dokonywana jest kontrola poprawności semantycznej, czyli sprawdzenie, czy kod nie odwołuje się do nieistniejących obiektów lub nie używa nieistniejących poleceń oraz kontrola poprawności syntaktycznej, podczas której sprawdzana jest poprawność użytej składni.

3. Standaryzacja wyodrębnionej części kodu – silnik wykonywania zapytań zapisuje kod w jednoznacznej postaci (np. usuwa niepotrzebne symbole).

4. Optymalizacja – Microsoft SQL Server posiada wewnętrzny proces zwany Optymalizatorem Zapytań, który wybiera optymalny sposób dostępu do danych, tzn. taki plan wykonania zapytania (ang. execution plan), w którym serwer będzie skanował (przeszukiwał) najmniejszą ilość stron danych (o fizycznej strukturze danych w Microsoft SQL Server możesz przeczytać w module 6). Na optymalizację szczególny wpływ ma struktura indeksów oraz sposób łączenia tabel.

5. Kompilacja i wykonanie – zapytanie jest kompilowane według optymalnego planu wykonania i w takiej postaci wykonywane.

6. Zwrócenie wyników – wyniki zapytania zwracane są do klienta.

(4)

Proces wykonywania procedury składowanej

Wykonywanie procedur składowanych odbywa się inaczej niż wykonywanie pojedynczego zapytania SQL. Utworzenie i pierwsze wykonanie procedury w Microsoft SQL Server można podzielić na następujące kroki:

1. Utworzenie definicji procedury składowanej – wykonywane jest polecenie CREATE PROCEDURE.

2. Sprawdzanie kodu procedury pod względem poprawności – dokonywana jest kontrola poprawności syntaktycznej kodu procedury.

3. Zapamiętanie procedury w bazie danych – nazwa procedury i jej kod (tzw. ciało procedury) są zapisywane do odpowiednich widoków systemowych bazy danych (sysobjects oraz syscomments).

4. Wywołanie procedury – procedura składowana jest wywoływana z odpowiednimi parametrami poleceniem EXEC.

5. Właściwe wykonanie procedury – optymalizacja planu wykonania i kompilacja.

6. Buforowanie planu wykonania – skompilowany optymalny plan wykonania jest zapisywany w tzw. buforze procedur, skąd jest wczytywany przy następnym wywołaniu procedury.

Na rys. 2 pokazano schemat tworzenia i wykonania procedury składowanej w środowisku SQL Server 2008.

Rys. 2 Tworzenie i wykonanie procedury składowanej w SQL Server 2008

Rekompilacja procedur składowanych

Czasami zachodzi potrzeba ponownej kompilacji procedury składowanej. Dzieje się tak, gdy wydajność skompilowanej procedury gwałtownie spada (może tak być z wielu powodów, np.

zmiany struktury indeksów lub zapisania dużej ilości rekordów), gdy istnieje potrzeba kompilacji procedury przy każdym jej wykonaniu (powody mogą być te same, co w pierwszej sytuacji) lub gdy zmianie ulega kod samej procedury (gdy użyjemy polecenia ALTER PROCEDURE).

Rekompilacji, czyli ponownej kompilacji procedury, można dokonać na dwa sposoby:

• dodając w definicji procedury klauzulę WITH RECOMPILE.

• używając specjalnej systemowej procedury rekompilującej (w Microsoft SQL Server jest to procedura sp_recompile).

(5)

Tworzenie procedur składowanych

Do tworzenia procedur składowanych używamy polecenia języka SQL CREATE PROCEDURE (lub w skrócie CREATE PROC):

CREATE { PROC | PROCEDURE } [nazwa_schematu.]nazwa_procedury [ @parametr typ_danych[,…n] ]

[ WITH <opcje_procedury> [,…n] ] [ FOR REPLICATION ]

AS

cialo_procedury

<opcja_procedury> ::=

[ ENCRYPTION ] [ RECOMPILE ]

[ EXECUTE_AS_Clause ]

W definicji procedury składowanej określamy:

• nazwę procedury

• nazwy

• typy danych

• kierunek działania parametrów procedury

• ciało procedury (kod wykonywany przez procedurę)

Opcjonalnie możemy również zadeklarować, czy procedura ma być przy każdym wykonaniu rekompilowana.

Poniżej podano przykład utworzenia prostej procedury składowanej nie zawierającej żadnych parametrów:

CREATE PROCEDURE p_pracownicy AS

SELECT imie, nazwisko FROM Osoby

Wywołanie procedury składowanej

Do wywołania procedury składowanej służy polecenie EXECUTE (lub w skrócie EXEC):

[ { EXEC | EXECUTE } ] [ @return_status = ]

{ module_name [ ;numer ] | @module_name_var } [ [ @parametr = ] { wartość

| @zmienna [ OUTPUT ] | [ DEFAULT ]

} ] [,…n]

[ WITH RECOMPILE ]

Przykładowo dla utworzonej wcześniej procedury składowanej p_pracownicy wywołanie będzie wyglądało w następujący sposób:

EXEC p_pracownicy GO

Parametry procedur składowanych

Procedury składowane mogą przyjmować parametry wywołania. Ilość i typ danych, które należy podać przy wywołaniu procedury składowanej, określamy w trakcie tworzenia procedury (używając polecenia CREATE PROCEDURE). W zależności od tego, czy parametry będą potrzebne do

(6)

wykonania procedury, czy też mają być one przez procedurę zwrócone, wyróżniamy dwa rodzaje parametrów: wejściowe (INPUT) oraz wyjściowe (OUTPUT).

Możliwe jest też zdefiniowanie w procedurze parametru przejściowego (będącego jednocześnie wejściowym i wyjściowym), czyli parametru, którego wartość podajemy przy wywołaniu, a która może zostać zmieniona podczas działania procedury.

CREATE PROCEDURE p_pracownicy

@nazwisko varchar(40)='Smith' AS

SELECT imie, nazwisko FROM Osoby

WHERE nazwisko=@nazwisko GO

Zalety i wady procedur składowanych

Procedury składowane dzięki temu, że są zapisane na serwerze oraz dzięki skompilowanemu planowi wykonania przechowywanemu w buforze procedur posiadają dwie zasadnicze zalety:

• zwiększają wydajność bazy danych

• ograniczają ruch w sieci (przesyłane są tylko nazwy procedur i wartości parametrów)

Ponadto procedury składowane mają kilka zalet z punktu widzenia programistów aplikacji bazodanowych:

• Zapewniają jedną logikę biznesową dla wszystkich aplikacji klienckich.

• Przesłaniają szczegóły tabel w bazie danych (przezroczystość struktury dla zwykłego użytkownika aplikacji).

• Umożliwiają modyfikację danych bez bezpośredniego dostępu do tabel bazy danych.

• Dostarczają mechanizmów bezpieczeństwa (można nadawać uprawnienia do wykonywania procedur poszczególnym użytkownikom bazy danych).

• Umożliwiają programowania modularne (procedura zostaje zapisana w bazie danych, skąd można ją wielokrotnie wywoływać; procedurę może pisać osoba wyspecjalizowana w bazach danych – programista aplikacji jedynie ją wywoła).

• Zapewniają szybsze wykonanie (jeśli wymagane jest wykonanie dużej liczby zapytań, procedury składowane są szybsze, ponieważ wykonują się na serwerze, są optymalizowane i umieszczane w pamięci przy pierwszym wykonaniu).

• Zmniejszają ruch sieciowy (jeśli zachodzi potrzeba wykonania wielu zapytań w T-SQL na raz, można je zastąpić wywołaniem jednej procedury składowanej).

Jeśli w ogóle można mówić o wadach procedur składowanych, to w zasadzie można wspomnieć o kilku aspektach:

• Następstwem rekompilacji czasem jest zmniejszenie wydajności procedury (czyli administrator baz danych musi wiedzieć, kiedy przeprowadzić rekompilację).

• W przypadku zagnieżdżania procedur składowanych należy pamiętać o tym, że zmienia się kontekst wykonania (procedura zagnieżdżana wykonuje się z uprawnieniami innej procedury).

• Wreszcie, aby tworzyć dobre (tzn. poprawnie działające) procedury składowane niezbędne jest poznanie zaawansowanych mechanizmów języka programowania baz danych (np. T- SQL), takich jak zmienne, funkcje i procedury systemowe czy obsługa błędów.

Wyzwalacze

Wyzwalacz (ang. trigger) jest specjalnym rodzajem procedury składowanej. W przeciwieństwie do zwykłej procedury składowanej wyzwalacz nie może zostać jawnie wywołany.

(7)

Wyzwalacz jest wywoływany w reakcji na określone akcje. Akcje te to wykonanie przez użytkownika określonego polecenia SQL (INSERT, UPDATE, DELETE) na danej tabeli, dla której został określony wyzwalacz.

Tworzenie wyzwalaczy

Wyzwalacze tworzymy używając polecenia CREATE TRIGGER. W definicji wyzwalacza określamy:

• nazwę wyzwalacza

• dla jakiej tabeli tworzymy wyzwalacz

• na jakie akcje wyzwalacz będzie reagował

• jakiego typu wyzwalacz tworzymy

• ciało wyzwalacza (odpowiednik ciała procedury składowanej) – czyli kod wykonywany przez wyzwalacz

W SQL Server 2008 istnieją trzy rodzaje wyzwalaczy:

1. Wyzwalacze obsługujące operacje DML (ang. Data Manipulation Language), czyli ^INSERT, UPDATE oraz DELETE wykonywane na tabeli lub widoku.

CREATE TRIGGER [nazwa_schematu.]nazwa_wyzwalacza ON { table | view }

[ WITH <dml_opcje_wyzwalacza> [,…n] ] { FOR | AFTER | INSTEAD OF }

[ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] [ WITH APPEND ]

[ NOT FOR REPLICATION ] AS wyrazenie_sql [ ; ] [,…n]

<dml_opcje_wyzwalacza> ::=

[ ENCRYPTION ]

[ EXECUTE AS Clause ]

2. Wyzwalacze obsługujące operacje DDL (ang. Data Definition Language), czyli CREATE, ALTER, DROP oraz pewne procedury składowane, które wykonują operacje DDL.

CREATE TRIGGER nazwa_wyzwalacza ON { ALL SERVER | DATABASE }

[ WITH <ddl_opcje_wyzwalacza> [,…n] ]

{ FOR | AFTER } { rodzaj_zdarzenia | event_group } [,…n]

AS wyrazenie_sql [ ; ] [,…n]

<ddl_opcje_wyzwalacza> ::=

[ ENCRYPTION ]

3. Wyzwalacze obsługujące zdarzenie logowania (LOGON), które jest wywoływane, kiedy ustalana jest sesja logującego się użytkownika.

CREATE TRIGGER nazwa_wyzwalacza ON ALL SERVER

[ WITH <opcje_wyzwalacza_logon> [,…n] ] { FOR | AFTER } LOGON

AS wyrazenie_sql [ ; ] [,…n]

<opcje_wyzwalacza_logon> ::=

[ ENCRYPTION ]

(8)

Zastosowanie wyzwalaczy

Wyzwalacze znajdują wiele zastosowań w bazach danych. Przede wszystkim stosujemy wyzwalacze wszędzie tam, gdzie w inny sposób nie da się weryfikować integralności danych, a zwłaszcza ich zgodności z regułami logiki biznesowej. Na przykład chcemy, by w pewnej kolumnie tabeli wstawiane były tylko wartości unikalne, ale jednocześnie zezwalamy na wstawienie wartości NULL.

Jedynym rozwiązaniem jest użycie wyzwalacza, który sprawdzi, czy wstawiana właśnie wartość już w danym polu wystąpiła, a jeżeli użytkownik wstawia wartość NULL, to wyzwalacz mu na to pozwoli (tego efektu nie można osiągnąć innymi metodami, np. używając indeksów, ustawiając właściwość unikalności kolumny lub używając kryteriów sprawdzających dane wstawiane w kolumnę).

Drugie zastosowanie to wszelkiego typu automatyzacja zadań administracyjnych w bazie danych (wszelkiego rodzaju „przypominacze”, obsługa nietypowych działań czy chociażby wysyłanie wiadomości przez email lub pager).

Wreszcie z uwagi na pewne cechy wyzwalacze pozwalają na określony typ przetwarzania transakcyjnego.

Przykładowe rozwiązanie

Tworzenie i uruchomienie procedury składowanej

W cel utworzenia nowej procedury składowanej należy z paska narzędziowego wybrać New Query i wpisać kod procedury. Przykładowo:

CREATE PROCEDURE dbo.getAllCustomers AS

BEGIN

SELECT [CustomerID], [CompanyName]

FROM [Northwind].[dbo].[Customers]

END

Następnie należy wykonać skrypt wciskając F5 lub wybierając z paska narzędziowego Execute. Jeśli procedura składowana została utworzona poprawnie, powinieneś otrzymać komunikat

„Command(s) completed successfully” oraz zauważyć, że została dodana nowa procedura składowana w obszarze Object Explorer, w gałęzi Databases -> Northwind -> Programmability ->

StoredProcedures, co pokazano na rys. 3.

Jeśli procedury nie widać w wyżej wymienionej gałęzi, należy ją odświeżyć. Jeśli procedura jest już utworzona, a mimo to zażądano kolejnego jej utworzenia, to otrzymamy następujący komunikat:

„There is already an object named ‘getAllCustomers’ in the databases”.

(9)

Rys. 3 Tworzenie nowej procedury

Aby uruchomić procedurę składowaną, wystarczy użyć polecenia EXECUTE (lub krócej EXEC).

Można także kliknąć prawym klawiszem myszki procedurę i wybrać Execute Stored Procedure, co wywoła okienko, w którym można przekazać do procedury jakieś parametry. Na rys. 4 widać, że rozwijane menu udostępnia także inne opcje, takie jak modyfikacja lub usunięcie.

Rys. 4 Wywołanie procedury

Inne metody tworzenia procedur składowanych

Inną metoda tworzenia procedury składowanej jest skorzystanie z gotowego schematu procedury.

W tym celu kliknij prawym przyciskiem myszy na gałęzi Stored Procedures i wybierz New StoredProcedure – uzyskasz w ten sposób gotowy schemat procedury składowanej, co pokazano na rys. 5.

(10)

Rys. 5 Tworzenie nowej procedury według wzorca

Procedury składowane a polisy

W SQL Server 2008 wprowadzono politykę opartą o Management Framework dla silnika SQL, dzięki której zarządzanie serwerem będzie bardziej zautomatyzowane w oparciu o pewne reguły, a nie skrypty.

Zadaniem DMF (z ang. Declarative Management Framework) jest umożliwienie administratorowi zarządzania serwerami za pomocą definiowanych przez administratora reguł. W SQL Server 2008 mamy gotowe szablony reguł oparte o najlepsze praktyki zaimplementowane w narzędziu Best Practices Analyzer, używanym przez administratorów z poprzednimi wersjami systemu SQL Server.

Korzystając z mechanizmu polis możemy stworzyć restrykcje dla procedur składowanych o postaci:

brak możliwości definiowania przez programistę bazy danych procedur składowanych z prefiksem

„sp_”. Restrykcję tę wprowadzimy na bazie danych Biblioteka.

W ramach Object Explorer rozwijamy zakładkę Management a następnie Policy Menagement. W pierwszym kroku należy dodać warunki, jakie będą dołączone do polisy.

Jako pierwszy stwórzmy warunek, który będzie pilnował, żeby nazwa procedury składowanej nie posiadała prefiksu „sp_”. Konfigurację tego warunku pokazano na rys. 6.

(11)

Rys. 6 Stworzenie warunku na procedurę składowaną

Następnie należy stworzyć warunek dotyczący bazy danych, na której polisa, którą za chwilę założymy, będzie działała. Zostało to zilustrowane na rys. 7.

Rys. 7 Stworzenie warunku na bazę danych

W kolejnym kroku należy wystawić polisę i dodać do niej wcześniej stworzone warunki.

Konfiguracje wystawiania polisy pokazano na rys. 8.

(12)

Rys. 8 Założenie polisy

Kiedy mamy już wystawiona polisę i chcemy ją przetestować, powinniśmy kliknąć prawym przyciskiem myszy bazę danych Biblioteka i wybrać New Query. Następnie w edytorze możemy wpisać następujący kod, służący do utworzenia pustej procedury składowanej:

CREATE PROCEDURE sp_testowa AS

GO

Zauważmy, ze wystąpił błąd. Procedura nie została utworzona, gdyż zadziałała restrykcja DMF, co pokazano na rys. 9.

Rys. 9 Błąd procedury i zadziałanie polisy

(13)

Tworzenie wyzwalacza

Stwórzmy wyzwalacz, którego zadaniem będzie nie dopuścić do zalogowania wskazanego przez nas użytkownika. W tym celu na początku dodajemy użytkownika zbd_user i kojarzymy go z bazą danych AdventureWorks. Następnie tworzymy odpowiedni wyzwalacz, który nie pozwoli użytkownikowi zalogować się do Microsoft SQL Server Management Studio. W tym celu klikamy prawym przyciskiem myszy bazę AdventureWorks i z menu kontekstowego wybieramy New Query. W oknie edycji zapytania wpisujemy następujący kod:

USE AdventureWorks GO

CREATE TRIGGER trgRestrictUser ON ALL SERVER WITH EXECUTE AS 'sa' FOR LOGON

AS BEGIN

IF (ORIGINAL_LOGIN() = 'zbd_user' AND APP_NAME() = 'Microsoft SQL Server Management Studio')

ROLLBACK;

END

Aby przetestować działanie utworzonego wyzwalacza, uruchamiamy drugą instancję SQL Server Management Studio. W oknie Connect to Server Authentication wybieramy SQL Server Authentication, w polu Login wpisujemy zbd_user, a w polu Password – user. Następnie klikamy Connect. W efekcie powinien pokazać się błąd logowania jak na rys. 10.

Rys. 10 Bład logowania

Porady praktyczne Procedury składowane

• W Microsoft SQL Server przy kontroli poprawności kodu procedury w trakcie jej tworzenia serwer nie sprawdza, czy istnieją obiekty (tabele, widoki), do których procedura się odwołuje. Sprawdzenie to następuje dopiero przy wykonaniu procedury (w przypadku odwołania do nieistniejącego obiektu procedura zgłosi błąd).

• Rekompilacja procedury składowanej nie oznacza utworzenia procedury na nowo. Oznacza utworzenie nowego planu wykonania i zapisanie go do bufora procedury na miejsce poprzednio skompilowanego planu tej samej procedury.

• Systemowe procedury składowane w systemie Microsoft SQL Server 2008 są przechowywane w bazie master, zaś w ich nazwach pojawia się prefiks „sp_”.

• Zgodnie z dobra praktyka programowania baz danych, procedury składowane użytkownika w SQL Server 2008 nie powinny zaczynać się od prefiksu „sp_”. Prefiksem tym obarczone są systemowe procedury składowane. W sytuacji kiedy kompilator zobaczy procedurę składowaną o takiej nazwie, będzie jej szukał w procedurach systemowych jako tych, które już są skompilowane, a ich plan wykonania jest zapamiętany w buforze procedur. Dopiero kiedy jej tam nie znajdzie zacznie ją kompilować według poznanego schematu. Spowoduje to wydłużenie czasu wykonania procedury składowanej.

(14)

• Stworzenie polisy, która nie pozwoli programiście utworzyć procedury składowanej zaczynającej się od prefiksu „sp_” spowoduje, że pierwsza próba stworzenia procedury o takiej nazwie pociągnie za sobą konieczność uruchomienia sprawdzenia polisy, a zatem czas wykonania polecenia nieznacznie się wydłuży. Kolejna próba stworzenia procedury niezgodnej z polisą spowoduje natychmiastowe wyświetlenie komunikatu o naruszeniu restrykcji.

• Z punktu widzenia bezpieczeństwa fizycznej struktury bazy danych procedury składowane stanowią dodatkowy poziom izolacji pomiędzy warstwą aplikacji dostępowej do bazy danych a warstwą fizyczna bazy danych.

• Poprzez zastosowanie procedur składowanych możemy uniknąć jednego z najpopularniejszych ataków na bazę danych, tzw. Wstrzykiwania kodu SQL z racji tego iż w sieci pomiędzy bazą danych a aplikacją dostępową nie jest przesyłany kod SQL tylko nazwa procedury składowanej wraz z jej parametrami.

Wyzwalacze

• Wyzwalacze mogą być tworzone bezpośrednio w Microsoft SQL Server 2008 Database Engine za pomocą zwykłych zapytań napisanych w języku T-SQL lub poprzez specjalne metody w Microsoft .NET Framework Common Language Runtime (CLR), które po utworzeniu importowane są do instancji serwera bazodanowego.

• Microsoft SQL Server 2008 pozwala na tworzenie wielu wyzwalaczy dla specyficznego zdarzenia.

• Do tworzenia wyzwalaczy potrzebne są specjalne uprawnienia w bazie danych.

• Następujące instrukcje języka T-SQL nie są dozwolone w wyzwalaczach DML: ALTER DATABASE, LOAD DATABASE, RESTORE DATABASE, CREATE DATABASE, LOAD LOG, RESTORE LOG, DROP DATABASE i RECONFGURE. Dodatkowo powyższe instrukcje nie mogą być użyte wewnątrz ciała wyzwalacza DML.

Uwagi dla studenta

Jesteś przygotowany do realizacji laboratorium jeśli:

• rozumiesz, co to jest procedura składowana oraz wyzwalacz

• rozumiesz zasadę działania procedur składowanych i wyzwalaczy

• znasz składnię zaawansowanego języka Transact-SQL

• umiesz dodawać użytkowników do SQL Server

• rozumiesz różnicę pomiędzy różnymi rodzajami wyzwalaczy

Pamiętaj o zapoznaniu się z uwagami i poradami zawartymi w tym module. Upewnij się, że rozumiesz omawiane w nich zagadnienia. Jeśli masz trudności ze zrozumieniem tematu zawartego w uwagach, przeczytaj ponownie informacje z tego rozdziału i zajrzyj do notatek z wykładów.

Dodatkowe źródła informacji

1. Deren Bieniek, Randy Dyess, Mike Hotek, Javier Loria, Adam Machanic, Antonio Soto, Adolfo Wiernik, SQL Server 2005 Implementacja i obsługa, APN Promise, 2006

W książce obszernie przedstawiono zagadnienia związane z programowaniem baz danych. Szczegółowo omówiono zagadnienia dotyczące procedur składowanych i wyzwalaczy. Omówiono w niej implementacje procedur składowanych oraz w jaki sposób przyznawać do nich uprawnienia. Pokazano implementacje wyzwalaczy (DML i DDL). Książka szczególnie polecana ze względu na dużą zawartość ćwiczeń laboratoryjnych.

2. Kalen Delaney, Podstawy baz danych krok po kroku, APN Promise, 2006

(15)

Bardzo dobra książka dla osób początkujących. Pokazano w niej praktyczne zastosowanie wyzwalaczy i procedur składowanych. Książka oprócz teorii zawiera wiele przykładów.

3. Dusan Petkovic, Microsoft SQL Server 2008: A Beginner's Guide, McGraw-Hill, 2008

Pozycja napisana w sposób prosty. Wprowadza w SQL Server 2008 w sposób szybki i łatwy. Osoba początkująca w SQL Server 2008 znajdzie w niej podstawy z każdego tematu dotyczącego serwera bazodanowego. W prosty sposób dowiesz się, jak tworzyć proste procedury składowane bez parametrów i z parametrami oraz jak posługiwać się wyzwalaczami. Pozycja polecana zarówno dla osób początkujących, jak i zaawansowanych.

(16)

Laboratorium podstawowe

Problem 1 (czas realizacji 10 min)

Jesteś programistą bazodanowym w firmie National Insurance. Właśnie dowiedziałeś się od swojego szefa, że firma planuje rozszerzenie na skalę uczelnianą systemu prac dyplomowych, którym zarządzała jak dotąd na Twoim wydziale. Pierwsze zadanie, jakie przed Tobą stoi to analiza już istniejących procedur składowanych.

Zadanie Tok postępowania

1. Nawiąż połączenie z SQL Server 2008

• Uruchom maszynę wirtualną BD2008.

— Jako nazwę użytkownika podaj Administrator.

— Jako hasło podaj P@ssw0rd.

Jeśli nie masz zdefiniowanej maszyny wirtualnej w Mirosoft Virtual PC, dodaj nową maszynę używając wirtualnego dysku twardego z pliku D:\VirtualPC\Dydaktyka\BD2008.vhd.

• Kliknij Start. Z grupy programów Microsoft SQL Server 2008 uruchom SQL Server Management Studio.

• W oknie logowania kliknij Connect.

2. Uzyskaj informacje o bazie danych

• Z menu głównego wybierz File -> Open -> File.

• Odszukaj plik C:\Labs\Lab09\ProcedurySystemowe.sql i kliknij Open.

• Zaznacz i uruchom (F5) poniższy fragment kodu, który wywołuje procedurę systemową sp_helpdb:

-- (1) Ustawiamy sie na baze danych PraceDyplomowe USE PraceDyplomowe

GO

-- (2) Wywolanie procedury systemowej sp_helpdb EXEC sp_helpdb PraceDyplomowe

Procedura sp_helpdb zwraca informacje o wybranej bazie danych (rozmiar, listę plików i informacje o nich).

3. Uzyskaj informacje o obiektach bazy danych

• Zaznacz i uruchom (F5) poniższy fragment kodu, który wywołuje procedurę systemową sp_help:

-- (3) Wywolanie procedury systemowej sp_help EXEC sp_help dbo.Prace

Procedura sp_help zwraca informacje o wybranym obiekcie bazy danych.

Czy w tabeli dbo.Prace jest jakakolwiek kolumna typu bit?

4. Uzyskaj informacje o indeksach tabeli

• Zaznacz kod, który wywołuje procedurę systemową sp_helpindex.

-- (4) Wywolanie procedury systemowej sp_helpindex EXEC sp_helpindex dbo.Prace

• Wciśnij F5, aby uruchomić zaznaczony fragment kodu.

Procedura sp_helpindex zwraca listę i opis indeksów założonych na kolumnach w wybranej tabeli.

Czy w tabeli dbo.Prace jest nałożony jakiś indeks?

(17)

Po dokonaniu analizy procedur już istniejących, kolejne zadanie, jakie zostało przed Tobą postawione, to zdefiniowanie procedur składowanych dla nowych użytkowników oraz modyfikacja już istniejących procedur składowanych. W pierwszej kolejności powinieneś zająć się procedurami składowanymi bez parametrów.

Jeśli nie masz zdefiniowanej maszyny wirtualnej w Microsoft Virtual PC, dodaj nową maszynę używając wirtualnego dysku twardego z pliku D:\VirtualPC\Dydaktyka\BD2008.vhd.

2. Utwórz procedurę składowaną

• Odszukaj plik C:\Labs\Lab10\Procedura_bez_parametrow.sql i kliknij Open.

• Zaznacz kod, który wywołuje procedurę składowaną Promotorzy:

GO

-- (2) Tworzymy procedure skladowana uzytkownika CREATE PROCEDURE Promotorzy

AS BEGIN

SELECT [Nazwisko], [Imie]

FROM Promotor END

Innym sposobem stworzenia procedury składowanej jest kliknięcie prawym przyciskiem myszy gałęzi Stored Procedures i wybranie New StoredProcedure – uzyskasz w ten sposób gotowy schemat procedury składowanej.

Jeśli procedura składowana została utworzona poprawnie, powinieneś otrzymać komunikat „Command(s) completed successfully” oraz zauważyć, że została dodana nowa procedura składowana w Object Explorer, w gałęzi Databases -> PraceDyplomowe -> Programmability -> StoredProcedures. Jeśli procedury nie widać w w/w gałęzi, odśwież ją. Jeśli procedura jest już utworzona, a mimo to zażądano kolejnego jej utworzenia, to powinieneś otrzymać komunikat „There is already object name ‘Promotorzy’ in the database”.

3. Uruchom procedurę składowaną

• Zaznacz kod, który wywołuje procedurę Promotorzy:

-- (3) Wywolujemy procedure skladowana Promotorzy EXEC Promotorzy

GO

(18)

prawym przyciskiem myszy na procedurze Promotorzy i wybranie Execute Stored Procedure, co wywoła okienko, w którym można przekazać do procedury jakieś parametry, jak na rys. 11.

Rys. 11 Wywołanie procedury składowanej z parametrem

Kolejne zadanie, jakie powinieneś wykonać, to zdefiniowanie procedur składowanych dla nowych użytkowników oraz modyfikacja już istniejących procedur składowanych z parametrami.

2. Utwórz procedurę składowaną

• Odszukaj plik C:\Labs\Lab10\Procedura_z_parametrami.sql i kliknij Open.

• Zaznacz kod, który wywołuje procedurę składowaną Promotorzy:

GO

-- (2) Tworzymy procedure skladowana użytkownika z parametrem CREATE PROCEDURE dbo.Promotorzy

@Nazwisko VARCHAR(30) AS

BEGIN SELECT *

FROM [PraceDyplomowe].[dbo].[Osoba]

WHERE [PraceDyplomowe].[dbo].[Osoba].[nazwisko] = @Nazwisko END

3. Uruchom procedurę składowaną

• Zaznacz kod, który wywołuje procedurę Promotorzy. Wciśnij F5, aby uruchomić zaznaczony fragment kodu:

-- (3) Wywolujemy procedure skladowana z parametrem EXEC Promotorzy @Nazwisko='Kowalski'

GO

(19)

Po zdefiniowaniu procedur składowanych wynikających ze zmian, jakie zachodzą w firmie, w której pracujesz, kolejnym etapem jest stworzenie odpowiedniej liczby wyzwalaczy. Jeden z wyzwalaczy powinien obsługiwać następujący warunek: każdy opiekun może mieć maksymalnie 10 dyplomantów.

2. Utwórz wyzwalacz

• Odszukaj plik C:\Labs\Lab10\Wyzwalacz_1.sql i kliknij Open.

• Zaznacz i uruchom (F5) poniższy fragment kodu:

-- (1) Ustawiamy się na baze danych PraceDyplomowe USE PraceDyplomowe

GO

-- (2) przygotujmy tabele do testowania wyzwalaczy SELECT ID_Osoby, Nazwisko, Imie, Nr_Indeksu, ID_Stopnian INTO Osoba_kopi

FROM Osoba GO

• Zaznacz i uruchom (F5) fragment kodu oznaczonego w komentarzu (3).

Wynik pokazano na rys. 12:

-- (3) zobaczmy, co znajduje sie w tabeli SELECT * FROM Osoba_kopi

GO

Rys. 12 Sprawdzenie zawartości tabeli

• W obrębie okna Object Explorer wybierz Osoba -> Triggers.

(20)

• Wciśnij prawy przycisk myszy i wybierz New Trigger.

• Zaznacz i uruchom (F5) poniższy fragment kodu:

-- (4) Tworzymy nowy wyzwalacz CREATE TRIGGER Ocenay

ON Osoba

AFTER INSERT, UPDATE AS

DECLARE @username as varchar(30) SELECT @username = SYSTEM_USER

PRINT 'Uzytkownik '+ @username + ' zmienil zawartosc tabeli Osoba!!!'

GO

• W celu zobaczenia działania wyzwalacza wywołaj następujące zapytanie:

-- (5) Sprawdzamy dzialanie wyzwalacza UPDATE Osoba

SET [Nazwisko] = 'Nowak' WHERE ID_Osoby=1

GO

Jeśli wyzwalacz zadziałał poprawnie, w oknie Messages powinien pojawić się komunikat o treści przedstawionej na rys. 13.

Rys. 13 Sprawdzanie efektu działania wyzwalacza

(21)

Laboratorium rozszerzone

Jesteś administratorem w firmie National Insurance. Właśnie dowiedziałeś się od swojego szefa, że firma zarządzająca bazą AdventureWorks planuje rozszerzenie i modernizacje systemu. W związku z modernizacją systemu bazodanowego oraz w celu spełnienia standardów bezpieczeństwa, dostęp do obiektów bazy danych powinien odbywać się poprzez procedury składowane. W wyniku tego część procedur składowanych powinna zostać zmodyfikowana i przekompilowana, część powinna zostać napisana od początku, a część powinna zostać usunięta.

Kolejnym pojawiającym się problemem jest kwestia spójności zmodyfikowanej bazy danych. W tym celu powinny zostać zmodyfikowane, utworzone lub usunięte wyzwalacze służące do zaimplementowania pewnych warunków.

Zadanie, jakie przed Tobą stoi, to:

1. Podjęcie decyzji, jakie nowe procedury składowane i wyzwalacze powinny zostać utworzone w celu poprawienia bezpieczeństwa bazy danych.

2. Podjęcie decyzji, które procedury składowane w bazie danych pozostaną bez zmian, a które zostaną zmodyfikowane lub usunięte.

3. Podjęcie decyzji, które wyzwalacze w bazie danych pozostaną bez zmian, a które zostaną zmodyfikowane lub usunięte.