Bezpieczeństwo Procedury składowane Funkcje i Wyzwalacze

1. BezpieczeństwoProcedury składowaneFunkcje i Wyzwalacze Wykład 8 Prowadzący: dr Paweł Drozda

2. Użytkownicy – dostęp do danych (1) • Użytkownik bazy danych – osoba lub aplikacja, mająca dostęp do części danych zgromadzonych w bazie • Uprawnienia – zakres czynności, które użytkownik może wykonać na bazie danych • Uwierzytelnianie – weryfikacja użytkownika (w MySQL –na podstawie loginu i hasła zapisanego w bazie)

3. Użytkownicy – dostęp do danych (2) • Dwa typy ograniczenia działań: • Ograniczenie dostępu do poszczególnych danych (tabele, bazy danych) • Ograniczenie wykonywania poszczególnych operacji (SELECT, UPDATE, itd.) • Mechanizmy nakładające ograniczenia: • System przywilejów • Role bazodanowe (tylko Oracle)

4. Przywileje • Prawo wykonywania przez użytkownika określonej akcji w bazie danych lub dostępu do określonego obiektu • Przykłady przywilejów: • Wstawianie do bazy • Usuwanie z bazy • Modyfikowanie rekordów • Przeglądanie rekordów

5. Zarządzanie kontami – MySQL (1) • Tworzenie użytkownika – bez żadnych przywilejów CREATE USER nazwa IDENTIFIED BY [PASSWORD] ‘hasło’; • Usuwanie użytkownika DROP USER nazwa; • Nadanie hasła SET PASSWORD [for nazwa]=PASSWORD(‘nowehaslo’); Przykład: SET PASSWORD FOR Stefan = PASSWORD(‘qwerty’);

6. Zarządzanie kontami – MySQL (2) • Zmiana nazwy użytkownika RENAME USER staranazwa TO nowanazwa; • Nadawanie przywilejów – GRANT GRANT rodzajePrzywilejów[(nazwyKolumn)] ON nazwyObiektów TO listaUzytkowników [IDENTIFIED BY [PASSWORD] ‘haslo’] [WITH opcje];

7. GRANT – przywileje (1) • Możliwe przywileje ALL – nadanie wszystkich przywilejów oprócz GRANT OPTION ALTER – możliwość modyfikacji struktury DELETE – możliwość usunięcia danych INDEX – możliwość tworzenia i usuwania indeksu INSERT – możliwość wstawiania danych CREATE – możliwość tworzenia tabel GRANT OPTION – możliwość nadawania uprawnień

8. GRANT – przywileje (2) • Możliwe przywileje (cd) CREATE USER – możliwość tworzenia użytkowników CREATE VIEW – możliwość tworzenia perspektyw DROP – możliwość usuwania tabel LOCK TABLES – możliwość blokowania SHOW DATABASES – możliwość obejrzenia wszystkich baz danych w systemie UPDATE – możliwość uaktualnień

9. GRANT, przywileje – przykłady GRANT SELECT, DELETE, INSERT, UPDATE ON *.* TO student; GRANT SHOW DATABASES ON *.* TO student; GRANT ALTER, DROP, CREATE ON bazka.* TO student;

10. GRANT – obiekty • Rodzaje obiektów • *.* - dostęp do wszystkich baz danych w systemie • Nazwa.* - dostęp do bazy o nazwie Nazwa • Nazwa – dostęp do tabeli o nazwie Nazwa w aktualnej bazie • Baza.Tabela – dostęp do tabeli Tabela w bazie danych Baza

11. GRANT obiekty - przykłady GRANT INSERT ON Biblioteka.* TO Bibliotekarz; GRANT UPDATE(zarobki) ON Uni.Pracownik TO Płace; GRANT ALL ON *.* TO Administrator; Use Pizzeria GRANT SELECT ON Zamowienia TO Kelner;

12. GRANT – użytkownicy (1) • Używając GRANT dla istniejącego użytkownika – zmiana przywilejów • GRANT dla nieistniejącego użytkownika – powstaje nowy użytkownik z przywilejami określonymi w poleceniu • Hasło • gdy powstaje nowy użytkownik bez hasła – logowanie bez hasła • Gdy ze słowem PASSWORD ‘hasło’ – hasło musi być zakodowane funkcją PASSWORD • Bez PASSWORD ‘hasło’ – hasło podane jako tekst

