Sinonime

Dupa cum stiti sinonimul este un cuvant cu exact acelasi inteles cu un alt cuvant, adica un cuvant care poate fi folosit in locul altui cuvant

Similar in dialectul bazelor de date, administratorul unei baze de date poate defini nume echivalente pentru un obiect al bazei de date.

In principal vom defini un sinonim pentru un obiect al bazei de date pentru a simplifica referirea la acel obiect.

De exemplu pentru a interoga tabela1 din schema unui alt utilizator, fie acesta user1, atunci vom referi aceasta tabela prin prefixarea numelui tabelei cu numele utilizatorului in a carui schema se gaseste tabela, adica vom scrie user1.tabela1. Daca numele utilizatorului este insa RO_L2_SQL01_S12 iar tabela se numeste d_track_listings, va trebui sa scriem RO_L2_SQL01_S12.d_track_listings pentru a ne referi la acea tabela, ceea ce este destul de neplacut. Pentru aceasta vom defini un sinonim mai scurt pentru tabela respectiva.

Sintaxa comenzii de creare a unui sinonim este

CREATE [PUBLIC] SYNONYM nume_sinonim

FOR obiect

De exemplu:

CREATE SYNONYM ana_track

FOR RO_L2_SQL01_S12.d_track_listings

In continuare, vom putea folosi acest sinonim in locul numelui complet al tabelei.

Se pot defini sinonime pentru tabele, vederi, secvente, proceduri sau alte obiecte ale bazei de date.

Optiunea PUBLIC este folosita de catre administratorul bazei de date pentru a crea un sinonim accesibil tuturor utilizatorilor bazei de date. In mod implicit un sinonim este privat.

Stergerea unui sinonim se face cu comanda DROP SYNONYM

Drepturi si roluri

V-ati intrebat vreodata ce ar insemna ca elevii dintr-o scoala sa aiba acces liber la catalog si sa poata face orice modificare doresc in catalog? Dar daca orice utilizator conectat la internet ar avea acces nerestrictionat la baza de date a CIA, NASA, a unei banci si asa mai departe?

Evident, in viata reala accesul in anumite locuri este restrictionat. Daca faci parte dintr-un anumit grup restrans de persoane, ca de exemplu angajatii bancii, poti avea acces in anumite zone restrictionate sau la anumite resurse la care alte persoane nu au acces.

Ca si in lumea reala si in cazul bazelor de date trebuie sa putem defini o serie de drepturi pentru utilizatorii bazei de date, sau sa restrictionam accesul acestora la anumite obiecte ale bazei de date.

Controlul securitatii in Oracle se asigura prin specificarea: utilizatorilor bazei de date, schemelor, privilegiilor (drepturilor) si rolurilor.

Utilizatorii bazei de date si schemele

Fiecare baza de date are o lista de nume de utilizatori. Pentru a accesa baza de date un utilizator trebuie sa foloseasca o aplicatie si sa se conecteze cu un nume potrivit. Fiecarui nume de utilizator ii este asociata o parola. Orice utilizator are un domeniu de securitate care determina privilegiile si rolurile, cota de spatiu pe disc alocat si limitele de resurse ce le poate utiliza (timp CPU etc).

Privilegiile

Privilegiul este dreptul unui utilizator de a executa anumite instructiuni SQL. Privilegiile pot fi:

privilegii de sistem - permit utilizatorilor sa execute o gama larga de instructiuni SQL, ce pot modifica datele sau structura bazei de date. Aceste privilegii se atribuie de obicei numai administratorilor bazei de date.

privilegii de obiecte - permit utilizatorilor sa execute anumite instructiuni SQL numai in cadrul schemei sale, si nu asupra intregii baze de date.

Acordarea privilegiilor reprezinta modalitatea prin care acestea pot fi atribuite utilizatorilor. Exista doua cai de acordare explicit (privilegiile se atribuie in mod direct utilizatorilor) si implicit (prin atribuirea acestora unor roluri, care la randul lor sunt acordate utilizatorilor).

Rolurile

Rolurile sunt grupe de privilegii, care se atribuie utilizatorilor sau altor roluri. Rolurile permit:

Reducerea activitatilor de atribuire a privilegiilor. Administratorul bazei de date in loc sa atribuie fiecare privilegiu tuturor utilizatorilor va atribui aceste privilegii unui rol, care apoi va fi disponibil utilizatorilor;

Manipularea dinamica a privilegiilor. Daca se modifica un privilegiu de grup, acesta se va modifica in rolul grupului. Automat modificarea privilegiului se propaga la toti utilizatorii din grup;

