Basi di dati - Teoria completa

Utilizzo dei trigger e controllo degli accessi in PL/SQL

END IF;END;In PL/SQL, new denota la tupla che si sta inserendo (come this in Java).Dato che si è specificato BEFORE, se lo stipendio risulta essere maggiore del doppio dello stipendio medio, allora l'INSERT non viene effettuato e rimane bloccato nellog.Un trigger deve necessariamente terminare, al fine di non creare problemi al DBMS.

Utilità dei trigger:

• Trattano vincoli non esprimibili dallo schema (esempio della condizione sullo stipendio);
• Attivano automaticamente azioni quando si verificano determinate condizioni.

Controllo degli Accessi

Chi crea lo schema della base di dati è l'unico che può effettuare operazioni su di essa (CREATE, ALTER, DROP).

Chi crea una tabella può specificare i modi in cui altre persone possono farne uso tramite il comando GRANT:

GRANT Privilegi ON Oggetto TO Utenti[WITH GRANT OPTION]

I privilegi che si possono assegnare sono:

• SELECT - permette la sola lettura dei dati;
• INSERT [(Attributi)] -

permette di inserire dei record (con valori non nulli per gli attributi);

DELETE - permette di cancellare i record;

UPDATE [(Attributi)] - permetti di modificare i record (o solo gli attributi);

REFERENCES [(Attributi)] - permette di definire chiavi esterne in altre tabelle che riferiscono gli attributi;

WITH GRANT OPTION - permette di trasferire i privilegi ad altri utenti.

Chi definisce una tabella o una view ha tutti i privilegi su di esse ed è l'unico che potrà eseguire un DROP e autorizzare altri ad usarla tramite GRANT.

Nel caso delle view, il creatore ha i privilegi che ha sulle tabelle usate nelle loro definizioni.

I permessi si possono revocare con il comando REVOKE:

REVOKE [GRANT OPTION FOR] Privilegi ON Oggetto FROM Utenti [CASCADE]

Se si usa CASCADE, i permessi verranno rimossi all'utente specificato e a tutti quelli che hanno ricevuto i permessi SOLO da quell'utente.

Esempi di GRANT:

GRANT INSERT, SELECT ON

Esami TO Tizioo Tizio può fare inserimenti ed effettuare query su Esami

GRANT DELETE ON Esami TO Capo WITH GRANT OPTION

Capo può cancellare record e autorizzare altri a farlo;

GRANT UPDATE (voto) ON Esami TO Sicuroo

Sicuro può modificare solo il voto degli esami;

GRANT SELECT, INSERT ON VistaEsamiBD1 TO Albanoo

Albano può interrogare e modificare solo i suoi esami.

Grafo delle Autorizzazioni

Grafo generato da "WITH GRANT OPTION"

L'utente I ha creato la tabella R e ha innescato la seguente successione di eventi:

I: GRANT SELECT ON R TO A WITH GRANT OPTION

A: GRANT SELECT ON R TO B WITH GRANT OPTION

B: GRANT SELECT ON R TO A WITH GRANT OPTION

I: GRANT SELECT ON R TO C WITH GRANT OPTION

C: GRANT SELECT ON R TO B WITH GRANT OPTION

SYS = System, sistemista

Nodi = utenti

Archi = relazione di concessione di privilegio

Proprietà: Se un nodo N ha un arco uscente con un privilegio, allora esiste un cammino da SYS

a N i cui archi sono etichettati dallo stesso privilegio e dalla grantoption.

Grafo generato da "REVOKE"

I: REVOKE SELECT ON R FROM A CASCADE

I: REVOKE SELECT ON R FROM C CASCADE

Nella prima istruzione, I toglie solo l'arco di privilegio che va ad A e non anche a B, perché questo ha ricevuto il privilegio da C e lo ha anche dato ad A.

Se si volesse revocare il privilegio a tutti quelli che hanno ricevuto il privilegio da A, si dovrebbe revocare il privilegio anche a C, come specificato nella seconda istruzione.

Con CASCADE si percorrono dei cammini, quindi non si può avere la certezza di eliminare i privilegi a tutti quelli che li hanno ricevuti (eseguire più REVOKE).

Indici

Gli indici sono strumenti che permettono di accedere in maniera più rapida ai contenuti delle tabelle.

