Transcript Slajd 1

Bazy Danych
Programowanie w SQL
Wojciech St. Mościbrodzki
[email protected]
Prawa dostępu do obiektów w mySQL

Główne zadania DBMS:




Identyfikacja i autoryzacja użytkowników
Definiowanie poziomów dostępu
Zarządzanie prawami dostępu do obiektów
Obsługa sytuacji wyjątkowych
Prawa dostępu w mySQL

Prawa dotyczą obiektów bazodanowych:



Połączeń, baz, tablic, triggerów
Użytkowników (w mySQL - w kontekście logowania!)
Polecenia związane z zarządzaniem prawami:







CREATE USER
RENAME USER
DROP USER
GRANT
REVOKE
SHOW GRANTS
SET PASSWORD
GRANT

Polecenie GRANT służy do nadawania:



PRAW
na OBIEKTY
określonym UŻYTKOWNIKOM
GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ...
ON [object_type] priv_level
TO user_specification [, user_specification] ...
[REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]
[WITH with_option ...]



PRAWA (przywileje) to operacje dozwolone, np. CREATE, INSERT, SELECT,
EXECUTE, FILE, RELOAD itp.
OBIEKTY to tabele, kolumny, funkcje, procedury
UŻYTKOWNIK jest określony kontekstem: login+hasło+adres(połączenie)
PRAWA (przywileje)

W MySQL definiuje się następujące poziomy praw:





global (odnosi się do całości obiektów serwera) – GRANT <prawo> on *.*
database (tylko dla danej bazy danych) – GRANT <prawo> on moja.*
table (tylko dla jednej tablicy w bazie) – GRANT <prawo> on moja.auto
column (tylko dla określonej kolumny) – GRANT <prawo> on moja.auto(id)
W zależności od poziomu prawa, są one inaczej pamiętane w
systemowej bazie danych:




global level – tablica mysql.user
database level – tablica mysql.db
table level – tablica mysql.tables_priv
column level – tablica mysql.columns_priv
GRANT – SQL vs mySQL





mySQL nadaje prawa w kontekście logowania (nie tylko usera)
mySQL implementuje prawa globalne i bazodanowe
mySQL rozszerza zakres praw
w mySQL usunięcie tabeli nie usuwa praw(!); prawa-zombie
mySQL pozwala na nadanie prawa INSERT do określonych kolumn,
przy jednoczesnym zabraniu go na inne (użyte zostaną wartości
DEFAULT)
Prawa dostępu - mySQL

Najczęściej używane:


Prawa do zarządzania:


ALL, ALTER, DELETE, DROP, INSERT, SELECT, TRIGGER, UPDATE
CREATE USER, CREATE VIEW, CREATE, INDEX, EXECUTE, RELOAD,
SHOW DATABASES, SHOW VIEW, SHUTDOWN, SUPER,
Pozostałe:

ALTER ROUTINE, CREATE ROUTINE, CREATE TEMPORARY TABLES,
EVENT, FILE, LOCK TABLES, PROCESS, REPLICATION CLIENT,
REPLICATION SLAVE,
GRANT SELECT
ON moja.samochod
TO wojmos
WITH GRANT OPTION

Specjalne prawo:

GRANT OPTION – możliwość przekazywania uprawnień
Implementacja kontekstu użytkownika


Prawa użytkownika przypisuje się parze (user, host)
Identyfikacja odbywa się za pomocą pary (user, password)
CREATE USER user_specification
[IDENTIFIED BY [PASSWORD] 'password']

UWAGA:


niestosowanie klauzuli IDENTIFIED BY umożliwia logowania bez hasła
IDENTIFIED BY PASSWORD umożliwia ominięcie podawania hasła otwartym
tekstem
mysql> create user wojmos;
mysql> select user, host, password from mysql.user;
+--------+-----------+-------------------------------------------+
| user
| host
| password
|
+--------+-----------+-------------------------------------------+
| root
| localhost | *E35C24480CDB73576312748BA0DCF2E4648A7E6C |
| wojmos | %
|
|
+--------+-----------+-------------------------------------------+
CREATE USER, DROP USER i SET PASSWORD





