Bezpieczeństwo Procedury składowane Funkcje i Wyzwalacze
-
Upload
blossom-price -
Category
Documents
-
view
31 -
download
1
description
Transcript of Bezpieczeństwo Procedury składowane Funkcje i Wyzwalacze
![Page 1: Bezpieczeństwo Procedury składowane Funkcje i Wyzwalacze](https://reader035.fdocument.pub/reader035/viewer/2022062422/56813419550346895d9b0674/html5/thumbnails/1.jpg)
BezpieczeństwoProcedury składowaneFunkcje i Wyzwalacze
Wykład 8Prowadzący: dr Paweł Drozda
![Page 2: Bezpieczeństwo Procedury składowane Funkcje i Wyzwalacze](https://reader035.fdocument.pub/reader035/viewer/2022062422/56813419550346895d9b0674/html5/thumbnails/2.jpg)
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)
![Page 3: Bezpieczeństwo Procedury składowane Funkcje i Wyzwalacze](https://reader035.fdocument.pub/reader035/viewer/2022062422/56813419550346895d9b0674/html5/thumbnails/3.jpg)
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)
![Page 4: Bezpieczeństwo Procedury składowane Funkcje i Wyzwalacze](https://reader035.fdocument.pub/reader035/viewer/2022062422/56813419550346895d9b0674/html5/thumbnails/4.jpg)
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
![Page 5: Bezpieczeństwo Procedury składowane Funkcje i Wyzwalacze](https://reader035.fdocument.pub/reader035/viewer/2022062422/56813419550346895d9b0674/html5/thumbnails/5.jpg)
Zarządzanie kontami – MySQL (1) Tworzenie użytkownika – bez żadnych
przywilejówCREATE 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’);
![Page 6: Bezpieczeństwo Procedury składowane Funkcje i Wyzwalacze](https://reader035.fdocument.pub/reader035/viewer/2022062422/56813419550346895d9b0674/html5/thumbnails/6.jpg)
Zarządzanie kontami – MySQL (2)
Zmiana nazwy użytkownikaRENAME USER staranazwa TO nowanazwa;
Nadawanie przywilejów – GRANTGRANT rodzajePrzywilejów[(nazwyKolumn)]
ON nazwyObiektów TO listaUzytkowników [IDENTIFIED BY [PASSWORD] ‘haslo’] [WITH opcje];
![Page 7: Bezpieczeństwo Procedury składowane Funkcje i Wyzwalacze](https://reader035.fdocument.pub/reader035/viewer/2022062422/56813419550346895d9b0674/html5/thumbnails/7.jpg)
GRANT – przywileje (1) Możliwe przywileje
ALL – nadanie wszystkich przywilejów oprócz GRANT OPTION
ALTER – możliwość modyfikacji strukturyDELETE – możliwość usunięcia danychINDEX – możliwość tworzenia i usuwania
indeksuINSERT – możliwość wstawiania danych CREATE – możliwość tworzenia tabelGRANT OPTION – możliwość nadawania
uprawnień
![Page 8: Bezpieczeństwo Procedury składowane Funkcje i Wyzwalacze](https://reader035.fdocument.pub/reader035/viewer/2022062422/56813419550346895d9b0674/html5/thumbnails/8.jpg)
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 tabelLOCK TABLES – możliwość blokowaniaSHOW DATABASES – możliwość obejrzenia
wszystkich baz danych w systemieUPDATE – możliwość uaktualnień
![Page 9: Bezpieczeństwo Procedury składowane Funkcje i Wyzwalacze](https://reader035.fdocument.pub/reader035/viewer/2022062422/56813419550346895d9b0674/html5/thumbnails/9.jpg)
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;
![Page 10: Bezpieczeństwo Procedury składowane Funkcje i Wyzwalacze](https://reader035.fdocument.pub/reader035/viewer/2022062422/56813419550346895d9b0674/html5/thumbnails/10.jpg)
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
![Page 11: Bezpieczeństwo Procedury składowane Funkcje i Wyzwalacze](https://reader035.fdocument.pub/reader035/viewer/2022062422/56813419550346895d9b0674/html5/thumbnails/11.jpg)
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 PizzeriaGRANT SELECT ON Zamowienia TO Kelner;
![Page 12: Bezpieczeństwo Procedury składowane Funkcje i Wyzwalacze](https://reader035.fdocument.pub/reader035/viewer/2022062422/56813419550346895d9b0674/html5/thumbnails/12.jpg)
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
![Page 13: Bezpieczeństwo Procedury składowane Funkcje i Wyzwalacze](https://reader035.fdocument.pub/reader035/viewer/2022062422/56813419550346895d9b0674/html5/thumbnails/13.jpg)
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;
![Page 14: Bezpieczeństwo Procedury składowane Funkcje i Wyzwalacze](https://reader035.fdocument.pub/reader035/viewer/2022062422/56813419550346895d9b0674/html5/thumbnails/14.jpg)
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ń
![Page 15: Bezpieczeństwo Procedury składowane Funkcje i Wyzwalacze](https://reader035.fdocument.pub/reader035/viewer/2022062422/56813419550346895d9b0674/html5/thumbnails/15.jpg)
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;
![Page 16: Bezpieczeństwo Procedury składowane Funkcje i Wyzwalacze](https://reader035.fdocument.pub/reader035/viewer/2022062422/56813419550346895d9b0674/html5/thumbnails/16.jpg)
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
![Page 17: Bezpieczeństwo Procedury składowane Funkcje i Wyzwalacze](https://reader035.fdocument.pub/reader035/viewer/2022062422/56813419550346895d9b0674/html5/thumbnails/17.jpg)
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
![Page 18: Bezpieczeństwo Procedury składowane Funkcje i Wyzwalacze](https://reader035.fdocument.pub/reader035/viewer/2022062422/56813419550346895d9b0674/html5/thumbnails/18.jpg)
Funkcje – składnia (1)
Tworzenie funkcjiCREATE FUNCTION nazwa(lista parametrow) RETURNS typ
zwracanej zmiennejpolecenia funkcji
Blok BEGIN – ENDGdy występuje w funkcji więcej niż jedna instrukcja –
grupowanie w blok BEGIN – END
Zmienne w funkcjachZwykłe – poprzedzone @Lokalne – deklarowane przy użyciu DECLARE, aktualne tylko w
bloku BEGIN ENDDECLARE zmienna1, zmienna2 , … typDanych [DEFAULT wartosc]
![Page 19: Bezpieczeństwo Procedury składowane Funkcje i Wyzwalacze](https://reader035.fdocument.pub/reader035/viewer/2022062422/56813419550346895d9b0674/html5/thumbnails/19.jpg)
Funkcje – składnia (2) Pętle w MySQL
REPEAT ciagInstrukcjiUNTIL warunek END REPEAT;Opuszczenie pętli – LEAVEWymuszenie kolejnej iteracji – ITERATEWHILE warunek DO instrukcje; END WHILE;
CASECASE wyrażenie
WHEN wartość1 THEN polecenia;WHEN wartość2 THEN polecenia;WHEN wartość3 THEN polecenia;else polecenia;
END CASE;
![Page 20: Bezpieczeństwo Procedury składowane Funkcje i Wyzwalacze](https://reader035.fdocument.pub/reader035/viewer/2022062422/56813419550346895d9b0674/html5/thumbnails/20.jpg)
Funkcje – składnia (3) Usuwanie
DROP FUNCTION [IF EXISTS] nazwa
Pokazanie składni funkcjiSHOW CREATE FUNCTION nazwa
Wyświetlenie statusu funkcjiSHOW FUNCTION STATUS
Informacje na temat funkcjiTabela information_schema.routines
![Page 21: Bezpieczeństwo Procedury składowane Funkcje i Wyzwalacze](https://reader035.fdocument.pub/reader035/viewer/2022062422/56813419550346895d9b0674/html5/thumbnails/21.jpg)
Funkcja – przykład(1)delimiter //CREATE FUNCTION silnia(n INT) RETURNS int(11)
BEGINDECLARE 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 ;
![Page 22: Bezpieczeństwo Procedury składowane Funkcje i Wyzwalacze](https://reader035.fdocument.pub/reader035/viewer/2022062422/56813419550346895d9b0674/html5/thumbnails/22.jpg)
Funkcja – przykład(2)CREATE FUNCTION silnia1(n INT) RETURNS int BEGIN
DECLARE i INT DEFAULT 1;DECLARE silnia INT DEFAULT 1;
BEGINIF (n=0) THEN RETURN 1;
ELSEREPEAT
SET i=i+1;SET silnia=silnia*i;
UNTIL i>=n END REPEAT;RETURN silnia;
END IF;END
![Page 23: Bezpieczeństwo Procedury składowane Funkcje i Wyzwalacze](https://reader035.fdocument.pub/reader035/viewer/2022062422/56813419550346895d9b0674/html5/thumbnails/23.jpg)
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;ELSERETURN CONCAT(LEFT(s, n-10), ' ... ', RIGHT(s, 5));
END IF; END IF;
END//delimiter ;
![Page 24: Bezpieczeństwo Procedury składowane Funkcje i Wyzwalacze](https://reader035.fdocument.pub/reader035/viewer/2022062422/56813419550346895d9b0674/html5/thumbnails/24.jpg)
Procedury – składnia
TworzenieCREATE PROCEDURE (IN|OUT|INOUT
zmienne typ)Pola IN określają parametry wejściowePola OUT określają parametry zwracane
Pozostałe elementy – tak jak w funkcjach (zamiast FUNCTION należy wstawić PROCEDURE)
![Page 25: Bezpieczeństwo Procedury składowane Funkcje i Wyzwalacze](https://reader035.fdocument.pub/reader035/viewer/2022062422/56813419550346895d9b0674/html5/thumbnails/25.jpg)
Procedury – przykład(1) CREATE PROCEDURE Studenci(OUT ile int)
SELECT COUNT(*) INTO ile FROM StudentWywołanieCALL 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
![Page 26: Bezpieczeństwo Procedury składowane Funkcje i Wyzwalacze](https://reader035.fdocument.pub/reader035/viewer/2022062422/56813419550346895d9b0674/html5/thumbnails/26.jpg)
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
![Page 27: Bezpieczeństwo Procedury składowane Funkcje i Wyzwalacze](https://reader035.fdocument.pub/reader035/viewer/2022062422/56813419550346895d9b0674/html5/thumbnails/27.jpg)
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
![Page 28: Bezpieczeństwo Procedury składowane Funkcje i Wyzwalacze](https://reader035.fdocument.pub/reader035/viewer/2022062422/56813419550346895d9b0674/html5/thumbnails/28.jpg)
Wyzwalacze - przykładTabela emps(emp_id, emp_name, dept_id, dept_name, salary)DELIMITER //CREATE TRIGGER odwrocony BEFORE INSERT ON emps FOR EACH ROWBEGINSET new.emp_name := REVERSE(new.emp_name);END;//DELIMITER ;
![Page 29: Bezpieczeństwo Procedury składowane Funkcje i Wyzwalacze](https://reader035.fdocument.pub/reader035/viewer/2022062422/56813419550346895d9b0674/html5/thumbnails/29.jpg)
Wyzwalacze – przykład (2)
CREATE trigger emps_bef BEFORE INSERT ON empsFOR EACH ROW BEGIN declare namelength numeric;
SET namelength = length( new.emp_name ) ;
SET new.salary = new.salary * namelength;
END
![Page 30: Bezpieczeństwo Procedury składowane Funkcje i Wyzwalacze](https://reader035.fdocument.pub/reader035/viewer/2022062422/56813419550346895d9b0674/html5/thumbnails/30.jpg)
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; //
![Page 31: Bezpieczeństwo Procedury składowane Funkcje i Wyzwalacze](https://reader035.fdocument.pub/reader035/viewer/2022062422/56813419550346895d9b0674/html5/thumbnails/31.jpg)
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);