13. GRANT – użytkownicy (2) • Użytkownik PUBLIC – przywileje nadane dla wszystkich użytkowników • Przykłady DROP USER Szukacz; GRANT SELECT ON *.* TO Szukacz; CREATE USER Tworca IDENTIFIED BY ‘wielki’; GRANT CREATE ON nowa.* TO Tworca; GRANT ALL ON *.* TO ADMIN IDENTIFIED BY PASSWORD ‘6af4442460ade563’; GRANT SELECT ON Test.* TO PUBLIC;

14. GRANT - opcje • GRANT OPTION – możliwość nadawania posiadanych uprawnień innym użytkownikom • MAX_QUERIES_PER_HOUR – max liczba zapytań na godzinę • MAX_UPDATES_PER_HOUR – max liczba aktualizacji na godzinę • MAX_CONNECTIONS_PER_HOUR = max liczba połączeń na godzinę • MAX_USER_CONNECTIONS – max liczba jednoczesnych połączeń jednego użytkownika • Gdy parametry zero – brak ograniczeń

15. Usuwanie przywilejów • REVOKE – składnia REVOKE rodzajePrzywilejów ON nazwyObiektów FROM użytkownicy; • Przykład: REVOKE ALTER, DROP on Baza.* FROM Tworca; REVOKE ALL, GRANT OPTION FROM student;

16. Procedury składowane • Zbiór instrukcji SQL wykonywanych jako jedno polecenie • Możliwości: • przekazywania parametrów do procedury • Wywołanie większości poleceń SQL (również innych procedur) • Dodanie fragmentu logiki po stronie bazy danych • Aby móc utworzyć procedurę składowaną – niezbędny przywilej CREATE ROUTINE

17. Procedury składowane - rodzaje • Procedury • Wywołane poleceniem CALL • Wynik w formie tabeli • Odwołuje się do referencji (IN, OUT) • Może używać poleceń SQL • Może wywoływać procedury i funkcje • Funkcje • Mogą być wbudowane w polecenie SQL • Zwracają pojedyncze wartości • Przekazywanie parametrów • Brak możliwości wywołania poleceń dotyczących tabel • Nie może odwoływać się do procedur

18. Funkcje – składnia (1) • Tworzenie funkcji CREATE FUNCTION nazwa(lista parametrow) RETURNS typ zwracanej zmiennej polecenia funkcji • Blok BEGIN – END Gdy występuje w funkcji więcej niż jedna instrukcja – grupowanie w blok BEGIN – END • Zmienne w funkcjach Zwykłe – poprzedzone @ Lokalne – deklarowane przy użyciu DECLARE, aktualne tylko w bloku BEGIN END DECLARE zmienna1, zmienna2 , … typDanych [DEFAULT wartosc]

19. Funkcje – składnia (2) • Pętle w MySQL REPEAT ciagInstrukcji UNTIL warunek END REPEAT; Opuszczenie pętli – LEAVE Wymuszenie kolejnej iteracji – ITERATE WHILE warunek DO instrukcje; END WHILE; • CASE CASE wyrażenie WHEN wartość1 THEN polecenia; WHEN wartość2 THEN polecenia; WHEN wartość3 THEN polecenia; else polecenia; END CASE;

20. Funkcje – składnia (3) • Usuwanie DROP FUNCTION [IF EXISTS] nazwa • Pokazanie składni funkcji SHOW CREATE FUNCTION nazwa • Wyświetlenie statusu funkcji SHOW FUNCTION STATUS • Informacje na temat funkcji Tabela information_schema.routines

21. Funkcja – przykład(1) delimiter // CREATE FUNCTION silnia(n INT) RETURNS int(11) BEGIN DECLARE i INT DEFAULT 1; DECLARE silnia INT DEFAULT 1; REPEAT SET i=i+1; SET silnia=silnia*i; UNTIL i>=n END REPEAT; RETURN silnia; END// delimiter ;

