Relacyjne bazy danych - laboratorium 8

Wprowadzenie

Na dzisiejszych laboratoriach konczymy zagadnienia związane z SQL. Do pracy na zajęciach wymagane są gotowe zadania z poprzednich zajęć. Jeśli pracujesz na nowym stanowisku, pobierz swoje repozytorium, uruchom PSQL na Dockerze. Połącz się za pomocą DataGripa z bazą oraz wgraj swoje skrypty.

SQL part 5

Tranzakcje

Tranzakcje bazadonowe są pojedynczą jednostką, zawierającą w sobie zadania np. dodawania, usuwania, aktualizowania danych. Ich głównym celem jest zachowanie spójności i trwałości danych. Jeśli jedno zadanie podczas całej tranzakcji nie powiedzie się, cała tranzakcja zostanie wycofana, co oznacza że aplikowane zmiany zostaną cofnięte, a baza wróci do stanu przed rozpoczecia tranzakcji. Najpopularniejszym przykładem zilustrowania potrzeb stosowania tranzakcji, są operacje wykonywane na potrzeby przelewów. Operacja przelewu bankowego składała by się ze zmniejszenia salda naszego konta, oraz powiększenia salda konta osoby do której wysyłamy przelew. Nie powodzenie jednej z tych operacji spodowałoby rozsynchronizowanie danych, ale i przedewszystkich realne szkody dla klientów. Dzięki zastosowaniu tranzakcji, obie operacje nie doszłoby do skutku, przez co uchronilibyśmy się od strat.

Podczas tworzenia tranzakcji powinniśmy trzymać się ze ścisłego zdefiniowago zbioru zasad ACID, które gwarantuje poprawne przetwarzanie tranzakcji. Tranzakcje powinny spełniać:

1. (Atomicity) Atomowość - każda transakcja jest niepodzielnym zbiorem operacji. Albo wykonają się wszystkie zadania, albo żadne
2. (Consistency) Spójność - po wykonaniu tranzakcji, baza danych powinno zostać spójna
3. (Isolation) Izolacje - tranzakcje powinny być od siebie niezależne i nie wpływać wzajemnie na siebie
4. (Durability) Trwałość - dane na których operowała tranzakcja powinny zostać trwale zapisane, nawet w przypadku wystąpienie awarii (bazy, sprzętu)

BEGIN

Aby rozpocząć tranzakcję należy użyć komendy BEGIN TRANSACTION. Po użyciu tej komendy, rozpoczyna się sesja tranzakcji, do której będą dodawane przez nas operacje SQL.

W poniższym przykładzie rozpoczynamy tranzakcję, w której dodajemy nowy adres do tabeli address.

BEGIN TRANSACTION;

INSERT INTO 
  address (street_name, building_number, flat_number, city, zip_code, country)
VALUES
  ('Grunwaldzka', '2a', '3', 'Gdansk', '80-000', 'Poland');

Uwaga! Do tego czasu dane adresowe nie będą widoczne dla innych użytkowników i sesji, dopóki zmian nie potwierdzimy, za pomocą komendy COMMIT.

COMMIT

Komenda potwierdza wykonanie wszystkich operacji i zapisuje zmiany w bazie danych. Gwarantuje ona również trwałość danych podczas awarii.

Rozszerzony przykład dodania nowego wpisu adresowego wraz z zatwierdzeniem operacji:

BEGIN TRANSACTION;

INSERT INTO 
  address (street_name, building_number, flat_number, city, zip_code, country)
VALUES
  ('Grunwaldzka', '2a', '3', 'Gdansk', '80-000', 'Poland');

COMMIT;  

ROLLBACK

Jeśli z kolei podczas tranzakcji, wystąpi błąd lub zdecydujemy się na wycofawanie zmian, to zamiast używania komendy COMMIT należy uzyć komendy ROLLBACK. Komenda ta cofnie wszystkie naniesione zmiany.

Przykład dla operacji dodowania danych adresowych (przyjmijmy że wszystkie kolumny w tabeli są typem text), jeden z insertowanych atrybutów ma podany niepoprawny typ:

BEGIN TRANSACTION;

INSERT INTO 
  address (street_name, building_number, flat_number, city, zip_code, country)
VALUES
  ('Władysława IV', '45b', '3', 'Gdynia', '81-000', 'Poland');

INSERT INTO 
  address (street_name, building_number, flat_number, city, zip_code, country)
VALUES
  ('Grunwaldzka', 2, '3', 'Gdansk', '80-000', 'Poland');

