Cosa sono le views?
Le viste sono delle QUERY memorizzate con un proprio nome che possono essere considerate simili a tabelle virtuali. Sono una via efficace per mostrare informazioni che arrivano da una più tabelle. Le analogie tra tabelle e viste sono parecchie, ma dobbiamo considerare le limitazioni ed i requisiti necessari per poter utlizzare le viste.
Come si crea una view?
Creare una view è una cosa piuttosto semplice, esiste una istruzione CREATE VIEW appositamente usata per questo scopo. Vediamone la sintassi ed anche un esempio:
CREATE VIEW [ < nome_database > . ] [ < proprietario > . ] nome_vista [ ( colonna [ ,...n ] ) ]
[ WITH < attributi_vista > [ ,...n ] ]
AS
istruzione_SELECT
[ WITH CHECK OPTION ]
Ora supponiamo di voler creare una vista che ci dia l’elenco di tutti i prodotti per ogni categoria del database Northwind. Per prima cosa costruiamo la query utile a recuperare i dati che ci servono
SELECT
Categories.CategoryName AS NomeCategoria,
Products.ProductName AS
NomeProdotto,
Products.QuantityPerUnit AS
Quantità
FROM Categories
INNER JOIN Products ON Categories.CategoryID = Products.CategoryID
Fatto questo non ci resta che memorizzare la query all’interno di una vista, che chiameremo V_ProdottiPerCategoria.
CREATE VIEW V_ProdottiPerCategoria AS
SELECT
Categories.CategoryName AS NomeCategoria,
Products.ProductName AS
NomeProdotto,
Products.QuantityPerUnit AS
Quantità
FROM Categories
INNER JOIN Products ON Categories.CategoryID = Products.CategoryID
In questo caso ho specificato il nome delle singole colonne dei dati all’interno della SELECT, ma sarebbe stato possibile fare questo anche a livello dell’intestazione della vista, ad esempio
CREATE VIEW V_ProdottiPerCategoria
(NomeCategoria,NomeProdotto,Quantità)
AS
SELECT
Categories.CategoryName,
Products.ProductName,
Products.QuantityPerUnit
FROM Categories
INNER JOIN Products ON Categories.CategoryID = Products.CategoryID
NB. Ricordate che ogni colonna dati della vista deve avere un
nome ed il nome deve essere univoco.
Ora che abbiamo creato la vista possiamo accedere molto rapidamente alla lista dei prodotti per categoria ongni qualvolta lo desideriamo, basterà una semplice SELECT del tipo: SELECT * FROM V_ProdottiPerCategoria.
Per modificare una vista esiste un’altra istruzione analoga alla precedente dal nome ALTER VIEW, vediamo la sintassi:
ALTER VIEW [ < nome_database > . ] [ <
proprietario > . ] nome_vista [ ( colonna [
,...n ] ) ]
[ WITH < attributi_vista > [ ,...n ] ]
AS
istruzione_SELECT
[ WITH CHECK OPTION ]
Come si può intuire questa istruzione modifica la struttura di una vista precedentemente creata, supponiamo ad esempio di voler aggiungere una colonna dati alla vista che abbiamo precedentemente creato
ALTER VIEW V_ProdottiPerCategoria (NomeCategoria,NomeProdotto,Quantità,InMagazzino)
AS
SELECT
Categories.CategoryName,
Products.ProductName,
Products.QuantityPerUnit,
Products.UnitsInStock
FROM Categories
INNER JOIN Products ON Categories.CategoryID = Products.CategoryID
Ecco fatto, basterà far girare nel QA il batch per la modifica della vista.
I limiti delle views
Non è possibile usare la clausola ORDER BY in una view
Questa limitazione può sembrare assurda, ma difatti non è possible specificare un ordinamento tramite la clausola ORDER BY per il ROWSET ritornato da una view. Il motivo di tutto ciò e che la view come la tabella ritorna un set di dati che non possiede alcun ordinamento. (Standard ANSI)
Per fare una prova lanciamo una ALTER sulla VIEW precedente provando ad inserire un ordinamento in base al nome della categoria
ALTER VIEW V_ProdottiPerCategoria
(NomeCategoria,NomeProdotto,Quantità,InMagazzino)
AS
SELECT
Categories.CategoryName,
Products.ProductName,
Products.QuantityPerUnit,
Products.UnitsInStock
FROM Categories
INNER JOIN Products ON Categories.CategoryID = Products.CategoryID
ORDER BY Categories.CategoryName
Una volta lanciata la modifica, SQL Server ci ritorna un errore di
questo tipo:
Server: Msg 1033, Level 15, State 1, Procedure V_ProdottiPerCategoria, Line 10
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.
Il messaggio ci dice che la clausola ORDER BY non può essere
utilizzata in una vista senza che la clausola TOP sia specificata.
Quindi è gioco facile trovare la scappatoia per creare viste
ordinate, sarà sufficiente inserire la clausola TOP all’interno della query, dato che la clausola TOP supporta anche la percentuale di righe per prendere tutto il set di dati sarà necessario specificare TOP 100 PERCENT
ALTER VIEW V_ProdottiPerCategoria
(NomeCategoria,NomeProdotto,Quantità,InMagazzino)
AS
SELECT TOP 100 PERCENT –non un numero fisso ma la
percentuale di righe 100%
Categories.CategoryName,
Products.ProductName,
Products.QuantityPerUnit,
Products.UnitsInStock
FROM Categories
INNER JOIN Products ON Categories.CategoryID = Products.CategoryID
ORDER BY Categories.CategoryName
Non è possibile usare una SELECT INTO in una view
Non è possibile usare una clausola COMPUTE o COMPUTE BY
Non è possibile fare riferimento a tabelle temporanee o
variabili tabella
Nascondere la definizione di una vista
Grazie all’opzione WITH ENCRYPTION disponibile con le istruzioni CREATE ed ALTER VIEW, possiamo criptare la definizione delle nostre viste (in buona sostanza la natura della query) da occhi indiscreti.
Difatti normalmente quando creiamo una vista viene aggiunta una riga nella tabella di sistema syscomment la quale contiene il testo della vista, facciamo un esempio
SELECT text FROM dbo.syscomments
WHERE id = object_id('V_ProdottiPerCategoria')
Con questa semplice SELECT possiamo recuperare la definizione della
vista, per ovviare a questo inconveniente proviamo a modificare la
vista con l’opzione WITH ENCRYPTION
ALTER VIEW dbo.V_ProdottiPerCategoria
(NomeCategoria,NomeProdotto,Quantità,InMagazzino)
WITH ENCRYPTION
AS
SELECT TOP 100 PERCENT
Categories.CategoryName,
Products.ProductName,
Products.QuantityPerUnit,
Products.UnitsInStock
FROM dbo.Categories
INNER JOIN dbo.Products ON Categories.CategoryID =
Products.CategoryID
ORDER BY Categories.CategoryName
Ed ora se lanciamo la SELECT precedente per recuperare la definizione dell’oggetto V_ProdottiPerCategoria vedrete che la definizione è stata criptata e non più accessibile. Solo SQL Server è in grado di risolvere l’algoritmo di criptazione ed eseguire correttamente la query contenuta nella vista.
Le viste come meccanismo di sicurezza
SQL Server gestisce i permessi di accesso ai dati sia a livello di tabella che e livello di colonna. Ma a differenza di altri RDBMS SQL Server non gestisce i permessi di accesso a livello di riga. In questo caso ci vengono in aiuto le viste per implementare meccanismi di sicurezza orizzontale sui dati.
Difatti attraverso le viste possiamo filtrare solo i dati (le righe della tabella) che ci interessano e concedere i relativi permessi solo agli utenti o i gruppi pertinenti, questi infine potranno lavorare con le viste come se fossero tabelle vere e proprie.
Ma per poter concedere tali permessi sulle viste non dobbiamo necessariamente disporre degli opportuni permessi sugli oggetti sottostanti a cui fa riferimento la vista a patto che vengano soddisfatti i seguenti requisiti:
- Il proprietario della view e quello degli oggetti sottostanti sono la stessa persona
- Se la vista fa riferimento ad un oggetto in un altro database, la login che sta eseguendo la query sulla vista deve avere accesso a quel database.
Se non dovesse essere soddisfatto primo requisito dobbiamo concedere all’utente i permessi a tutti gli oggetti cui la vista fa riferimento.
Facciamo un esempio, supponiamo voler creare una vista per visualizzare gli autori e i libri da loro scritti
CREATE VIEW dbo.V_AutoriPerTitoli AS
SELECT
T.title AS "Titolo",
A.au_lname + ' ' + A.au_fname AS
"Autore libro",
A.state AS "Stato di
nascita"
FROM
dbo.titles T
INNER JOIN dbo.titleauthor TA ON TA.title_id =
T.title_id
INNER JOIN dbo.authors A ON A.au_id = TA.au_id
In questo caso la vista dbo.V_AutoriPerTitoli fa
riferimento a tre tabelle tutte di proprietà del possessore
della vista, quindi per concedere l’uso di questa vista all’utente gino sarà sufficiente eseguire il comando:
GRANT SELECT ON dbo.V_AutoriPerTitoli TO gino
Osserviamo bene una cosa, non è necessario per accedere ai dati che l’utente gino abbia i permessi sugli oggetti a cui fa riferimento la vista dbo.V_AutoriPerTitoli (le tabelle dbo.titles, dbo.titleauthor, dbo.authors).
Ora vediamo un altro caso, la vista dbo.V_AutoriPerTitoli2
fa riferimento a due oggetti che non sono di proprietà del possessore della vista (dbo).
CREATE VIEW dbo.V_AutoriPerTitoli2 AS
SELECT
T.title AS "Titolo",
A.au_lname + ' ' + A.au_fname AS
"Autore libro",
A.state AS "Stato di
nascita"
FROM
dbo.titles T
INNER JOIN pippo.titleauthor TA ON TA.title_id =
T.title_id
INNER JOIN pippo.authors A ON A.au_id = TA.au_id
In questo caso non sarà sufficiente concedere all’utente gino i permessi sulla vista come nel caso precedente, ma dovremo concedere esplicitamente i permessi anche sugli oggetti a cui la vista fa riferimento:
GRANT SELECT ON pippo.titleauthor TO gino
GRANT SELECT ON pippo.authors TO gino
Nella prossima puntata impareremo a sfruttare le potenzialità delle stored procedure.