CREATE USER – tworzenie użytkowników
DROP USER – usuwanie użytkowników (z prawami!)
SET PASSWORD – ustawianie haseł
RENAME USER – zmiana nazwy
GRANT USAGE
CREATE USER user_specification
[IDENTIFIED BY [PASSWORD] 'password']
DROP USER user [, user] ...
RENAME USER old_user TO new_user
SET PASSWORD [FOR user] = {
'encrypted password‘ |
PASSWORD('some password') |
OLD_PASSWORD('some password')
}
GRANT USAGE ON *.* TO ‘wojmos'@'%.wojmos.com' IDENTIFIED BY 'newpass';
Hasła podawane w CREATE USER i SET PASSWORD
mysql> create user 'sample1';
mysql> create user 'sample2' identified by 'lipton';
mysql> create user 'sample3' identified by password
'*90E462C37378CED12064BB3388827D2BA3A9B689';
mysql> create user 'sample3' identified by password 'lipton';
ERROR 1372 (HY000): Password hash should be a 41-digit hexadecimal number
mysql> create user 'sample4';
mysql> set password for sample4 = PASSWORD('lipton');
mysql> create user 'sample5';
mysql> set password for sample5 = OLD_PASSWORD('lipton');
mysql> select user, host, password from mysql.user;
+---------+-----------+-------------------------------------------+
| user
| host
| password
|
+---------+-----------+-------------------------------------------+
| root
| localhost | *E35C24480CDB73576312748BA0DCF2E4648A7E6C |
| sample2 | %
| *D0CD8B382FCD172A273DE23563C530DB2E12D059 |
| wojmos | %
|
|
| sample1 | %
|
|
| sample3 | %
| *90E462C37378CED12064BB3388827D2BA3A9B689 |
| sample4 | %
| *D0CD8B382FCD172A273DE23563C530DB2E12D059 |
| sample5 | %
| 58ac86b863e0cbbf
|
+---------+-----------+-------------------------------------------+
Przykład działania GRANT OPTION
REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ...
ON [object_type] priv_level
FROM user [, user] ...
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...
GRANT ALL ON *.* TO wojmos with GRANT OPTION
root
GRANT ALL ON sample.* TO sample1 with GRANT OPTION
wojmos
GRANT ALL ON sample.* TO sample2
sample1
CREATE TABLE X(id int)
sample2
DROP USER SAMPLE1
CREATE TABLE Y(id int)
sample2
<===========?
Kontekst użytkownika

mySQL umożliwia dość szerokie i elastyczne zarządzanie prawami:
mysql> create user koza@localhost;
mysql> create user koza identified by 'lipton';
mysql> create user koza@'192.168.0.110';
mysql> create user koza@'10.10.0.';
mysqlmysql> select user, host, password from mysql.user where user like
'koza';
+------+---------------+-------------------------------------------+
| user | host
| password
|
+------+---------------+-------------------------------------------+
| koza | localhost
|
|
| koza | %
| *D0CD8B382FCD172A273DE23563C530DB2E12D059 |
| koza | 192.168.0.110 |
|
| koza | 10.10.0.
|
|
+------+---------------+-------------------------------------------+
Szyfrowanie tablic – bezpieczeństwo w mySQL

Rozważmy typową bazę, z dostępem via Internet / PHP:


Typowy (powszechne rozwiązanie) plik show.php:


USER(id int, login char(10), pass char(10), firstname char(20), lastname char(50))
http://show.php?q=‘select * from product where id=7’
Typowy atak:

http://show.php?q=‘select * from user’
Tworzenie własnych funkcji i procedur
Tworzenie własnych funkcji i procedur


Funkcje są definiowanymi przez użytkownika rozszerzeniami SQL.
MySQL pozwala na 3 mechanizmy:




Funkcje wkompilowywane w kod serwera
Funkcje w postaci kompilowanych bibliotek
Funkcje i procedury składowane (stored functions/procedures)
(Zwykły użytkownik zasadniczo używa jedynie 3 drogi)
Stored function (funkcja składowana) jest wywoływana tak, jak
zwykła funkcja SQL.
select concat('Ala', ' ma kota');
select count(*) from user;
select moja_funkcja();

Stored procedure (procedura składowana) jest wywoływana przez
call
call moja_procedura();
Własne funkcje

