Sql server 2005 - creer un role automatiquement (mode lecteur ou utilisateur)

Commenter

Contenu du snippet

En cours de développement, on peut vouloir mettre en place un politique de gestion basée sur les ROLES SQL Server 2005.
Or on a bien souvent déjà créé une quantité plus ou moins importante d'objets (tables, vues, SP, fonctions) pour lesquels il faut alors appliquer les droits à la main.

Ce script vous permet de faire ce travail en spécifiant au départ le nom du rôle et le type de rôle (lecteur ou utilisateur).

Source / Exemple : 


USE [MaBase]
GO

DECLARE @RoleName		AS VARCHAR(100)
DECLARE @SQL			AS VARCHAR(MAX)
DECLARE @TypeRole		AS INT
DECLARE @SQLCMD			AS VARCHAR(200)	

SET @RoleName = 'LECTEUR'
SET @TypeRole = 0 -- 0 = UNIQUEMENT dataReader/executeFN | 1 = dataReader/dataWriter/executeSP/executeFN | 

------------------------------------------------------------------------------------------
---- CREATION DU ROLE
------------------------------------------------------------------------------------------
IF  NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = @RoleName AND type = 'R')
BEGIN
	SET @SQL = 'CREATE ROLE ['+ @RoleName +'] AUTHORIZATION [dbo] '
	EXECUTE (@SQL)
END

IF(@TypeRole = 1) 
	BEGIN
		------------------------------------------------------------------------------------------
		---- BOUCLE SUR LES TABLES ET VUES
		---- AJOUTE LES DROITS DE MODIFICATION SUR LES TABLES ET VUES
		------------------------------------------------------------------------------------------
		PRINT '--------------------------------------------------------------------------------------------------' 
		PRINT '-- MISE EN PLACE DU ROLE UTILISATEUR SUR LES TABLES ET VUES : DROIT SUR LE SELECT/UPDATE/INSERT/DELETE ET EXECUTE DES SP ET FN' 
		DECLARE CURSORTABLESANDVUES CURSOR FOR SELECT '['+ TABLE_SCHEMA +'].['+ TABLE_NAME +']' AS TABLE_COMPLETE_NAME FROM INFORMATION_SCHEMA.TABLES
		OPEN CURSORTABLESANDVUES
		FETCH NEXT FROM CURSORTABLESANDVUES INTO @SQLCMD
		WHILE (@@fetch_status >= 0) 
			BEGIN 
				SET @SQL = 'GRANT SELECT ON '+ @SQLCMD +' TO ['+ @RoleName +']'
				PRINT (@SQL)
				EXECUTE (@SQL)
				SET @SQL = 'GRANT UPDATE ON '+ @SQLCMD +' TO ['+ @RoleName +']'
				PRINT (@SQL)
				EXECUTE (@SQL)
				SET @SQL = 'GRANT DELETE ON '+ @SQLCMD +' TO ['+ @RoleName +']'
				PRINT (@SQL)
				EXECUTE (@SQL)
				SET @SQL = 'GRANT INSERT ON '+ @SQLCMD +' TO ['+ @RoleName +']'
				PRINT (@SQL)
				EXECUTE (@SQL)
				FETCH NEXT FROM CURSORTABLESANDVUES INTO @SQLCMD
			END
		CLOSE CURSORTABLESANDVUES
		DEALLOCATE CURSORTABLESANDVUES

		------------------------------------------------------------------------------------------
		---- BOUCLE SUR LES FONCTIONS ET PROCEDURES STOCKEES
		---- AJOUTE LES DROITS EXECUTE DES SP ET FONCTIONS
		------------------------------------------------------------------------------------------
		PRINT '--------------------------------------------------------------------------------------------------' 
		PRINT '-- MISE EN PLACE DU ROLE UTILISATEUR SP ET FUNCTION : DROIT SUR LE EXECUTE' 
		DECLARE CURSORSP CURSOR FOR SELECT '['+ sys.schemas.name +'].['+ sys.objects.name +']' AS SPFN_COMPLETE_NAME FROM sys.objects INNER JOIN sys.schemas ON sys.schemas.schema_id = sys.objects.schema_id WHERE sys.objects.type = 'P' OR sys.objects.type = 'FN' ORDER BY sys.objects.type, sys.objects.name
		OPEN CURSORSP
		FETCH NEXT FROM CURSORSP INTO @SQLCMD
		WHILE (@@fetch_status >= 0) 
			BEGIN 
				SET @SQL = 'GRANT EXECUTE ON '+ @SQLCMD +' TO ['+ @RoleName +']'
				PRINT (@SQL)
				EXECUTE (@SQL)

				FETCH NEXT FROM CURSORSP INTO @SQLCMD
			END
		CLOSE CURSORSP
		DEALLOCATE CURSORSP
		PRINT '--------------------------------------------------------------------------------------------------' 

	END
