Na 11 laboratorium zajmiemy się zagadnieniem widoków i uprawnień.

Tworzenie nowego użytkownika

Z bazy danych może korzystać wielu użytkowników. Nie wszystkim chcemy dawać te same uprawnienia. Na przykład administrator powinien mieć możliwość przeglądania i zmiany wszystkich danych, pracownik sklepu tylko tych, które dotyczą sprzedaży i informacji o klientach, a klient powinien móc jedynie przeglądać produkty.

Aby sprawdźić wszystkich użytkowników używamy polecenia

SELECT User, Host FROM mysql.user;

Tworzymy nowego użytkownika

CREATE USER 'johngalt'@'localhost' IDENTIFIED BY 'dollar';

W tej chwili nie ma on prawa do przeglądania i zmieniania żadnej z baz. Aby mu je nadać posługujemy się poleceniem

GRANT ALL PRIVILEGES ON actors.films TO 'johngalt'@'localhost';
FLUSH PRIVILEGES;

Zauważmy, że przyznawanie użykownikom uprawnień na istniejących tabelach jest ryzykowne i niepożądane. W naszym przypadku moglibyśmy chcieć explicite zabronić dzieciom przeglądania opisów filmów dla dorosłych. Jak to zrobić? Moglibyśmy stworzyć nową tabele z filmami dla dzieci. Dlaczego nie jest to dobry pomysł? Inna możliwość to stworzenie widoku, czym za chwilę się zajmiemy.

Aby usunąc uprawnienia używamy komendy REVOKE

REVOKE ALL PRIVILEGES ON actors.films FROM 'johngalt'@'localhost';
FLUSH PRIVILEGES;

Dobry tutorial dotyczący administracji bazą danych znajduje się tutaj.

Widoki

Na wstępie odsyłam do przyzwoitego tutorialu na temat widoków. W razie problemów można tam znaleźć wiele pożytecznych informacji.

Stwórzmy najpierw prosty widok, w którym będziemy mieli dostęp wszystkich aktorów, którzy wystąpili w co najmniej 30 filmach.

CREATE VIEW frequent_actors AS
  SELECT * from actors WHERE
    actors.actor_id IN
    ( SELECT actor_id FROM film_actors
    GROUP BY actor_id
      HAVING COUNT(film_id) >30
    )

Przy tworzeniu zapytań z widokiem, korzystamy z niego jak ze zwykłej tabeli. Jednak nie jest on fizycznie przechowywany w pamięci.

SELECT COUNT(*) FROM frequent_actors;

Ćwiczenie

  1. Stwórz widok child_suitable_movies z filmami, które mogą oglądać dzieci do 13 lat.
  2. Nadaj Johnowi Galtowi uprawnienia do SELECT na widoku child_suitable_movies
  3. Połącz się z bazą z poziomu DataGripa jako johngalt i przekonaj się, że nie masz wtedy uprawnień do przeglądania tabel, ani do zmieniania widoku child_suitable_movies.

Updatable views

Jeśli damy użytkownikowi uprawnienia do zmieniania (UPDATE, DELETE) widoku, to powstaje pytanie w jaki sposób należy tę informację propagować na tabelę na podstawie których widok powstał. MySQL decyduje o tym za nas w sposób automatyczny.

O ile definicja widoku nie zawiera pewnych komend, to jest on updatable, to znaczy można zmieniać za jego pośrednictwem bazę danych.

Przykład widoku, który nie jest updatable

CREATE VIEW actors_frequencies AS
  SELECT first_name, last_name, COUNT(*) AS freq from actors INNER JOIN film_actors ON actors.actor_id = film_actors.actor_id
    GROUP BY actors.actor_id;

Aby sprawdzić, które widoki są updatable uzywamy

SELECT
    table_name, is_updatable
FROM
    information_schema.views
WHERE table_schema='actors';

Ćwiczenie

  1. Dodaj nowy film do bazy korzystając z widoku child_suitable_movies
  2. Zastanów się jakich użytkowników możesz mieć w swoim projekcie. W jaki sposób możesz wykorzystać widoki, aby zapewnić, że każdy użytkownik będzie miał dostęp jedynie do tych informacji, które są mu niezbędne. Czemu nie należy dawać użytkownikom zbyt dużych uprawnień?