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

Soyez le premier à donner votre avis sur cette source.

Snippet vu 8 772 fois - Téléchargée 27 fois

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

Ajouter un commentaire

Commentaires

cs_fabrice69
Messages postés
1765
Date d'inscription
jeudi 12 octobre 2000
Statut
Modérateur
Dernière intervention
11 décembre 2013
4
Bonne nouvelle :P

Fabrice
cacalex
Messages postés
69
Date d'inscription
mardi 9 mars 2004
Statut
Membre
Dernière intervention
3 avril 2008

Bah, déjà, j'arrive à donner les permission SELECT, UPDATE ,DELETE et INSERT sur les tables et les vues... Il ne me resteras qu'à modifier manuellement EXECUTE pour les procédure stockés...

Ce code m'as évité beaucoup de travail !!!

Merci !
cs_fabrice69
Messages postés
1765
Date d'inscription
jeudi 12 octobre 2000
Statut
Modérateur
Dernière intervention
11 décembre 2013
4
Bonjour,

Comme je vous l'ai expliqué, de nombreux changement ont été effectués entre la version SQL Server 2000 et SQL Server 2005, notemment la mise en place des schémas (qui n'existe pas réellement sous 2000).
En effet, sous 2000, le schéma est le compte utilisateur ayant créé l'objet (donc très souvent on retrouve dbo.NomObjet).
Sous 2005, c'est différent et un peu plus proche des concepts d'ORACLE.

Cordialement
Romelard Fabrice
cacalex
Messages postés
69
Date d'inscription
mardi 9 mars 2004
Statut
Membre
Dernière intervention
3 avril 2008

En fait, quel est l'équivalent pour 2000 du sys.schemas de 2005 ?!?!?

Qu'est-ce que contient le sys.schemas de 2005 ?!?!?

Ça m'aiderais à m'orienter...

Merci !
cacalex
Messages postés
69
Date d'inscription
mardi 9 mars 2004
Statut
Membre
Dernière intervention
3 avril 2008

Remplacer
'sys.database_principals'
par
'sysusers',
et remplacer
"name @RoleName AND type 'R'"
par
"name @RoleName AND altuid '0'"

Et ça fonctionnes...

Impossible faire fonctionner "Grant Execute" des fonctions par contre...
---- BOUCLE SUR LES FONCTIONS
---- AJOUTE LES DROITS EXECUTE DES FONCTIONS
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯


Résultat :
-- MISE EN PLACE DU ROLE LECTEUR FUNCTION : DROIT SUR LE EXECUTE
Serveur : Msg 208, Niveau 16, État 1, Ligne 98
'sys.schemas' : nom d'objet incorrect.

Je vais fouiller un peu...

Merci !

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.