Selectarea disponibilitatilor privilegiilor. Privilegiile pot fi grupate pe mai multe roluri, care la randul lor pot fi activate sau dezactivate in mod selectiv;

Proiectarea unor aplicatii inteligente. Se pot activa sau dezactiva anumite roluri functie de utilizatorii care incearca sa utilizeze aplicatia.

Un rol poate fi creat cu parola pentru a preveni accesul neautorizat la o aplicatie. Aceasta tehnica permite utilizarea parolei la momentul pornirii aplicatiei, apoi utilizatorii pot folosi aplicatia fara sa mai cunoasca parola.

Pentru acordarea unui drept unui anumit utilizator vasile se va folosi comanda GRANT. De exemplu, pentru a se conecta la baza de date, un utilizator trebuie sa aiba permisiunea de a crea o sesiune. Acest drept se aloca de catre un utilizator privilegiat (utilizatorul system de exemplu) prin comanda

GRANT CREATE SESSION TO vasile

Acum utilizatorul vasile se poate conecta la baza de date.

Revocarea unui drept unui anumit utilizator se face folosind comanda REVOKE ca in exemplul urmator:

REVOKE CREATE SESSION FROM vasile

Drepturile de sistem

Un drept de system permite unui utilizator sa efectueze anumite operatii asupra bazei de date precumexecutarea comenzilor DDL. Cele mai uzuale drepturi system sunt prezentate in tabelul urmator.

Tabelul II.10.1. Privilegii sistem

Acordarea drepturilor de sistem

Dupa cum am precizat acordarea drepturilor se face folosind comanda GRANT. In exemplul urmator se acorda cateva drepturi sistem utilizatorului ion

GRANT CREATE SESSION, CREATE USER, CREATE TABLE TO ion

Se poate de asemenea folosi optiunea WITH ADMIN OPTION care permite unui utilizator sa aloce si el drepturile primite cu aceasta optiune, mai departe, altor utilizatori:

GRANT EXECUTE ANY PROCEDURE TO ion WITH ADMIN OPTION;

Dreptul acordat utilizatorului ion, de a executa orice procedura poate fi acordata de acesta mai departe utilizatorului george. Pentru aceasta ion se va conecta la baza de date folosind comanda

CONNECT ion/test

unde ion este username-ul iar test este parola si apoi va acorda dreptul lui george

GRANT EXECUTE ANY PROCEDURE TO george;

Un drept se poate aloca tuturor utilizatorilor bazei de date folosin optiunea PUBLIC ca in urmatorul exemplu:

CONNECT system/manager

GRANT EXECUTE ANY PROCEDURE TO PUBLIC;

In acest moment orice utilizator al bazei de date are dreptul de a executa o procedura in orice schema.

Drepturile la nivel de obiect

Un drept la nivel de obiect permite unui utilizator sa execute anumite actiuni asupra obiectelor bazei de date, ca de exemplu executarea anumitor comenzi DML pe tabelele bazei de date. De exemplu GRANT INSERT ON adm.elevi permite unui utilizator sa insereze linii noi in tabela elevi din schema adm. Cele mai des intalnite drepturi la nivel de obiect sunt prezentate in tabelul urmator:

Tabelul II.10.2. Privilegii la nivel de obiect

Acordarea drepturilor la nivel de obiect

Veti utiliza de asemenea comanda GRANT. Exemplul urmator acorda utilizatorului ion dreptul de SELECT INSERT, si UPDATE pe tabela elevi si dreptul de SELECT asupra tabelei angajati

GRANT SELECT, INSERT, UPDATE ON adm.elevi TO ion;

GRANT SELECT ON profesori.angajati TO ion;

Urmatoarea comanda permite utilizatorului ion sa modifice doar valorile din coloanele prenume si adresa, din tabela elevi, utilizatorului ion

GRANT UPDATE (prenume,adresa) ON adm.elevi TO ion;

Folosind optiunea WITH GRANT OPTION veti permite utilizatorului sa acorde mai departe dreptul primit si altor utilizatori:

GRANT SELECT ON adm.elevi TO ion WITH GRANT OPTION;

Dreptul de a interoga tabela adm.elevi poate fi acum acordat de catre ion oricarui alt utilizator:

CONNECT ion/test

GRANT SELECT ON adm.elevi TO george;

Revocarea drepturilor la nivel de obiect se va face folosind comanda REVOKE. Urmatoarea comanda revoca dreptul de inserare de noi linii la tabela elevi utilizatorului ion

REVOKE INSERT ON elevi FROM ion;

Comanda va fi rulata din contul adm