Sono insiemi ordinati di coppie (valore dell'attributo/puntatore ai record che contengono tale valore) del tipo: (a, {r , ..., r })1 k dove r riferisce un'ennupla

La creazione di indici non è un comando standard dell'SQL e quindi cambia a seconda dei sistemi di riferimento. Con Oracle, si trovano i seguenti comandi:

• CREATE INDEX NomeIdx ON Tabella(Attributi): Viene creato un indice riferito ad una tabella con alcuni attributi. In questo caso viene lasciata al sistema la scelta del tipo di indice.
• CREATE INDEX NomeIdx ON Tabella WITH STRUCTURE = BTREE, KEY = (Attributi): Viene specificata la struttura di memorizzazione (BTREE) e gli attributi chiave (KEY).
• DROP INDEX NomeIdx: Viene eliminato un indice.

Bisogna tenere in considerazione che gli indici occupano spazio, circa il 20% dell'intera tabella.

• Tabella degli attributi:
  • SYSCOLUMNS(name, tbname, tbcreator, colno, coltype, lenght,default, remarks);
• Tabella degli indici:
  • SYSINDEXES(name, tbname, creator, uniquerule, colcount);
• Altre sulle viste, vincoli, autorizzazioni, etc (una decina).

• Il DDL consente di definire tabelle, viste e indici. Le tabelle si possono modificare aggiungendo e togliendo attributi e vincoli;
• Le viste si possono interrogare come ogni altra tabella, ma si possono modificare solo se esiste una corrispondenza biunivoca fra le righe della vista e un sottoinsieme delle righe di una sola tabella di base;
• I comandi GRANT e REVOKE insieme alle viste permettono di fare dei controlli sull'utilizzo dei dati;
• L'SQL permette di definire molti vincoli, oltre a quelli di chiave e referenziale;
• Fanno parte dello schema anche le procedure e i trigger.

basi di dati. Per farlo si sono tenute in considerazione alcune problematiche:

• Come collegarsi alla BD;
• Come trattare gli operatori SQL all'interno di un programma;
• Come eseguire query e update sulla BD;
• Come trattare il risultato di una query SQL;
• Come scambiare informazioni sull'esito delle operazioni.

Approcci

Ci sono 3 approcci alla programmazione con l'utilizzo di basi di dati:

1. Linguaggio integrato (dati e DML):
   • Linguaggio progettato appositamente per usare SQL. Tale linguaggio diventa un sovrainsieme dell'SQL, i cui comandi (o l'interprete) sono controllati staticamente dal traduttore ed eseguiti dal DBMS;
2. Linguaggio convenzionale + API specifiche:
   • Viene utilizzato un linguaggio convenzionale (come Java) che utilizza funzioni di una libreria predefinita per sfruttare le funzionalità dell'SQL. Le query sono stringhe che vengono passate come parametri alle funzioni delle API, le quali, a loro volta, le passano al DBMS.

È la strategia più usata:

Linguaggio che ospita l’SQL:

• Il linguaggio convenzionale viene esteso con un nuovo costrutto per marcare i comandi SQL.
• C’è un pre-compilatore che controlla i comandi SQL, li sostituisce con chiamate a funzioni predefinite (librerie) e genera un programma nel linguaggio convenzionale unito all’uso di librerie.

I notebook Jupiter utilizzano il terzo approccio.

PL/SQL di Oracle:

Il linguaggio integrato più famoso per l’utilizzo di SQL è PL/SQL di Oracle.

È un linguaggio per manipolare basi di dati che integra i costrutti dei linguaggi di programmazione a blocchi con una struttura del controllo e contiene SQL come sottolinguaggio (il suo DML).

PL/SQL permette di:

• Definire variabili di tipo scalare, record (annidato), insieme di scalari, insieme di record piatti, cursori;
• Definire i tipi delle variabili a partire da quelli dello schema della base di dati;
• Eseguire interrogazioni SQL ed esplorarne

Il risultato:

• Modificare la base di dati;
• Definire procedure e moduli;
• Gestire il flusso del controllo, le transazioni, le eccezioni.

Con la terminologia attuale, questo linguaggio viene chiamato DSL - Domain Specific Language, un linguaggio di programmazione completo ma specifico per le basi di dati.

Esempio di procedura in PL/SQL:

• CREATE PROCEDURE Esempio1(p_Mat IN Studenti.Matricola%TYPE) IS
• DECLARE
• Identificatori per lo scambio dati
• XNome CHAR;
• XProvincia Studenti.Provincia%TYPE;
• XAttributi Studenti%ROWTYPE;
• prv_manca EXCEPTION;
• BEGIN
• ricerca di ennupla : stampa Nome e Provincia
• SELECT Nome, Provincia INTO XNome, XProvincia
• FROM Studenti WHERE Matricola = p_Mat;
• IF XProvincia IS NULL THEN
• RAISE prv_manca
• ELSE PRINT ....
• END IF
• EXCEPTION
• WHEN prv_manca THEN <gestione eccezione>
• END;

Procedura Esempio1 con parametro p_Mat, il cui tipo è prelevato dallo schema della base di dati, ovvero il tipo dell'attributo Matricola nella tabella Studenti.

XAttributi è lo

Lo schema della relazione Studenti indica il tipo della riga della tabella.

Il cursore è lo strumento del DBMS che permette di gestire i risultati di query che consistono di più di una tupla. Si possono assimilare agli iteratori di Java.

Un cursore viene creato tramite un'espressione SQL, poi viene aperto per far calcolare il risultato al DBMS e in seguito, tramite il comando FETCH le tuple vengono prelevate una per una. Infine, il cursore va chiuso con il comando CLOSE, per poter rilasciare le risorse.

Esempio di utilizzo