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: [sql]/****** 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[/sql] 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: [sql]

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; [/sql]

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.