Observatie! Daca am acordat un drept unui utilizator A folosind optiunea WITH GRANT OPTION, iar acest utilizatorul A a acordat si el la randul lui dreptul altor utilizatori B C si D, atunci cand vom revoca dreptul utilizatorului A, va fi revocat automat acel drept si tuturor utilizatorilor carora utilizatorul A le-a acordat acel drept, respectiv utilizatorilor B C si D

Gestiunea rolurilor

Dupa cum am precizat la inceputul capitolului, putem crea un rol, prin intermediul caruia vom putea acorda drepturi unui grup de utilizatori avand rolul respectiv, lucru mult mai usor decat acordarea drepturilor fiecarui utilizator separat.

De exemplu, in loc sa acordam drepturi de select insert si update mai multor utilizatori

GRANT SELECT, INSERT, UPDATE ON adm.elevi TO ion;

GRANT SELECT, INSERT, UPDATE ON adm.elevi TO vasile;

GRANT SELECT, INSERT, UPDATE ON adm.elevi TO gheorghe;

GRANT SELECT, INSERT, UPDATE ON adm.elevi TO maria;

GRANT SELECT, INSERT, UPDATE ON adm.elevi TO alin;

E mai comod sa cream un rol, sa acordam drepturi pentru acest rol si apoi sa acordam rolul respectiv celor cinci utilizatori. Vom scrie asadar:

CREATE ROLE profi;

GRANT SELECT, INSERT, UPDATE ON adm.elevi TO profi;

GRANT profi TO ion, vasile, gheorghe, maria, alin;

In orice moment putem sterge un rol folosind comanda DROP ROLE. Aceasta va duce la revocarea tuturor drepturilor acordate utilizatorilor prin intermediul acestui rol.

Sa dam un exemplu mai complex de acordare a drepturilor si privilegiilor. Sa presupunem ca rulam pe rand urmatoarele comenzi:

CONNECT hr/test;

CREATE ROLE r1;

CREATE ROLE r2;

GRANT SELECT, INSERT, DELETE ON hr.elevi TO r1

WITH GRANT OPTION;

GRANT DELETE, UPDATE ON hr.elevi TO r2

WITH GRANT OPTION;

GRANT r1 TO user1

GRANT r2 TO user2

GRANT CREATE VIEW TO user3 WITH GRANT OPTION

GRANT DELETE ON hr.elevi TO user3

GRANT UPDATE ON hr.elevi TO user4

CONNECT user2/pas2

GRANT DELETE ON hr.elevi TO user4

GRANT UPDATE ON hr.elevi TO user4

In acest moment utilizatorii au urmatoarele drepturi (figura II.10.1.):

Tabelul II.10.3.

Figura II.10.1. Schema de acordare a drepturilor

Daca acum stergem rolul r2:

DROP ROLE r2

utilizatorul user2 va pierde dreptul de DELETE si UPDATE asupra tabelei hr.elevi, si prin intermediul sau va pierde dreptul de DELETE si utilizatorul user4, care a primit acest drept de la user2. Desi user4 a primit de la user2 si dreptul de UPDATE, el nu va pierde acest drept deoarece a primit acest drept si direct de la utilizatorul SYSTEM Asadar dupa stergerea rolului r2, drepturile utilizatorilor sunt urmatoarele:

Tabelul II.10.4.

Gestiunea tranzactiilor

O tranzactie este un grup de comenzi SQL care sunt vazute ca o singura unitate. Imaginati-va o tranzactie ca un grup de comenzi SQL care nu pot fi separate, si al caror efect este in intregime salvat in baza de date, fie este in intregime anulat. Sa ne gandim de exemplu la efectuarea unui transfer bancar dintr-un cont in alt cont. O comanda UPDATE va efectua operatia de scadere a sumei de bani tranzactionata dintr-un cont, iar o alta comanda UPDATE va adauga suma respectiva la cel de al doilea cont. Daca ambele operatii decurg normal fara probleme, atunci ele vor deveni ambele permanente. Daca una dintre aceste doua comenzi esueaza (de exemplu nu poate fi contactata banca in care se depun banii) atunci ambele comenzi vor fi anulate. E normal sa renuntam la scaderea sumei de bani dintr-un cont, daca acestia nu pot fi depusi in celalalt cont, in caz contrar ar duce la pierderea banilor respectivi.

In general o tranzactie poate fi formata din mai multe comenzi INSERT UPDATE, si DELETE

Pentru a face permanenta o tranzactie folositi comanda COMMIT. Daca doriti sa renuntati la modificarile efectuate in cadrul unei tranzactii trebuie sa rulati o comanda ROLLBACK

