Wykład 10
Ograniczenia | System uprawnień
Ograniczenia MySQL
Ć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.
Zagnieżdżone instrukcje 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 ....
Transakcje
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.
Instrukcja LOCK TABLES
Skł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.
Klucze obce
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 (views)
,,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ść.
Indeksy
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.
System uprawnień
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).
Uwagi
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.