CREATE PROCEDURE/FUNCTION

DROP PROCEDURE/FUNCTION

SHOW PROCEDURE/FUNCTION STATUS
mysql> show function status;
+-----------+---------+----------+----------------+---------------------+---------------------+---------------+---------+
| Db
| Name
| Type
| Definer
| Modified
| Created
| Security_type | Comment |
+-----------+---------+----------+----------------+---------------------+---------------------+---------------+---------+
| poligon
| nazwa
| FUNCTION | root@localhost | 2009-12-10 21:36:12 | 2009-12-10 21:36:12 | DEFINER
|
|
| populacja | exp2lvl | FUNCTION | root@localhost | 2009-08-21 16:35:32 | 2009-08-21 16:35:32 | DEFINER
|
|
+-----------+---------+----------+----------------+---------------------+---------------------+---------------+---------+
2 rows in set (0.00 sec)

SHOW CREATE PROCEDURE/FUNCTION

Procedury i funkcje są obiektami konkretnej bazy (od 5.0.1+)!
Procedury i funkcje są obiektami serwera (do 5.0.0)!

Funkcje vs procedury





Funkcje i procedury składowane mają podobną budowę i znaczenie
Funkcje wymagają określenia RETURNS
Funkcje składowane i UDF dzielą ze sobą przestrzeń nazw
Parametry funkcji są zawsze IN (nie mogą zmienić wartości)
Funkcje są wywoływane jak normalne funkcje SQL
CREATE [DEFINER] FUNCTION sp_name ([func_parameter[,...]]) RETURNS type
[special ...]
routine_body
func_parameter: name type
special: LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO
SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER
| INVOKER } | COMMENT 'string'
routine_body: SQL | BEGIN...END
Funkcje vs procedury