Comanda ROLLBACK fara nici un parametru, incheie tranzactia curenta si renunta la toate modificarile facute in cadrul acestei tranzactii. Aveti insa posibilitatea definirii in cadrul unei tranzactii a unui asa numit punct de intoarcere, sau punct de salvare. Odata definit un astfel de punct de salvare, veti putea renunta doar la o parte din modificarile facute in cadrul tranzactiei curente.

Definirea unui punct de revenire se face cu comanda SAVEPOINT avand sintaxa:

SAVEPOINT nume_punct_de_revenire

Revenirea la un punct de revenire se face cu comanda ROLLBACK astfel:

ROLLBACK TO nume_punct_de_revenire

Definirea punctelor de revenire este utila in cazul unor tranzactii mari, cand in cazul in care faceti o greseala nu trebuie sa renuntati la toate operatiile din cadrul tranzactiei ci doar la o parte dintre acestea.

O tranzactie fiind un grup de comenzi SQL tratate ca un intreg, trebuie sa stabilim unde incepe o tranzactie si unde se termina aceasta.

O tranzactie incepe la intalnirea unuia dintre urmatoarele evenimente:

In momentul conectarii la baza de date si la inceperea rularii primei comenzi DML (INSERT UPDATE DELETE

La terminarea unei tranzactii anterioare si rularea urmatoarei comenzi DML.

O tranzactie se termina cand apare unul dintre urmatoarele evenimente:

La executarea unei comenzi COMMIT sau ROLLBACK (fara nici un parametru, intrucat ROLLBACK TO nu termina tranzactia ci doar revine la un punct precizat din cadrul tranzactiei curente)

La executarea unei comenzi DDL (CREATE ALTER DROP RENAME TRUNCATE), caz in care este executata automat comanda COMMIT

La executarea unei comenzi DCL (GRANT sau REVOKE) caz in care este executata automat comanda COMMIT

Va deconectati de la baza de date. Daca iesiti normal din SQL*Plus cu comanda Exit, sau dati Logout din Oracle Database Express Edition atunci are loc un COMMIT automat. Daca iesirea se face anormal, de exemplu in cazul unei pene de curent, atunci se executa in mod automat o comanda ROLLBACK

Executati o comanda DML care esueaza, caz in care are loc un ROLLBACK automat pentru acea singura comanda.

Sa experimentam acum modul de folosire a tranzactiilor.

Atentie In Oracle Database Express Edition toate comenzile sunt autocommit, si nu vor fi recunoscute comenzile COMMIT ROLLBACK sau SAVEPOINT. Pentru acest exercitiu puteti rula comenzile SQL in linia de comanda. Pentru aceasta alegeti din meniul Start Programs Oracle Database 10g Express Edition optiunea Run SQL Command Line. Se va deschide o fereastra in care va veti conecta la baza de date folosind comanda

CONECT

Introduceti username-ul (hr) si parola si in acest moment puteti rula orice comanda SQL.

Pentru a experimenta folosirea tranzactiilor vom crea urmatoarea tabela:

create table savepoint_test ( n number )

Inseram acum cateva linii in aceasta tabela:

insert into savepoint_test values (1);
insert into savepoint_test values (2);
insert into savepoint_test values (3);

Definim acum un punct de salvare

savepoint sp1;

si mai inseram cateva linii in tabela

insert into savepoint_test values (10);
insert into savepoint_test values (20);
insert into savepoint_test values (30);

Definim un nou punct de salvare

savepoint sp2;

si inseram in final inca trei linii

insert into savepoint_test values (100);
insert into savepoint_test values (200);
insert into savepoint_test values (300);

Verificam acum daca datele au fost inserate in tabela

select * from savepoint_test;

si vedem ca toate datele au fost inserate:

Figura II.11.1.

Revenim acum la punctul de revenire sp2

ROLLBACK TO sp2

si verificam continutul tabelei:

select * from savepoint_test;

Observati ca ultimele linii inserate dupa definirea punctului de salvare sp2 au fost sterse din tabela (figura II.11.2.).

Figura II.11.2.

Inseram alte trei linii

insert into savepoint_test values (111);
insert into savepoint_test values (222);
insert into savepoint_test values (333);

testam continutul tabelei:

select * from savepoint_test;

Figura II.11.3.

Revenim la punctul de salvare sp2

ROLLBACK TO sp2

si verificam continutul tabelei:

select * from savepoint_test;

Evident ultimele trei linii nu se mai gasesc in tabela continutul tabelei fiind acelasi cu cel din figura II.11.2. Daca revenim acum la punctul de salvare sp1, in tabela nu mai raman decat trei linii (figura II.11.4.)

ROLLBACK TO sp1
select * from savepoint_test;

Figura II.11.4.

Schematic tranzactia anterioara arata ca in figura II.11.5.

Figura II.11.5.