ELSE 
	BEGIN 
		------------------------------------------------------------------------------------------
		---- BOUCLE SUR LES TABLES ET VUES
		---- AJOUTE LES DROITS DE SELECTION SUR LES TABLES ET VUES
		------------------------------------------------------------------------------------------
		PRINT '--------------------------------------------------------------------------------------------------' 
		PRINT '-- MISE EN PLACE DU ROLE LECTEUR SUR LES TABLES ET VUES : DROIT SUR LE SELECT' 
		DECLARE CURSORTABLESANDVUES CURSOR FOR SELECT '['+ TABLE_SCHEMA +'].['+ TABLE_NAME +']' AS TABLE_COMPLETE_NAME FROM INFORMATION_SCHEMA.TABLES
		OPEN CURSORTABLESANDVUES
		FETCH NEXT FROM CURSORTABLESANDVUES INTO @SQLCMD
		WHILE (@@fetch_status >= 0) 
			BEGIN 
				SET @SQL = 'GRANT SELECT ON '+ @SQLCMD +' TO ['+ @RoleName +']'
				PRINT (@SQL)
				EXECUTE (@SQL)
				FETCH NEXT FROM CURSORTABLESANDVUES INTO @SQLCMD
			END
		CLOSE CURSORTABLESANDVUES
		DEALLOCATE CURSORTABLESANDVUES

		------------------------------------------------------------------------------------------
		---- BOUCLE SUR LES FONCTIONS
		---- AJOUTE LES DROITS EXECUTE DES FONCTIONS
		------------------------------------------------------------------------------------------
		PRINT '--------------------------------------------------------------------------------------------------' 
		PRINT '-- MISE EN PLACE DU ROLE LECTEUR FUNCTION : DROIT SUR LE EXECUTE' 
		DECLARE CURSORSP CURSOR FOR SELECT '['+ sys.schemas.name +'].['+ sys.objects.name +']' AS SPFN_COMPLETE_NAME FROM sys.objects INNER JOIN sys.schemas ON sys.schemas.schema_id = sys.objects.schema_id WHERE sys.objects.type = 'FN' ORDER BY sys.objects.type, sys.objects.name
		OPEN CURSORSP
		FETCH NEXT FROM CURSORSP INTO @SQLCMD
		WHILE (@@fetch_status >= 0) 
			BEGIN 
				SET @SQL = 'GRANT EXECUTE ON '+ @SQLCMD +' TO ['+ @RoleName +']'
				PRINT (@SQL)
				EXECUTE (@SQL)

				FETCH NEXT FROM CURSORSP INTO @SQLCMD
			END
		CLOSE CURSORSP
		DEALLOCATE CURSORSP
		PRINT '--------------------------------------------------------------------------------------------------' 

	END

Conclusion :


Bon coding

Romelard Fabrice [MVP]

A voir également

S'inscrire maintenant

Vous n'êtes pas encore membre ?

inscrivez-vous, c'est gratuit et ça prend moins d'une minute !

Les membres obtiennent plus de réponses que les utilisateurs anonymes.

Le fait d'être membre vous permet d'avoir un suivi détaillé de vos demandes et codes sources.

Le fait d'être membre vous permet d'avoir des options supplémentaires.

Du même auteur (cs_fabrice69)