Ćwicząc tworzenie baz danych oraz stosowanie języka SQL na systemie MySQL pamiętać należy o tym, że implementacja SQL w tym systemie jest niepełna -- wykazuje on szereg ograniczeń i braków, w stosunku zarówno do standardu SQL jak i do implementacji dostępnych w czołowych komercyjnych systemach zarządzania relacyjnymi bazami danych. Z drugiej strony, doświadczenia zdobyte na bazie użytkowania jakiegokolwiek konkretnego RDBMS nie są w pełni przenośne na inne systemy, jako że każdy z nich wykazuje swoiste cechy odnośnie implementowanego dialektu SQL, a w zakresie narzędzi do tworzenia interfejsów użytkownika czy metod administracji bazami danych standaryzacja w zasadzie nie istnieje. W zamian za swoje ograniczenia, MySQL oferuje jednak pewne istotne zalety: jest to system niewielki (pod względem wielkości kodu programów i wymagań sprzętowych), względnie niezbyt skomplikowany, i bardzo wydajny nawet na całkiem dużych zbiorach danych. No i przede wszystkim w większości przypadków można z niego korzystać za darmo (z ograniczeniami szczegółowo podanymi w warunkach licencji).
Projektanci MySQL dokonali pewnych wyborów, rezygnując z implementowania tych cech czy funkcji obsługiwanych przez inne RDBMS, które okazały się nie do pogodzenia z ich założeniami projektowymi, dotyczącymi optymalizacji sprawności systemu, lub po prostu wydawały im się niepotrzebne z punktu widzenia przewidywanych przez nich samych zastosowań. MySQL nie jest jedynym RDBMS dostępnym za darmo na platformy unixowe; szczególnie popularną alternatywą jest PostgreSQL, system pod wieloma względami bardziej rozbudowany i pełniej realizujący standardy SQL. W konkretnych zastosowaniach brak pewnych możliwości, których nie posiada MySQL, może okazać się niewygodny lub wręcz nie do przyjęcia; oczywiście, jeżeli wniesienie opłaty licencyjnej (na ogół dość wysokiej) nie stanowi problemu, warto rozważyć zastosowanie któregoś z czołowych komercyjnych RDBMS, jak Oracle lub Sybase. Należy jednak pamiętać o często znacznie większych wymaganiach sprzętowych jakie należy spełnić, aby takie systemy mogły pracować z pełną wydajnością. Od niedawna większość czołowych producentów systemów baz danych udostępnia bezpłatnie swoje produkty do testowania na systemie operacyjnym Linux, co niewątpliwie powinno ułatwić dokonanie wyboru. Niewykluczone, że przyszłe wydania niniejszego kursu zostaną oparte na którymś z wymienionych komercyjnych RDBMS, o ile warunki licencyjne i sprzętowe będą na to pozwalały.
Przejdziemy teraz do krótkiego omówienia najważniejszych konkretnych ograniczeń i braków MySQL w stosunku do standardu oraz innych popularnych RDBMS, wskazując (tam gdzie jest to możliwe) sposoby ominięcia tych ograniczeń proponowane w MySQL. Szersze omówienie znajduje się jak zwykle w dokumentacji.
SELECT
MySQL nie pozwala na (zgodne ze standardową składnią SQL) zagnieżdżone instrukcje
SELECT, jak w przykładzie
SELECT * from table WHERE id IN (SELECT id from table2)
Implementacja tej składni przewidziana jest jednak w kolejnych wersjach MySQL i być może jest już ona dostępna w wersji nowszej, aniżeli zainstalowana w tej chwili na naszym serwerze.
SELECT INTO TABLE
Obecnie MySQL nie pozwala na bezpośrednie zapisanie wyniku instrukcji SELECT
do tabeli za pomocą składni typu SELECT ... INTO TABLE .... W zamian
można wykorzystywać konstrukcję SELECT ... INTO OUTFILE ... ,
zapisując wynik do pliku tymczasowego, a następnie LOAD DATA INFILE ....
Możliwe jest jednak również stosowanie instrukcji SELECT zagnieżdżonej
w instrukcji INSERT: INSERT INTO tabela SELECT ....
Za najpoważniejszy brak systemu MySQL uważa się zazwyczaj niezaimplementowanie
obsługi transakcji oraz cofania
(instrukcje COMMIT i ROLLBACK z innych RDBMS),
co jest standardowo przyjętym sposobem zapewniania integralności danych w sytuacjach,
gdy wielu użytkowników może jednocześnie wykonywać operacje modyfikujące dane
w tych samych tabelach. W zastępstwie MySQL oferuje instrukcję
LOCK TABLES, umożliwiającą zablokowanie innym użytkownikom dostępu
do określonych tabel na czas ich modyfikacji. Zob. też następujące
wyjaśnienia w dokumentacji.
LOCK TABLESSkładnia:
LOCK TABLES tabela1 READ|WRITE [ tabela2 READ|WRITE ]
Blokuje dostęp do wymienionych tabel w sposób określony klauzulą READ
lub WRITE. Przy podaniu klauzuli READ tabela
będzie dostępna jedynie do odczytu, zarówno dla bieżącego użytkownika (dokładniej: dla
wątku serwera obsługującego bieżące połączenie) jak i dla wszystkich pozostałych, do czasu
zniesienia blokady. Przy klauzuli WRITE jedynie aktualny wątek
będzie dopuszczony do jakichkolwiek operacji na tabeli (zarówno odczytu jak i zapisu lub
modyfikacji).
Zniesienie blokad nałożonych przez aktualny wątek następuje w wyniku wykonania instrukcji
UNLOCK TABLES
zamknięcia połączenia z serwerem, lub wykonania kolejnej instrukcji
LOCK TABLES, która znosi wszystkie wcześniejsze blokady.
MySQL wprawdzie akceptuje deklaracje kluczy obcych w instrukcji
CREATE TABLE, lecz nie wykonuje w ich wyniku żadnej akcji -- tzn.
informacja o kluczach obcych nie jest w żaden sposób zapisywana. Oznacza to, że
implementacja integralności referencyjnej opartej na kluczach obcych jest zadaniem
dla aplikacji.
,,Widoki'' to coś w rodzaju wirtualnych tabel, stosowanych w większości RDBMS po to, by różni użytkownicy lub różne aplikacje mogły w sposób mniej więcej automatyczny korzystać z różnych sposobów organizacji tych samych danych. Funkcjonalność ta nie jest dotąd zaimplementowana w MySQL, choć jest to planowane na przyszłość.
W większości implementacji SQL istnieją instrukcje CREATE INDEX
i DROP INDEX, służące do tworzenia i odpowiednio usuwania indeksów
dla istniejących tabel. W MySQL instrukcje te nie działają (choć ich użycie nie jest
błędem), tworzenie i usuwanie indeksów musi być wykonywane bądź przy okazji
CREATE TABLE, bądź za pomocą ALTER TABLE,
jak to omówiono wcześniej.
Ponieważ system uprawnień dostępu do baz danych zaimplementowany w MySQL różni się dość wyraźnie od stosowanych w innych RDBMS, tutaj jest właściwe miejsce na jego krótkie omówienie.
W MySQL przyznanie uprawnień następuje na podstawie kombinacji użytkownik
plus host z którego nawiązano połączenie. Należy więc pamiętać, że jedno
i to samo konto użytkownika na ogół korzysta z różnych uprawnień, w zależności od tego,
z jakiego komputera zostało nawiązane połączenie z serwerem baz danych -- chodzi
oczywiście o komputer, na którym uruchomiono program klienta mysql lub
inny program nawiązujący połączenie, np. korzystający z funkcji biblioteki do tworzenia
programów klienckich MySQL.
Konta użytkowników MySQL (jak również ich hasła) nie mają w zasadzie nic wspólnego z unixowymi kontami loginowymi na serwerze, oraz ich hasłami. W najprostszym przypadku rozsądną polityką jest, aby nazwy tych kont były takie same -- ale nic nie stoi na przeszkodzie, aby użytkownik bazy danych MySQL wogóle nie posiadał konta loginowego na maszynie serwera baz danych, i nawiązywał kontakt z serwerem jedynie za pomocą programu (lub programów) klienckich uruchamianych na innym komputerze. Często może to być wręcz pożądane ze względów bezpieczeństwa.
Obszar roboczy serwera MySQL to zbiór baz danych, z których każda jest
po prostu zbiorem tabel dla którego można oddzielnie zdefiniować uprawnienia dostępu.
Informacja o użytkownikach, istniejących bazach danych, i uprawnieniach dostępu
przechowywana jest w specjalnej systemowej bazie danych o nazwie mysql.
Standardowo zawiera ona cztery tabele, o nazwach db,
user, host i func. Ostatnia z nich
nie ma związku z uprawnieniami dostępu, lecz służy do zarządzania utworzonymi przez
użytkowników rozszerzeniami serwera MySQL, skompilowanymi do bibliotek dynamicznych.
Nie będziemy tu jej bliżej omawiać
(o tworzeniu rozszerzeń).
Uprawnienia użytkownika MySQL dotyczą operacji na encjach tabel (SELECT,
INSERT, UPDATE, DELETE), operacji
dotyczących całych tabel i baz danych (CREATE i DROP),
operacji związanych z manipulacją plików (LOAD DATA INFILE i
SELECT INTO OUTFILE), oraz czynności administracyjnych
(RELOAD, SHUTDOWN, PROCESS).
Uprawnieniom tym odpowiadają kolumny w tabelach uprawnień,
o nazwach w rodzaju Select_priv,
File_priv, Drop_priv itp. Wartości w tych kolumnach
mogą być 'Y' (,,tak'') lub 'N' (,,nie''), tzn. kolumny te
są typu enum('N','Y').
Tabela user zawiera kolumny Host typu
char(60) i User typu char(16),
stanowiące łącznie klucz główny, kolumnę Password typu
char(16) przeznaczoną na zapisanie hasła użytkownika
w postaci zaszyfrowanej, oraz kolumny opisujące wszelkie możliwe
uprawnienia jakie można przyznać danej parze Host+User, o wartościach
domyślnych 'N'. Uprawnienia dotyczące encji, tabel i baz danych
przyznane w tabeli user stosują się do wszystkich tabel wszystkich
baz danych danego serwera. Kolumny uprawnień File_priv i administracyjnych
(Reload_priv, Shutdown_priv i
Process_priv) występują jedynie w tej tabeli i jedynie tutaj mogą
być przyznane.
Tabela db posiada klucz główny złożony z kolumn Host,
User i Db (char(64)), i kolumny
odpowiadające uprawnieniom do operacji na encjach oraz tabelach. Poprzez wartość
'Y' w odnośnej kolumnie tej tabeli nadaje się danej parze
Host+User prawo do odpowiednich operacji obejmujące tabele z danej
bazy danych.
Rzadziej stosowana jest tabela host; jej kluczem głównym są kolumny
Host i Db (kolumny opisujące uprawnienia są takie
same jak w tabeli db), i przydaje się ona w sytuacjach, gdy użytkownicy
łączą się z bazą danych z wielu różnych zdalnych maszyn. Wówczas w kolumnie
db.Host można dla danych użytkowników pozostawić wartość pustą,
a wpisy opisujące maszyny klienckie wprowadzić do tabeli host -- będą
one wykorzystywane do przyznawania tym użytkownikom uprawnień do operacji
na określonych tu bazach danych.
W kolumnach Host umieszcza się nazwę domenową (np.
bobo.fuw.edu.pl), specjalną nazwę localhost
opisującą połączenia lokalne (z maszyny serwera), lub adres internetowy
(np. 148.81.6.15). Dozwolone są metaznaki % i
_, interpretowane jak zwykle w napisach SQL. Podanie jako wartości
w db.Host napisu pustego powoduje, że wykorzystane będą
wpisy z tabeli host -- jak opisano powyżej. Podanie wartości
pustej w user.Host lub host.Host jest równoważne
podaniu wartości '%' -- tzn. uprawnienia będą przysługiwały
połączeniom nawiązanym z dowolnego adresu (z dowolnej maszyny, która
jest w stanie nawiązać połączenie TCP/IP z serwerem).
W kolumnach Db również dopuszcza się stosowanie metaznaków;
wartość pusta jest równoważna '%'.
Nie są one natomiast dozwolone w kolumnach User. Pusta wartość
w User prowadzi do przyznania uprawnień użytkownikom o
dowolnych nazwach (nie rozpoznanych na podstawie innych wpisów, p. poniżej.).
Sposób wykorzystania wpisów w tabelach uprawnień jest następujący. Po pierwsze, tabele
host, db i user sortowane są
w taki sposób, że w pierwszej kolejności wykorzystuje się wiersze dla których
wartości w kolumnach Host nie zawierają metaznaków %
i _, następnie zawierające te metaznaki, a na końcu -- z wartościami
pustymi. W ramach tego porządku następuje analogiczne sortowanie według wartości
w kolumnie User, oraz (dla tabeli db) według wartości
kolumny Db. Do przyznania uprawnień będą wykorzystane
pierwsze wartości pasujące znalezione zgodnie z tym porządkiem. W pierwszej kolejności
wartości user.User i user.Host zostają dopasowane
do podanej nazwy użytkownika i źródła połączenia (ze sprawdzeniem hasła, o ile jest
ono niepuste). Następnie przeszukana zostanie tabela db, z zachowaniem
opisanego porządku sortowania, i użytkownikowi zostaną przyznane uprawnienia dostępu
do poszczególnych baz danych wynikające z tej tabeli. Kolejny krok ma miejsce jedynie,
jeżeli dopasowanie encji z tabeli db nastąpiło przy pustej wartości
db.host: w takim przypadku przeszukana zostanie tabela
host i wykonany zostanie iloczyn logiczny uprawnień wynikających
z tabeli db i tabeli host. To znaczy, że przyznane
zostaną jedynie te uprawnienia, dla których w odnośnych kolumnach obu tabel występuje
wartość 'Y'. Na koniec, w ostatnim kroku wykonana zostaje suma
logiczna ustalonych dotąd uprawnień z tymi, które wynikają z dopasowanego wiersza
tabeli user -- a więc uprawnienia globalne zdefiniowane w tej tabeli
mogą ew. poszerzać zakres uprawnień jakie przyznano użytkownikowi w odniesieniu
do konkretnych baz danych. Wynikiem tej nieco złożonej procedury jest ostateczny
zestaw uprawnień, jakie będą przysługiwały danemu użytkownikowi (a dokładniej -- danemu
połączeniu z serwerem MySQL).
Przy tworzeniu nowych użytkowników, zmianach uprawnień itp. stosuje się zwykłe
instrukcje SQL, wykonując operacje na tabelach bazy danych mysql.
Zmiany dokonane w tych tabelach zostaną uwzględnione przez serwer dopiero po
dokonaniu operacji reload (np. za pomocą programu
mysqladmin). Oczywiście wszelkie tego rodzaju operacje wymagają
odpowiednich uprawnień; domyślnie (po instalacji serwera) przysługują one jedynie
użytkownikowi o nazwie root, lecz można to zmienić w zwykły sposób
(tj. tak samo jak uprawnienia do operacji na innych bazach danych, i za pomocą
wpisów w tabeli user odnośnie operacji administracyjnych jak
reload). W ten sposób uprawnienia administracyjne mogą być
selektywnie przyznawane wielu osobom.
Hasła MySQL muszą być zapisywane w tabeli user w postaci
zaszyfrowanej. Służy do tego wbudowana funkcja PASSWORD.
Strona ta była oglądana