wyk8

Bezpieczeństwo
Procedury składowane
Funkcje i Wyzwalacze
Wykład 8
Prowadzący: dr Paweł Drozda
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)
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)
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
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’);
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];
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ń
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ń
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
 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)
 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
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;
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ń
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;
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
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
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]
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;
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
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 ;
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)
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 ;
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)
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
 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
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
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 ;
Wyzwalacze – przykład (2)
CREATE trigger emps_bef BEFORE INSERT ON
emps
FOR EACH ROW
BEGIN declare namelength numeric;
SET namelength = length( new.emp_name ) ;
SET new.salary = new.salary * namelength;
END
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; //
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);