Ćwiczenie 6

1. AUTORYZACJA DOSTĘPU

W SZBD Oracle dostęp do danych realizuje się przy użyciu poleceń grant i revoke. W celu udostępnienia obiektów innym użytkownikom systemu nadajemy im odpowiednie przywileje poleceniem grant o formacie:

grant przywileje

on obiekt

to użytkownik;

W przypadku relacji i perspektyw jest możliwe nadanie poniższych przywilejów:

select	odczyt danych z relacji
insert	wstawianie krotek do relacji
update	uaktualnianie krotek relacji
delete	usuwanie krotek relacji
alter	zmiana schematu relacji
index	tworzenie indeksów relacji
all	wszystkie

Dostęp do cudzej relacji wymaga, oprócz uzyskania odpowiedniego przywileju, poprzedzeniem nazwy relacji identyfikatorem jej właściciela. Długie i niewygodne nazwy mogą być zastępowane ich synonimami, które definiujemy za pomocą polecenie create synonym o następującym formacie:

create [public] synonym nazwa for obiekt;

public- synonim dostępny dla wszystkich użytkowników bazy danych. Prawo ich tworzenia posiada administrotor. Pominięcie słowa public spowoduje utworzenie synonim prywatnego użytkownika

obiekt - obiekt bazy danych.

Np.:

SQL> create synonym pr

• for d_inf2_4.prac2_4;

i odwołanie się do relacji poprzez synonim:

SQL> select * from pr;

Jeśli określony obiekt chcemy udostępnić wszystkim użytkownikom, to zamiast jego identyfikatora piszemy public, np.:

SQL>grant select

• on prac

• to public;

Nadanie przywileju jednemu użytkownikowi nie ogranicza możliwości jego nadania innemu, za wyjątkiem, gdy w poleceniu grant użyjemy klauzuli with grant option. Np.:

SQL>grant select

• on prac

• to d_inf2_4

• with grant option;

Synonim prywatny usuwamy poleceniem

SQL> drop synonym nazwa;

a publiczny - poleceniem:

SQL> drop public synonym nazwa;

Administrator SZBD jest jedynym użytkownikiem, który może rejestrować nowych użytkowników w systemie poleceniem o formacie:

grant przywilej to użytkownik identified by hasło;

najczęściej używanymi przywilejami to:

connect możliwość dołączenia się do SZBD

resource możliwość tworzenia obiektów obiektów bazy danych, np. relacji, liczników,

dba możliwość definiowania użytkowników oraz pełnego dostępu do obiektów i funkcji SZBD

W celu odwołania nadanego przywileju stosujemy polecenie revoke o formacie:

revoke przywileje

on relacja/perspektywa

from użytkownicy;

Najpierw musimy utworzyć usera:

create user stud1 identified by pass;

Nadać mu wszelkiej maści prawa:

grant create session, dba to stud1;

Podłączyć:

connect stud1/pass;

Zadania do samodzielnego wykonania

Zad. 1. W bazie danych zostało utworzonych 2 użytkowników stud1 i stud2. Jako użytkownik stud2 utworzyć relacje prac_stud2 zawierającą dane z relacji prac. Użytkownik stud1 może wyświetlać zawartość tej relacji poleceniem:

SQL> select * from prac_stud1;

CREATE TABLE prac_stud2 AS SELECT * FROM PRAC;

CREATE SYNONYM prac_stud1 FOR prac_stud2;

GRANT SELECT ON prac_stud2 TO stud1;

//select * from prac_stud1;

-- a nastepnie w celu ewentualnego zabrania

REVOKE SELECT ON prac_stud2 FROM stud1;

Zad. 2. Zmodyfikować prawa użytkownika stud1 do relacji prac_stud2 tak, aby mógł wykonywać na niej wszystkie operacje DML i DDL oraz nadawać przywileje innym użytkownikom do tej relacji.

DDL - Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:

CREATE - to create objects in the database

ALTER - alters the structure of the database

DROP - delete objects from the database

TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed

COMMENT - add comments to the data dictionary

RENAME - rename an object

DML - Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:

SELECT - retrieve data from the a database

INSERT - insert data into a table

UPDATE - updates existing data within a table

DELETE - deletes all records from a table, the space for the records remain

MERGE - UPSERT operation (insert or update)

CALL - call a PL/SQL or Java subprogram

EXPLAIN PLAN - explain access path to data

LOCK TABLE - control concurrency

GRANT prawo1, prawo2,…,prawoN ON prac_stud2 TO stud1;

GRANT prawo1, prawo2,…,prawoN ON prac_stud2 TO public;