Następnie by wycofać tranzakcję wystarczy wpisać:

ROLLBACK;

Nadawanie oraz zarządzenie uprawnieniami

CREATE ROLE

W porównaniu do innych DBMS, w PSQL nie tworzymy osobno kont użytkowników, które można przypisać do ról. Role same w sobie reprezentują konta. Istnieje również możliwość grupowania ról. Tworząc rolę jest ona dostępną dla wszystkich baz danych na klastrze. Dlatego też rolom powinniśmy przypisywać również uprawnienia dla zdefiniowanego przez nas zakresu dostępu, którym może być ograniczenie do możliwości wykonania danych zapytań tylko na jednej bazie, tabeli itd.

Przykład stworzenia nowej roli, która uprawnia do zalogowania się do bazy danych:

CREATE ROLE lele
LOGIN 
PASSWORD 'foobar';

Jednakże, stworzona przez nas powyżej rola nie ma możliwości wykonania żadnych operacji na bazie danych. Musimy jej nadać uprawnienia.

GRANT

Podstawową komendą SQL służącą do nadawania uprawnień jest GRANT. Uprawnienia można nadać na różne obiekty w bazie danych np. na samą baze, tabele, kolumne, funkcje, scheme itd.

Poniżej zaprezentowane zostały przykłady nadania różnych uprawnień dla roli lele:

Przykład nadania wszystkich możliwych uprawnień oraz akcji do bazy danych. Dla DB, możliwymi uprawnieniami jest nadania możliwości połączenia do klastra, tworzenia baz, tabel, scheme oraz tabel tymczasowych.

GRANT ALL PRIVILEGES ON DATABASE taxidb TO lele;

Przykład nadania samego uprawnienia do tworzenia baz, tabel, scheme oraz tabel tymczasowych dla roli lele:

GRANT CREATE ON DATABASE taxidb TO lele;

Przykład nadania uprawnienia do wykonywania operacji SELECT na wszystkich tabelach w scheme data dla roli lele:

GRANT SELECT ON ALL TABLES IN SCHEMA data TO lele;

Przykład nadania uprawnień do wykonywania komend SELECT oraz INSERT na tabeli address dla roli lele:

GRANT SELECT, INSERT ON address TO lele;

Przykład nadania uprawnień do wykonywania komendy SELECT na kolumnie street w tabeli address:

GRANT SELECT (street) ON address TO lele;

Przykład nadania uprawnienia umożliwiającego wywołanie funkcji check_age():

GRANT EXECUTE ON FUNCTION check_age() TO lele;

Przykład nadania uprawnienia do dostępu do obiektów wewnątrz scheme data:

GRANT USAGE ON SCHEMA data TO lele;

Uwaga! Uprawnienia do usuwania tabel oraz modyfikacji ich struktur nie mogą zostać nadane dla ról! Te operacje mogą wykonać tylko ownerzy tabel oraz role z uprawnieniami superuser. Ownerem obiektu jest rola, która stworzyła dany obiekt. Domyślnie superuser w PSQL jest rola postgres, ta sama którą wykorzystujemy do logowania wewnątrz kontenera. Poniżej znajduje się przykład stworzenia roli z uprawnieniami superusera:

CREATE ROLE superlele
SUPERUSER 
LOGIN 
PASSWORD 'foobar123';

Jednakże rolę superuser można stworzyć za pomocą tylko i wyłącznie z wykorzystaniem innej roli superusera.

REVOKE

Komenda REVOKE z kolei służy do odebrania wcześniej nadanych uprawnień dla roli. Składnia jest zbliżona do komendy GRANT, z tą różnicą że zamiast używania słowa kluczowego TO, używamy FROM.

Przykład zabrania uprawnień do wykonywanie operacji SELECT oraz INSERT na tabeli address dla roli lele:

REVOKE SELECT, INSERT ON address FROM lele;

Zadania

Pamiętaj by zapisać wykonane skrypty w swoim repozytorium. Na potrzeby dzisiejszych zajęć do folderu migrations dodaj i pracuj na pliku V5__lab08.sql. Kontynuujemy pracę nad bazą danych z poprzedniego laboratorium.

1. Napisz skrypty w których:
   • wykonasz kilka poprawnie zrealizowanych tranzakcji, które naniosą zmiany na bazę danych
   • wykonasz skrypt który się niepowiedzie z wycofaniem tranzakcji
2. Stwórz użytkownika w bazie danych z uprawnieniami read-write, a następnie zabierz mu wszystkie uprawnienia do zapisu.