Aspekty systemowe baz danych

Systemowe aspekty baz
danych
Deklaracja zmiennej
• Zmienne mogą być wejściowe i wyjściowe
• Zmienne w T-SQL można deklarować za pomocą @:
declare @nazwisko varchar(20)
• Zapytanie z użyciem zmiennej:
select @nazwisko = Nazwisko
from Pracownik
where NrPrac = 22000
• Spowoduje wyświetlenie tego, co jest pod zmienną
@nazwisko
select @nazwisko
Zapytania sparametryzowane
• Deklaracja zmiennych:
declare @nazwisko varchar(20)
declare @NrPrac int
• Przypisanie wartości do zmiennej
set @NrPrac = 22000
• Zapytanie z parametrami
select @nazwisko = Nazwisko
from Pracownik
where NrPrac = @NrPrac
• Spowoduje wyświetlenie tego, co jest pod zmienną
@nazwisko:
select @nazwisko
Procedury składowane
• Procedura składowana jest zapisywana do bazy danych
• Przykład:
create procedure GetName
@NrPrac int,
@nazwisko varchar(20) output
as
begin
select @nazwisko = Nazwisko
from Pracownik
where NrPrac = @NrPrac
End
• Wywołanie powyższej procedury składowanej
declare @nazwisko varchar(20)
declare @NrPrac int
set @NrPrac = 22000
exec GetName @NrPrac, @nazwisko output
select @nazwisko
Procedury składowane
• Przykład:
create procedure GetName
@NrPrac int,
@nazwisko varchar(20) output,
@Zarobki int output
as
begin
if (@NrPrac < 10000)
begin
select @nazwisko = Nazwisko from Pracownik
where NrPrac = @NrPrac
select @Zarobki = Zarobki from Pracownik
where NrPrac = @NrPrac
end
end
Procedury składowane
Wywołanie procedury:
• Deklaracja zmiennych:
declare @nazwisko varchar(20)
declare @NrPrac int
declare @Zarobki int
• Przypisanie zmiennej:
set @NrPrac = 3990
• Wywołanie procedury:
exec GetName @NrPrac, @nazwisko output,
@Zarobki output
• Wyświetlenie wartości:
select @nazwisko , @Zarobki
Funkcje
• Wyróżniamy dwa rodzaje funkcji:
– skalarne;
– tablicowe.
Funkcje skalarne
• Przykład funkcji skalarnej:
create function GetNameFun (@NrPrac int)
RETURNS nchar(20)
as
begin
declare @nazwisko nchar(20)
select @nazwisko = Nazwisko
from Pracownik
where NrPrac = @NrPrac
return @nazwisko
End
• Funkcja skalarna zwraca pojedynczą wartość
• Wywołanie funkcji:
declare @nazwisko varchar(20)
set @nazwisko = dbo.GetNameFun(22000)
Funkcje tablicowe
• Przykład funkcji tablicowej:
create function GetNameTable (@NrPrac int)
RETURNS table
as
return (select Nazwisko
from Pracownik
where NrPrac = @NrPrac)
• Funkcja tablicowa zwraca tablicę
• Wywołanie funkcji:
select * from GetNameTable(22000)
Procedury składowane (MySQL)
• Procedura składowana jest zapisywana do bazy danych
• Przykład:
DELIMITER $$
create procedure GetName (Nr int)
Begin
//Deklaracja zmiennych
declare Naz varchar(30);
declare I varchar(20);
// Pobieranie danych
select Nazwisko,Imie INTO Naz,I
from Pracownik
where NrPrac = Nr;
// Wypisanie danych
select Naz, I;
end$$
DELIMITER ;
• Wywołanie powyższej procedury składowanej
call GetName(5111);
Procedury składowane (MySQL)
• Procedura z parametrem zewnętrznym:
DELIMITER $$
create procedure GetName (OUT Naz varchar(20), Nr int)
begin
select Nazwisko INTO Naz
from Pracownik
where NrPrac = Nr;
end$$
DELIMITER ;
• Wywołanie powyższej procedury składowanej
set @Naz = '';
call GetName(@Naz, 5111);
select @Naz;
Procedury składowane (MySQL)
• Procedura z parametrem zewnętrznym:
DELIMITER $$
create procedure GetName (OUT Naz varchar(20), Nr int)
Begin
IF (Nr < 5000) THEN
select Nazwisko INTO Naz
from Pracownik
where NrPrac = Nr;
END IF;
end$$
DELIMITER ;
• Wywołanie powyższej procedury składowanej
set @Naz = '';
call GetName(@Naz, 5111);
select @Naz;
Funkcje (MySQL)
• Funkcje są zapisywane do bazy danych.
• W odróżnieniu od procedur składowanych zwracają jakąś wartość.
• Przykład:
DELIMITER $$
create function GetNameFunction (Nr int)
returns varchar(50)
begin
declare Naz varchar(20);
select Nazwisko INTO Naz
from Pracownik
where NrPrac = Nr;
return Naz;
end$$
DELIMITER ;
• Wywołanie powyższej funkcji
set @s = GetNameFunction(3588);
select @s;
Dynamiczny SQL
• Umożliwia dynamiczne tworzenie zapytań (na
poziomie wykonywania programu);
• Przykład zapytania:
declare @zm_zapytanie varchar(100)
declare @zm_tabela varchar(100)
declare @zm_kolumny varchar(100)
set @zm_kolumny = 'Imie, Nazwisko'
set @zm_tabela = ' Pracownik'
set @zm_zapytanie = ' select '+ @zm_kolumny + ' from ' +
@zm_tabela
• Wywołanie:
execute sp_sqlexec @zm_zapytanie
lub
exec( @zm_zapytanie)
Dynamiczny SQL
• Funkcja tworząca dynamiczne zapytanie
create procedure GetQuery
@query nvarchar(200),
@NrPrac int
as
begin
declare @result varchar(200)
set @result = @query + ' where NrPrac = ' +
CAST(@NrPrac as varchar(20))
execute sp_sqlexec @result
End
• Wywołanie
execute GetQuery 'select * from Pracownik ' , 22000
Procedury systemowe w SQL Server
• Zaczynają się od sp_
• Utworzenie nowego typu za pomoca procedury
systemowej
exec sp_addtype PESEL ,'varchar(11)', 'NOT
NULL'
• Odczytanie tekstu procedury składowanej:
exec sp_helptext 'GetName'
• Sprawdzanie parametrów triggera
exec sp_helptrigger Osoba
Kursory
declare @Nazwisko varchar(20)
declare @Imie varchar(20)
declare @NrPrac int
declare kurPlaca cursor for
select Imie, Nazwisko, NrPrac from Pracownik
open kurPlaca
fetch next from kurPlaca into @Imie, @Nazwisko, @NrPrac
while @@FETCH_STATUS = 0
begin
select @Imie, @Nazwisko, @NrPrac
if (@NrPrac = 22000)
begin
select @Imie = 'NewName'
update Pracownik set Imie = @Imie where current of kurPlaca
end
fetch next from kurPlaca into @Imie, @Nazwisko, @NrPrac
End
close kurPlaca
deallocate kurPlaca
Indeksy
• Indeks określony na atrybucie A pewnej relacji jest mechanizmem,
który pozwala na efektywne wyszukiwanie krotek o ustalonej
wartości składowej atrybutu A.
• Indeks składa się z uporządkowanej kolekcji: wartości atrybutu A
oraz adresu fizycznego rekordu odpowiadającego temu atrybutowi.
• Indeksy stosuje się w dużych tabelach, nakłada się na takie atrybuty
(z reguły o typie int), na których selekcja jest prowadzona
szczególnie często.
• Przykładowo zakładamy, że taka operacja będzie wykonywana
bardzo często:
select * from Pracownik
where NrPrac = 23000
Indeksy
• Tworzenie indeksu:
create index IndeksNrPrac on Pracownik(NrPrac)
• Optymalizator zapytań najpierw sięga do indeksu
IndeksNrPrac i znajduje wartości adresów fizycznych
rekordów spełniających kryteria.
• Indeksy po wielu atrybutach:
create index IndeksNrPracNazw on
Pracownik(NrPrac, Nazwisko)
• Usuwanie indeksów (SQL3):
drop index IndeksNrPracNazw
• Usuwanie indeksów (T-SQL):
drop index Pracownik.IndeksNrPracNazw
Rodzaje indeksów na przykładzie
SQL Server
• Indeks klastrowy:
– może być jeden w tabeli;
– dane znajdują się w liściach;
– utworzenie klucza głównego na kolumnę w tabeli powoduje
dodanie indeksu klastrowego na tę kolumnę;
create clustered index nazwa_indeksu on tabela (kolumna1,…)
• Indeks nieklastrowy:
– może być więcej niż jeden w tabeli;
– w liściach nie ma danych tylko są wskaźniki do danych;
create nonclustered index nazwa_indeksu on tabela (kolumna1,…)
Indeks klastrowy
Indeks nieklastrowy
Indeksy
• Wybór właściwego indeksu zależy od
projektanta bazy danych.
• Przyspiesza wykonywanie zapytań
pobierających;
• Operacje wstawiania, usuwania i
modyfikacji bazy danych są
skomplikowane przy zastosowaniu
indeksów. Musi być reorganizacja
indeksów.
Perspektywy (Views)
• Perspektywy są bytami podobnymi do tabel.
• Różnica jest taka, że:
– Tabele znajdują się fizycznie w bazie danych.
– Jedynie definicja perspektywy znajduje się w bazie
danych.
– Wyliczenie (materializacja) następuje w momencie
użycia perspektywy. Wynik jest “konsumowany” i
następnie kasowany.
– Wada: czas ewaluacji perspektyw + czas ewaluacji
zapytań używających perspektyw bez optymalizacji
często nieakceptowalny
Perspektywy
• Przykład
Create view PracLokal as
SELECT lok.Lokalizacja, lok.Wydzial AS LokWydz,
pr.Imie, pr.Nazwisko, pr.NrPrac, pr.RokPracy,
pr.Wydzial AS PrWydz, pr.Zarobki
FROM Lokalizacja as lok INNER JOIN
Pracownik AS pr ON lok.Wydzial = pr.Wydzial
• Widok można traktować jak tabelę:
select * from PracLokal
where Nazwisko='Zaorski'
Modyfikowanie widoków
• Modyfikowanie widoków polega na modyfikacji
tabel źródłowych na których widok się opiera.
• Modyfikowanie widoków jest możliwe tylko w
ściśle określonych sytuacjach:
- widok musi być oparty na klauzuli SELECT bez
operacji grupowania;
- klauzula WHERE nie może zawierać zapytania
dotyczącego modyfikowanej relacji ;
- klauzula SELECT musi być na tyle duża, żeby
wstawić odpowiednie wartości do relacji, która ma być
zmodyfikowana. Atrybuty modyfikowanej tabeli,
których nie ma w perspektywie, powinny dać się
wypełnić wartościami domyślnymi albo NULL;
Modyfikowanie widoków
• To zadziała, bo modyfikowana jest jedna tabela:
insert into PracLokal (Imie, Nazwisko, NrPrac)
values ('Damian', 'Matysiak', 2490)
• To nie zadziała, bo jest próba modyfikacji więcej
niż jednej tabeli:
insert into PracLokal (Imie, Nazwisko, NrPrac, Lokalizacja)
values ('Damian', 'Matysiak', 2490, 'Lublin')
Zastosowanie perspektyw
• Ograniczenie danych do takich, które są
istotne w konkretnym zastosowaniu.
• Ograniczenie dostępu do obiektów,
ochrona prywatności.
• Ukrywanie przed użytkownikami struktury
bazy danych.
• Hurtownie danych: analiza informacji
gromadzonych z heterogenicznych źródeł.
Autoryzacja
• Autoryzacja może być definiowana na
następujących poziomach:
– Serwera bazy danych;
– Bazy danych;
– Obiektów w bazie danych;
• Dodanie nowego użytkownika:
exec sp_addlogin 'Piotr','My-Passw‘
CREATE LOGIN Piotr1 WITH PASSWORD = 'My-Pasw1'
Role na serwerze bazy danych
Fixed server role
Description
sysadmin
Can perform any activity in SQL Server
serveradmin
Can set server-wide configuration options, shut
down the server
setupadmin
Can manage linked servers and startup
procedures
securityadmin
Can manage logins and CREATE DATABASE
permissions, also read error logs and change
passwords
processadmin
Can manage processes running in SQL Server
dbcreator
Can create, alter, and drop databases
diskadmin
Can manage disk files
bulkadmin
Can execute BULK INSERT statements
Procedury systemowe obsługujące role serwera
bazy danych
sp_addsrvrolemember
Adds a login as a member of a fixed server
role
sp_dropsrvrolemember
Removes an SQL Server login, Windows
user or group from a fixed server role
sp_helpsrvrole
Returns a list of the fixed server roles
sp_helpsrvrolemember
Returns information about the members of
fixed server roles
sp_srvrolepermission
Returns the permissions applied to a fixed
server role
Dodanie użytkownika
• Dodanie nowego użytkownika:
exec sp_addlogin 'Piotr',‘PiotrHaslo‘
CREATE LOGIN Piotr1 WITH PASSWORD = ‘Piotr1Haslo'
• Dodanie roli serwera dla użytkownika:
exec sp_addsrvrolemember Piotr,'sysadmin'
Role bazy danych
W systemie istnieją standardowe role bazy danych. Nie
można ich zmieniać, ale można tworzyć nowe.
db_owner
Has all permissions in the database
db_accessadmin
Can add or remove user IDs
db_securityadmin
Can manage all permissions, object
ownerships, roles and role memberships
db_ddladmin
Can issue ALL DDL, but cannot issue GRANT,
REVOKE, or DENY statements
db_backupoperator
Can issue DBCC, CHECKPOINT, and BACKUP
statements
db_datareader
Can select all data from any user table in the
database
db_datawriter
Can modify any data in any user table in the
database
db_denydatareader
Cannot select any data from any user table in
the database
db_denydatawriter
Cannot modify any data in any user table in the
database
Procedury składowane obsługujące role bazy
danych
sp_addrole
Creates a new database role in the current
database
sp_addrolemember
Adds a user to an existing database role in
the current database
sp_dbfixedrolepermission
Displays permissions for each fixed
database role
sp_droprole
Removes a database role from the current
database
sp_helpdbfixedrole
Returns a list of fixed database roles
sp_helprole
Returns information about the roles in the
current database
sp_helprolemember
Returns information about the members of
a role in the current database
sp_droprolemember
Removes users from the specified role in
the current database
Dodanie użytkownika
• Dodanie nowego użytkownika do bazy
danych:
CREATE USER Piotr
• Dodanie roli serwera dla użytkownika:
exec sp_addrolemember 'db_owner',Piotr
exec sp_addrolemember 'db_ddladmin',Piotr
Definiowanie uprawnień na poziomie obiektów i
instrukcjach
• Uprawnienia można definiować na
następujących obiektach lub instrukcjach:
• SELECT, INSERT, UPDATE, DELETE, EXECUTE
• CREATE DATABASE, CREATE DEFAULT, CREATE
FUNCTION, CREATE PROCEDURE, CREATE RULE,
CREATE TABLE, CREATE VIEW
• BACKUP DATABASE, BACKUP LOG
Dodanie użytkownika oraz jego roli przykład
• Dodanie nowego użytkownika
exec sp_addlogin 'Nowy','NoweHaslo'
CREATE USER Nowy
exec sp_addrolemember 'db_ddladmin',Nowy
• Nie ma uprawnień do wykonania zapytania, a
więc nie można zrobić:
select * from Bar
Instrukcja GRANT
•
GRANT – umożliwia użytkownikowi lub roli wykonywanie operacji
• Przykład:
grant select on Pracownik to Nowy
• Użytkownik Nowy może teraz wykonać instrukcję:
select * from Pracownik
• Przykład2:
grant select on Pracownik(Imie, Nazwisko) to Nowy
• Użytkownik Nowy może teraz wykonać instrukcję:
select Imie from Pracownik
• Nie może jednak wykonać operacji:
select * from Pracownik
Instrukcja GRANT
• Przykład3:
grant select on Pracownik to Nowy WITH GRANT
OPTION
• Użytkownik Nowy może teraz wykonać
instrukcję SELECT i może też dać
uprawnienia do wykonania tej instrukcji
innemu użytkownikowi.
• GRANT może być stosowany na każdym
poziomie autoryzacji.
Instrukcja REVOKE
• Instrukcja REVOKE powoduje odebranie
prawa do wykonywania instrukcji.
• Przykład:
grant select on Pracownik(Imie, Nazwisko) to Nowy
revoke select on Pracownik from Nowy
• Powoduje odebranie użytkownikowi prawa
do wykonywania instrukcji SELECT na
tabeli Pracownik.
Dziękuję