22. Funkcja – przykład(2) CREATE FUNCTION silnia1(n INT) RETURNS int BEGIN DECLARE i INT DEFAULT 1; DECLARE silnia INT DEFAULT 1; BEGIN IF (n=0) THEN RETURN 1; ELSE REPEAT SET i=i+1; SET silnia=silnia*i; UNTIL i>=n END REPEAT; RETURN silnia; END IF; END

23. Funkcja – przykład(3) delimiter // CREATE FUNCTION shorten(s VARCHAR(255), n INT) RETURNS VARCHAR(255) BEGIN IF ISNULL(s) THEN RETURN ''; ELSEIF n<15 THEN RETURN LEFT(s, n); ELSE IF CHAR_LENGTH(s) <= n THEN RETURN s; ELSE RETURN CONCAT(LEFT(s, n-10), ' ... ', RIGHT(s, 5)); END IF; END IF; END// delimiter ;

24. Procedury – składnia • Tworzenie CREATE PROCEDURE (IN|OUT|INOUT zmienne typ) Pola IN określają parametry wejściowe Pola OUT określają parametry zwracane • Pozostałe elementy – tak jak w funkcjach (zamiast FUNCTION należy wstawić PROCEDURE)

25. Procedury – przykład(1) • CREATE PROCEDURE Studenci(OUT ile int) SELECT COUNT(*) INTO ile FROM Student Wywołanie CALL Studenci(@a); SELECT @a; • CREATE PROCEDURE pobierz (out nazwa VARCHAR(50), in skrot VARCHAR(5))BEGINSELECT opis INTO nazwa FROM skroty WHERE skrot_id=skrot LIMIT 1;END

26. Wyzwalacze • Pozwalają wykonywać ciąg poleceń SQL przed lub po jednym z poleceń INSERT, UPDATE, DELETE wykonanym na ustalonej tabeli • Przyporządkowane do konkretnej tabeli • Unikalne nazwy dla tej samej tabeli • Brak możliwości wywołania CALL, poleceń transakcji wewnątrz wyzwalacza • Wywoływany automatycznie przy zajściu zdarzenia

27. Wyzwalacze - składnia(1) • Tworzenie Tylko dla użytkowników z uprawnieniami SUPER, od wersji 5.1.6 nowe uprawnienie TRIGGER CREATE TRIGGER nazwa BEFORE|AFTER INSERT|UPDATE|DELETE ON tabela FOR EACH ROW ciag polecen; • Dostęp do kolumn rekordu • OLD.nazwakolumny – wartość przed wykonaniem polecenia • NEW.nazwakolumny – wartość po wykonaniu polecenia • Usuwanie wyzwalaczy • DROP TRIGGER nazwatabeli.nazwawyzwalacza • Wyświetlanie • SHOW TRIGGERS

28. Wyzwalacze - przykład Tabela emps(emp_id, emp_name, dept_id, dept_name, salary) DELIMITER // CREATE TRIGGER odwrocony BEFORE INSERT ON emps FOR EACH ROW BEGIN SET new.emp_name := REVERSE(new.emp_name); END; // DELIMITER ;

29. Wyzwalacze – przykład (2) CREATE trigger emps_befBEFORE INSERT ON empsFOR EACH ROW BEGIN declare namelength numeric;SET namelength = length( new.emp_name ) ;SET new.salary = new.salary * namelength;END

30. Wyzwalacze – przykład (3) CREATE TABLE test1(a1 INT); CREATE TABLE test2(a2 INT); CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY); CREATE TABLE test4( a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b4 INT DEFAULT 0 ); delimiter // CREATE TRIGGER testref BEFORE INSERT ON test1 FOR EACH ROW BEGIN INSERT INTO test2 SET a2 = NEW.a1; DELETE FROM test3 WHERE a3 = NEW.a1; UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1; END; //

31. Wyzwalacze – przykład (3) cd. delimiter ; INSERT INTO test3 (a3) VALUES (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL); INSERT INTO test4 (a4) VALUES (0), (0), (0), (0), (0), (0), (0), (0), (0), (0); INSERT INTO test1 VALUES (1), (3), (1), (7), (1), (8), (4), (4);