Funkcje i procedury składowane mają podobną budowę i znaczenie
Procedury nie określają RETURNS – nie zwracają wartości
Procedury przekazują wartości tylko przez zmienne
Parametry procedur mogą być IN, OUT lub INOUT
Procedury wywoływane są tylko poprzez call
CREATE [DEFINER] PROCEDURE sp_name ([proc_parameter[,...]]) [special...]
routine_body
proc_parameter: [ IN | OUT | INOUT ] name type
special: LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO
SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER
| INVOKER } | COMMENT 'string'
routine_body: SQL | BEGIN...END
Porównanie CREATE dla funkcji i procedur
CREATE
[DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
CREATE
[DEFINER = { user | CURRENT_USER }]
FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body
proc_parameter:
[ IN | OUT | INOUT ] param_name type
func_parameter:
type:
param_name type
Any valid MySQL data type
characteristic:
LANGUAGE SQL
| [NOT] DETERMINISTIC
READS SQL DATA | MODIFIES SQL DATA }
INVOKER }
| COMMENT 'string'
| { CONTAINS SQL | NO SQL |
| SQL SECURITY { DEFINER |
DELIMITER i jego znaczenie

Procedury i funkcje składają się zwykle z wielu linii kodu. Oznacza
to problem z interpretacją znaku ; po stronie klienta.

PRZYPOMINAMY: znak terminalny „;” powoduje natychmiastowe
wysłanie poleceń do serwera (w celu interpretacji). Druga jego rola
to oddzielanie kolejnych poleceń.
CREATE FUNCTION nazwa () RETURNS VARCHAR(20)
BEGIN
RETURN 'Hello world';
END;
w tym miejscu kod
zostanie wysłany do
serwera
CREATE FUNCTION nazwa () RETURNS VARCHAR(20)
BEGIN
RETURN 'Hello world';
powyższy fragment NIE JEST jednak poprawny!
DELIMITER – zmiana znaku terminalnego

WNIOSEK: musimy chwilowo zmienić znaczenie znaku ; (aby
przestał wysyłać kod do serwera).
DELIMITER ;;
nowy symbol terminalny
CREATE FUNCTION nazwa () RETURNS VARCHAR(20)
BEGIN
RETURN ‘Hello world';
END;
tworzymy funkcję
;;
wysyłamy do serwera
DELIMITER ;
stary symbol terminalny
koniec polecenia
(sprzątamy po sobie!)
Prosta funkcja składowana

Stworzenie funkcji
mysql> DROP FUNCTION IF EXISTS Hello;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+-------------------------------+
| Level | Code | Message
|
+-------+------+-------------------------------+
| Note | 1305 | FUNCTION Hello does not exist |
+-------+------+-------------------------------+
DELIMITER ;;
CREATE FUNCTION Hello() RETURNS VARCHAR(20)
BEGIN
RETURN 'Hello world';
END;
;;
DELIMITER ;
Prosta funkcja składowana

Stworzenie i wywołanie funkcji
DROP FUNCTION IF EXISTS Hello;
DELIMITER ;;
CREATE FUNCTION Hello() RETURNS VARCHAR(20)
BEGIN
RETURN 'Hello world';
END;
;;
DELIMITER ;
mysql> select hello();
+-------------+
| hello()
|
+-------------+
| Hello world |
+-------------+
1 row in set (0.00 sec)
Prosta procedura składowana

Stworzenie procedury
mysql> DROP PROCEDURE IF EXISTS Hello;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+--------------------------------+
| Level | Code | Message
|
+-------+------+--------------------------------+
| Note | 1305 | PROCEDURE Hello does not exist |
+-------+------+--------------------------------+
DELIMITER ;;
CREATE PROCEDURE Hello()
BEGIN
SELECT('Hello world');
END;
;;
DELIMITER ;
Prosta funkcja składowana

Stworzenie i wywołanie procedury
DROP PROCEDURE IF EXISTS Hello;
DELIMITER ;;
CREATE PROCEDURE Hello()
BEGIN
SELECT('Hello world');
END;
;;
DELIMITER ;
mysql> call hello();
+-------------+
| Hello world |
+-------------+
| Hello world |
+-------------+
1 row in set (0.00 sec)
Parametry przekazywane do obiektów składowanych


Funkcje – parametry zawsze są traktowane jako IN
Procedury – parametry mogą mieć typ IN, OUT lub INOUT




IN – parametr wejściowy, modyfikacja nie jest widoczna na zewnątrz (inicjalizuje
go obiekt wywołujący funkcję)
OUT – parametr wyjściowy, pełni rolę wartości zwracanej przez zmienną, ma
inicjalnie ustawianą wartość na NULL (inicjalizuje go funkcja)
INOUT - parametr wejściowo-wyjściowy, pełni rolę wartości zwracanej przez
zmienną, ma inicjalnie ustawianą wartość przez obiekt wywołujący funkcję
Dane do przykładu
mysql> select * from pracownik;
+--------+-----------+-----------+-------------+----------+
| Jan
| Kowalski | polski
| 73020103456 | Gdansk
|
| Jan
| Kowalski | niemiecki | 73020103456 | Gdansk
|
| Piotr | Nowak
| polski
| 43121102934 | Szczecin |
| Stefan | Kozlowski | polski
| 98101045777 | Warszawa |
| Piotr | Nowak
| angielski | 43121102934 | Szczecin |
| Jan
| Kurski
| polski
| 71101099348 | Gdansk
|
+--------+-----------+-----------+-------------+----------+
Parametry IN

Procedura z parametrem typu IN
CREATE PROCEDURE Sample(IN parametr char(30))
BEGIN
SELECT * FROM pracownik WHERE i LIKE parametr;
END;

Wywołanie
mysql> call Sample('Jan');
+------+----------+-----------+-------------+--------+
| i
| n
| j
| p
| miasto |
+------+----------+-----------+-------------+--------+
| Jan | Kowalski | polski
| 73020103456 | Gdansk |
| Jan | Kowalski | niemiecki | 73020103456 | Gdansk |
| Jan | Kurski
| polski
| 71101099348 | Gdansk |
+------+----------+-----------+-------------+--------+
3 rows in set (0.00 sec)
Parametry OUT

Procedura z parametrem typu OUT
CREATE PROCEDURE Sample(OUT parametr char(30))
BEGIN
SELECT count(*) FROM pracownik into parametr;
END;

Wywołanie
call sample(@zmienna); select @zmienna;
Query OK, 0 rows affected (0.00 sec)
+----------+
| @zmienna |
+----------+
| 6
|
+----------+
1 row in set (0.00 sec)
Parametry OUT

Parametr typu OUT ma inicjalną wartość NULL
CREATE PROCEDURE Sample(OUT parametr char(30))
BEGIN
SELECT parametr;
SELECT count(*) FROM pracownik into parametr;
END;

Wywołanie
mysql> call sample(@zmienna);
+----------+
| parametr |
+----------+
| NULL
|
+----------+
mysql> call sample('Jan');
ERROR 1414 (42000): OUT or INOUT argument 1 for routine wojmos.sample
is not a variable or NEW pseudo-variable in BEFORE trigger
Parametry OUT

Parametr typu OUT ma ZAWSZE inicjalną wartość NULL
CREATE PROCEDURE Sample(OUT parametr char(30))
BEGIN
SELECT parametr;
SELECT count(*) FROM pracownik into parametr;
END;

Wywołanie
mysql> select @a:=3; call sample(@a);
+-------+
| @a:=3 |
+-------+
|
3 |
+-------+
+----------+
| parametr |
+----------+
| NULL
|
+----------+
Parametry INOUT

Procedura z parametrem typu INOUT
CREATE PROCEDURE Sample(INOUT parametr char(30))
BEGIN
SELECT parametr;
SELECT count(*) FROM pracownik into parametr;
END;

Wywołanie (parametr inicjowany przez obiekt wołający!)
mysql> select @a:=3; call sample(@a);
+-------+
| @a:=3 |
+-------+
|
3 |
+-------+
+----------+
| parametr |
+----------+
| 3
|
+----------+
Parametry INOUT

Procedura z parametrem typu INOUT
CREATE PROCEDURE Sample(INOUT parametr char(30))
BEGIN
SELECT parametr;
SELECT count(*) FROM pracownik into parametr;
END;

Wywołanie (parametr inicjowany przez obiekt wołający!)
mysql> call sample(@a); select @a;
+----------+
| parametr |
+----------+
| 3
|
+----------+
+------+
| @a
|
+------+
| 6
|
+------+
Własne funkcje

SHOW PROCEDURE/FUNCTION STATUS
mysql> show function status;
+-----------+---------+----------+----------------+---------------------+---------------------+---------------+---------+
| Db
| Name
| Type
| Definer
| Modified
| Created
| Security_type | Comment |
+-----------+---------+----------+----------------+---------------------+---------------------+---------------+---------+
| poligon
| nazwa
| FUNCTION | root@localhost | 2009-12-10 21:36:12 | 2009-12-10 21:36:12 | DEFINER
|
|
| populacja | exp2lvl | FUNCTION | root@localhost | 2009-08-21 16:35:32 | 2009-08-21 16:35:32 | DEFINER
|
|
+-----------+---------+----------+----------------+---------------------+---------------------+---------------+---------+
2 rows in set (0.00 sec)
mysql> show function status;
+-----------+---------+----------+----------------+
| Db
| Name
| Type
| Definer
|
+-----------+---------+----------+----------------+
| poligon
| nazwa
| FUNCTION | root@localhost |
| populacja | exp2lvl | FUNCTION | root@localhost |
+-----------+---------+----------+----------------+
Budowa obiektów składowanych - zmienne

Zmienne w programach składowanych



zmienne sesyjne
zmienne lokalne (dla bloków BEGIN-END)
Zmienne lokalne:






deklarowane za pomocą DECLARE
muszą być deklarowane
istnieją tylko w kontekście swoich bloków
nie wymagają używania @
mogą być ustawiane za pomocą SET
mogą być ustawiane za pomocą SELECT i SELECT INTO
Zmienne w obiektach składowanych

Zliczamy byty o podanym imieniu w 2 tablicach
CREATE PROCEDURE Sample(IN parametr char(30))
BEGIN
DECLARE x1 CHAR(30) DEFAULT 0;
DECLARE x2 CHAR(30) DEFAULT 0;
SELECT count(*) into x1 FROM czlowiek where imie like parametr;
SELECT count(*) into x2 FROM pracownik where i like parametr;
select x1 as czlowiek, x2 as pracownik;
END;
mysql> call sample('jan');
+----------+-----------+
| czlowiek | pracownik |
+----------+-----------+
| 0
| 3
|
+----------+-----------+
1 row in set (0.00 sec)