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);