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ę