Bezpieczeństwo
Procedury składowane
Funkcje i Wyzwalacze
Wykład 8
Prowadzący: dr Paweł Drozda
Użytkownicy  dostęp do danych (1)
oðUżytkownik bazy danych  osoba lub
aplikacja, mająca dostęp do części
danych zgromadzonych w bazie
oðUprawnienia  zakres czynnoÅ›ci, które
użytkownik może wykonać na bazie
danych
oðUwierzytelnianie  weryfikacja
użytkownika (w MySQL  na podstawie
loginu i hasła zapisanego w bazie)
Użytkownicy  dostęp do danych (2)
oðDwa typy ograniczenia dziaÅ‚aÅ„:
Ograniczenie dostępu do poszczególnych
danych (tabele, bazy danych)
Ograniczenie wykonywania poszczególnych
operacji (SELECT, UPDATE, itd.)
oðMechanizmy nakÅ‚adajÄ…ce ograniczenia:
System przywilejów
Role bazodanowe (tylko Oracle)
Przywileje
oðPrawo wykonywania przez użytkownika
określonej akcji w bazie danych lub
dostępu do określonego obiektu
oðPrzykÅ‚ady przywilejów:
Wstawianie do bazy
Usuwanie z bazy
Modyfikowanie rekordów
Przeglądanie rekordów
ZarzÄ…dzanie kontami  MySQL (1)
oð Tworzenie użytkownika  bez żadnych
przywilejów
CREATE USER nazwa IDENTIFIED BY [PASSWORD]
 hasło ;
oð Usuwanie użytkownika
DROP USER nazwa;
oð Nadanie hasÅ‚a
SET PASSWORD [for
nazwa]=PASSWORD( nowehaslo );
Przykład:
SET PASSWORD FOR Stefan = PASSWORD( qwerty );
ZarzÄ…dzanie kontami  MySQL (2)
oðZmiana nazwy użytkownika
RENAME USER staranazwa TO nowanazwa;
oðNadawanie przywilejów  GRANT
GRANT rodzajePrzywilejów[(nazwyKolumn)]
ON nazwyObiektów TO listaUzytkowników
[IDENTIFIED BY [PASSWORD]  haslo ]
[WITH opcje];
GRANT  przywileje (1)
oð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ń
GRANT  przywileje (2)
oð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ń
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;
GRANT  obiekty
oð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
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;
GRANT  użytkownicy (1)
oð UżywajÄ…c GRANT dla istniejÄ…cego użytkownika 
zmiana przywilejów
oð GRANT dla nieistniejÄ…cego użytkownika 
powstaje nowy użytkownik z przywilejami
określonymi w poleceniu
oð 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
GRANT  użytkownicy (2)
oðUżytkownik PUBLIC  przywileje nadane
dla wszystkich użytkowników
oð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;
GRANT - opcje
oð GRANT OPTION  możliwość nadawania
posiadanych uprawnień innym użytkownikom
oð MAX_QUERIES_PER_HOUR  max liczba
zapytań na godzinę
oð MAX_UPDATES_PER_HOUR  max liczba
aktualizacji na godzinÄ™
oð MAX_CONNECTIONS_PER_HOUR = max
liczba połączeń na godzinę
oð MAX_USER_CONNECTIONS  max liczba
jednoczesnych połączeń jednego
użytkownika
oð Gdy parametry zero  brak ograniczeÅ„
Usuwanie przywilejów
oðREVOKE  skÅ‚adnia
REVOKE rodzajePrzywilejów ON
nazwyObiektów FROM użytkownicy;
oðPrzykÅ‚ad:
REVOKE ALTER, DROP on Baza.* FROM
Tworca;
REVOKE ALL, GRANT OPTION FROM student;
Procedury składowane
oð Zbiór instrukcji SQL wykonywanych jako jedno
polecenie
oð 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
oð Aby móc utworzyć procedurÄ™ skÅ‚adowanÄ… 
niezbędny przywilej CREATE ROUTINE
Procedury składowane - rodzaje
oð 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
oð 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
Funkcje  składnia (1)
oðTworzenie funkcji
CREATE FUNCTION nazwa(lista parametrow) RETURNS typ
zwracanej zmiennej
polecenia funkcji
oð Blok BEGIN  END
Gdy występuje w funkcji więcej niż jedna instrukcja 
grupowanie w blok BEGIN  END
oðZmienne w funkcjach
Zwykłe  poprzedzone @
Lokalne  deklarowane przy użyciu DECLARE, aktualne tylko w
bloku BEGIN END
DECLARE zmienna1, zmienna2 , & typDanych [DEFAULT wartosc]
Funkcje  składnia (2)
oð PÄ™tle w MySQL
REPEAT ciagInstrukcji
UNTIL warunek END REPEAT;
Opuszczenie pętli  LEAVE
Wymuszenie kolejnej iteracji  ITERATE
WHILE warunek DO instrukcje; END WHILE;
oð CASE
CASE wyrażenie
WHEN wartość1 THEN polecenia;
WHEN wartość2 THEN polecenia;
WHEN wartość3 THEN polecenia;
else polecenia;
END CASE;
Funkcje  składnia (3)
oð Usuwanie
DROP FUNCTION [IF EXISTS] nazwa
oð Pokazanie skÅ‚adni funkcji
SHOW CREATE FUNCTION nazwa
oð WyÅ›wietlenie statusu funkcji
SHOW FUNCTION STATUS
oð Informacje na temat funkcji
Tabela information_schema.routines
Funkcja  przykład(1)
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
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
Funkcja  przykład(3)
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
Procedury  składnia
oðTworzenie
CREATE PROCEDURE (IN|OUT|INOUT
zmienne typ)
Pola IN określają parametry wejściowe
Pola OUT określają parametry zwracane
oðPozostaÅ‚e elementy  tak jak w
funkcjach (zamiast FUNCTION należy
wstawić PROCEDURE)
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))
BEGIN
SELECT opis INTO nazwa FROM skroty WHERE
skrot_id=skrot LIMIT 1;
END
Wyzwalacze
oð PozwalajÄ… wykonywać ciÄ…g poleceÅ„ SQL przed
lub po jednym z poleceń INSERT, UPDATE,
DELETE wykonanym na ustalonej tabeli
oð Unikalne nazwy dla tej samej tabeli
oð Brak możliwoÅ›ci wywoÅ‚ania CALL, poleceÅ„
transakcji wewnÄ…trz wyzwalacza
Wyzwalacze - składnia(1)
oð Tworzenie
Tylko dla użytkowników z uprawnieniami SUPER
CREATE TRIGGER nazwa BEFORE|AFTER
INSERT|UPDATE|DELETE ON tabela
FOR EACH ROW ciag polecen;
oð DostÄ™p do kolumn rekordu
OLD.nazwakolumny  wartość przed wykonaniem polecenia
NEW.nazwakolumny  wartość po wykonaniu polecenia
oð Usuwanie wyzwalaczy
DROP TRIGGER nazwatabeli.nazwawyzwalacza
oð WyÅ›wietlanie
SHOW TRIGGERS
Wyzwalacze - przykład
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; |
Wyzwalacze  przykład 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);