Programmazione.it :: Una strana vista su SQL Server ed Oracle

Ciao, per farti riconoscere devi fare il login. Non ti sei ancora iscritto? Che aspetti, registrati adesso!

Una strana vista su SQL Server ed Oracle

Scritto da Ciro Fiorillo il 18-09-2008 ore 09:34

Nei DBMS le viste consentono di rendere più semplice l'accesso alle informazioni che derivano da più tabelle, oppure possono essere utilizzate come meccanismo di “sicurezza” per fornire l'accesso ad un sottoinsieme di attributi di una tabella. Non è inusuale, quindi, trovarsi di fronte ad uno scenario, in cui una vista è costruita sulla base di un'altra vista, alla quale viene applicato un predicato WHERE per la selezione di alcuni elementi in base ad un criterio prestabilito.

Facciamo un esempio basandoci sul database AdventureWorks di SQL Server: creiamo una prima vista sul database:

<span style="font-size:1.0em">
CREATE VIEW PADRE AS SELECT FirstName, LastName 
from [HumanResources].[vEmployee] 
</span>

ed una seconda vista basata sulla precedente:

<span style="font-size:1.0em">
CREATE VIEW FIGLIO AS SELECT TOP 5 * FROM PADRE
order by FirstName
</span>

Verifichiamo la corretta definizione delle viste con una semplice istruzione SELECT:

<span style="font-size:1.0em">
SELECT * FROM FIGLIO
</span>

il cui risultato è visibile nell'immagine che segue:

<img src="http://farm4.static.flickr.com/3287/2866722535_3bcc0ef7a7_o.jpg" width="173" height="147" alt="Risultati1" /></a>

Modifichiamo adesso la definizione della vista Padre come segue:

<span style="font-size:1.0em">
ALTER VIEW PADRE AS SELECT EmployeeID, FirstName, LastName 
from [HumanResources].[vEmployee] 
</span>

e rieseguiamo la query sulla vista Figlio

<span style="font-size:1.0em">
SELECT * FROM FIGLIO
</span>

Il risultato sarà:

<img src="http://farm4.static.flickr.com/3111/2867554270_2cf9477f04_o.jpg" width="174" height="144" alt="Risultati2" /></a>

Sorpresi? Non c'è da esserlo, conoscendo come SQL Server lavora con le viste; infatti la lista delle colonne comprese nella vista viene interpretata solo al momento della creazione iniziale della stessa; ecco il perché dello strano comportamento. Fortunatamente c'è un rimedio: la stored procedure di sistema sp_refreshview, che consente di aggiornare i metadata della definizione della vista.

In questo caso è necessario eseguire tale stored procedure sulla vista Figlio, in quanto è quella interessata dal cambiamento nella definizione della vista Padre; in presenza di più viste in cascata, un semplice metodo può essere quello di eseguire la stored procedure su tutte le viste del database corrente, come proposto da Keith Rull; su TechNet viene invece mostrato come generare uno script che effettui tale aggiornamento.

Proviamo a riprodurre una situazione analoga sul database Oracle (nello schema di esempio HR) mediante lo script seguente:

<span style="font-size:1.0em">
CREATE VIEW PADRE AS SELECT FIRST_NAME, LAST_NAME FROM HR.EMPLOYEES; 
CREATE VIEW FIGLIO AS SELECT * FROM PADRE WHERE ROWNUM < 6;
SELECT * FROM FIGLIO;
CREATE OR REPLACE VIEW PADRE AS SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME FROM HR.EMPLOYEES;
SELECT * FROM FIGLIO;
</span>

In questo caso la vista Figlio non risente della modifica alla definizione della vista Padre, nel senso che continua a mostrare correttamente i campi First_Name e Last_Name, ma comunque non riporta il nuovo campo aggiunto alla vista Padre anche alla vista Figlio.

Quanto visto ci deve spingere a verificare bene il nostro codice, quando lo stesso debba essere eseguito su piattaforme diverse; in questa particolare situazione, infine, elencare nella definizione delle viste i campi, invece di usare la SELECT *, avrebbe evitato il verificarsi dell'anomalia in entrambi i casi.

Intervento di Roberto Colmegna a.k.a. rcolmegna del 19-09-2008 ore 08:34, Varese (VA)

Plebeo
(8 interventi)
Iscritto il 06-01-2006

Giusto per rimanere in tema ...

http://www.rctecno.net/PG-sec.htm

circa l'utilizzo di viste, per implementare un modello di sicurezza, in postgres SQL.