SQL Server: meta programmazione e meta descrittori

In questo articolo della serie SQL Server, vedremo come creare  in modo dinamico delle istruzione per impostare delle policy di sicurezza. Creeremo da zero una stored procedure chiamata sp_FixOverallGrant che in modo dinamico imposterà i profili di sicurezza per due utenze, una in lettura e scrittura (EBRB0_APP) ed una in sola lettura (EBRB0_USR).

In particolare vedremo come sia possibile sia eseguire SQL dinamico che accedere alla struttura interna del database.
Dal punto di vista idiomatico SQL Server utilizza il seguente codice per cancellare un elemento, per esempio una stored procedure:

/****** Object:  StoredProcedure [dbo].[sp_FixOverallGrant]    Script Date: 01/24/2011 14:51:23 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_FixOverallGrant]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_FixOverallGrant]
GO

La linea 2 effettua una select nella meta tabella sys.objects, cercando se esiste una stored procedure chiamata sp_FixOverallGrant.

Vediamo ora il codice completo della stored procedure:


CREATE PROCEDURE dbo.sp_FixOverallGrant
AS
 BEGIN
	DECLARE @mycur CURSOR
	DECLARE @cmdx NVARCHAR(200)
	PRINT 'sp_FixOverallGrant Inizio fixes...';
	PRINT 'Fase 1 di 2: Fix grant su procedure e funzioni';
	-- Fix Stored procedure
	SET @mycur = CURSOR
	FOR
		-- Stored Procedure e FUNCTIONS
		SELECT 'GRANT EXECUTE ON [dbo].[' +name +'] TO [EBRB0_APP];'
		FROM sys.objects WHERE 
		(type in (N'P', N'PC')  OR (type in (N'FN', N'IF', N'TF', N'FS', N'FT')))
		and name LIKE 'sp%' and name not in 
		( 'sp_creatediagram','sp_helpdiagramdefinition', 'sp_renamediagram', 'sp_alterdiagram', 'sp_dropdiagram',
		  'sp_upgraddiagrams', 'sp_helpdiagrams' )
		ORDER BY name;
	OPEN @mycur

	-- Exec first
	FETCH NEXT FROM @mycur INTO @cmdx;

	WHILE @@FETCH_STATUS = 0
	BEGIN	
		PRINT 'Eseguo '+@cmdx;
		EXEC dbo.sp_executesql @statement = @cmdx;
		FETCH NEXT FROM @mycur INTO @cmdx
	END

	CLOSE @mycur
	DEALLOCATE @mycur




	-- Tabelle: Grant per le due utenze EBRB0_APP e EBRB0_USR
	-- 
	DECLARE @tabcur CURSOR	

	PRINT 'Fase 2 di 2: Fix grant su tabelle';
	-- Fix Stored procedure
	SET @tabcur = CURSOR
	FOR
		-- Tabella
		SELECT 'GRANT INSERT,SELECT,UPDATE,DELETE ON [dbo].['+name+'] TO [EBRB0_APP]; GRANT SELECT ON [dbo].['+name+'] TO [EBRB0_USR]; ' 
		FROM sys.objects WHERE type in (N'U') and name LIKE 'T_E%' ORDER BY NAME;
	OPEN @tabcur

	-- Exec first
	FETCH NEXT FROM @tabcur INTO @cmdx;

	WHILE @@FETCH_STATUS = 0
	BEGIN	
		PRINT 'Eseguo '+@cmdx;
		EXEC dbo.sp_executesql @statement = @cmdx;
		FETCH NEXT FROM @tabcur INTO @cmdx
	END

	CLOSE @tabcur
	DEALLOCATE @tabcur
	PRINT 'sp_FixOverallGrant terminata';
END
GO

EXEC sp_FixOverallGrant;

Le linee 9-29 servono per effettuare una query sui meta dati e creare delle istruzioni SQL per le grant in esame.
La linea 27 mostra come eseguire una stringa di comando sql in modo dinamico.
Il codice si ripete dalla linea 34 per la gestione delle tabelle.
Infine l’ultima istruzione esegue la stored procedure.