II WSPÓŁBIEŻNA REALIZACJA TRANSAKCJI

Użytkownik może manipulować danymi za pomocą poleceń SQL*Plus tzw. Tryb ad hoc) lub za pośrednictwem aplikacji. Związane jest to z wystąpieniem określonej transakcji, którą możemy traktować jako funkcję przejścia bazy danych ze stanu S₁ do stanu S₂. Ponieważ z każdej bazy danych korzysta wielu użytkowników, ważne jest aby kilka transakcji było wykonywanych współbieżnie. Problem określania poprawnego uporządkowania akcji współbieżnie wykonywanych transakcji jest rozwiązywany trzema grupami metod:

• porządkowania wg etykiet czasowych,

• walidacji,

• blokowania (najczęściej używana).

Rozszerzeniem SZBD Oracle jest wyróżnienie dwóch klas transakcji - DML i DDL. Transakcje DML składają się z dowolnej liczby poleceń języka manipulowania danymi i zapytań i traktowane są niepodzielnie (atomowo). Transakcje DDL zawierają dokładnie jedno polecenie języka definiowania danych. Początkiem transakcji DDL jest jej pierwsze polecenie, a końcem jedno z poniższych zdarzeń:

• akceptacja, zrealizowana za pomocą wyrażenia commit, lub wycofanie - rollback;

• użycie polecenia DDL;

• niektóre błędy, w tym zakleszczenie transakcji (dead-lock);

• zakończenie sesji użytkownika (log off);

• błędy maszynowe.

Operacje wykonywane pomiędzy dwoma poleceniami commit - to pojedyncza transakcja. Oprócz blokad zakładanych niejawnie przez SZBD, użytkownik ma możliwość ustawienia blokady relacji za pomoca polecenia lock table o formacie:

lock table nazwa_relacji in tryb [nowait];

tryb - może przyjmować różne wartości - najczęściej share i exclusive,

nowait - powoduje natychmiastowe przerwanie próby blokowania relacji, jeśli nie może ona być założona, np.:

SQL>lock table zesp in exclusive mode;

Uwaga:

Jeśli chcemy częściowo zachować daną transakcję, można to zrobić przy użyciu tzw. punktu zachowania poleceniem savepoint. Polecenie to ma format:

Savepoint nazwa_punktu_zachowania;

III. ZWIĘKSZANIE EFEKTYWNOŚCI DOSTĘPU DO RELACJI

Efektywność dostępu do relacji podwyższa się poprzez indeksowanie plików dyskowych (przechowujących relacje), który to plik jest porządkowany wg wartości klucza o wartościach numerycznych, znakowych i stanowiących kombinację tych pól.

Najczęściej stosowanym jest indeks o strukturze B-drzewa, którego podstawą konstrukcji jest założenie, że ponieważ indeks jest plikiem, może posiadać on również swój indeks.

W systemie Oracle indeksy są implementowane za pomocą tzw. zrównoważonych B-drzew, będących rozszerzeniem B-drzew. Wyróżnia się 3 typy indeksów:

unique		zapewnia unikalność wartości atrybutów, na których został założony
non unique		zapewnia możliwie najszybszą realizację zapytań
compressed		typ domyślny, minimalizujący pamięć przeznaczoną na reprezentacje indeksu
non compressed		minimalizuje czas dostępu do indeksu
single column		założony na pojedynczym atrybucie
concatenated		na skonkatenowanych atrybutach

W celu utworzenia indeksu stosujemy polecenie create indeks o formacie:

create [unique[ index nazwa_indeksu

on relacja (atrybut1, atrybut2);

Przykład:

SQL>create unique index i_zesp

2 on prac (id_zesp);

Zadania do samodzielnego wykonania

Zad. 3. Utworzyć unikalny indeks na atrybucie nazwisko relacji prac. Co się stanie po wykonaniu poleceń:

CREATE UNIQUE INDEX nazwisko ON PRAC(nazwisko);

Zad. 4. Zdefiniować indeksy na odpowiednich atrybutach tak, aby przyspieszyć wykonanie poniższego polecenia:

SQL> select p.nazwisko, p.zatrudniony, s.nazwisko, s.zatrudniony

• from prac p, prac s

• where p.id_szefa=s.id_prac

• and p.placa_pod>s.placa_pod;

CREATE INDEX p ON PRAC(nazwisko,zatrudniony,szef,id_prac,placa_pod);

select p.nazwisko, p.zatrudniony, s.nazwisko, s.zatrudniony

from prac p, prac s

where p.szef=s.id_prac

and p.placa_pod>s.placa_pod;

LABORATORIUM z BAZ DANYCH

ORACLE

6

---