Droit sql pour chaque utilisateurs

Version imprimable

21/10/2013, 10h57
djinpark1

Droit sql pour chaque utilisateurs

Bonjour,
j'aimerai créer une vue qui permet de lister tout les droits pour chaque utilisateur ainsi que leur rôles pour chaque base.
Merci

Bonjour,

Voici une requête pour lister tous les membres de tous les rôles d'une base de données :

Code:

1
2
3
4
5
6
7
SELECT		R.name AS role_name
		, M.name AS member_name
FROM		sys.database_role_members AS RM
INNER JOIN	sys.database_principals AS R
			ON R.principal_id = RM.role_principal_id
INNER JOIN	sys.database_principals AS M
			ON M.principal_id = RM.member_principal_id

Voici une requête pour lister les permissions des rôles, membre par membre :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
SELECT		R.name AS role_name
		, M.name AS member_name
		, G.permission_name
		, CASE G.class
			WHEN 1 THEN O.name
			WHEN 3 THEN S.name
			WHEN 0 THEN DB_NAME()
		END AS object_name
		, CASE G.class
			WHEN 1 THEN O.type_desc
			WHEN 3 THEN 'SCHEMA'
			WHEN 0 THEN 'DATABASE'
		END AS object_type_desc
		, SO.name
FROM		sys.database_role_members AS RM
INNER JOIN	sys.database_principals AS R
			ON RM.role_principal_id = R.principal_id
INNER JOIN	sys.database_principals AS M
			ON RM.member_principal_id = M.principal_id
LEFT JOIN	sys.database_permissions AS G
			ON G.grantee_principal_id = R.principal_id
LEFT JOIN	sys.schemas AS S
			ON G.major_id = S.schema_id
LEFT JOIN	sys.objects AS O
			ON G.major_id = O.object_id
LEFT JOIN	sys.schemas AS SO
			ON O.schema_id = SO.schema_id

Enfin voici une requête qui liste les droits spécifiques à chaque utilisateur, et qui donne en plus le script pour octroyer ou révoquer un tél privilège :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
;WITH
	CTE AS
	(
		SELECT		DB_USER.name AS user_name
				, DB_GRANT.class_desc
				, O.type_desc AS DB_object_type
				, DB_GRANT.state_desc
				, DB_GRANT.permission_name
				,  CASE DB_GRANT.class_desc
					WHEN 'OBJECT_OR_COLUMN' THEN SO.name + '.'
					WHEN 'SCHEMA' THEN 'SCHEMA::'
					WHEN 'TYPE' THEN 'TYPE::' + STY.name + '.'
					ELSE SO.name + '.'
				END
				+ CASE DB_GRANT.class_desc
					WHEN 'OBJECT_OR_COLUMN' THEN O.name
					WHEN 'SCHEMA' THEN S.name
					WHEN 'TYPE' THEN TY.name
					ELSE O.name
				END AS object_name
		FROM		sys.database_permissions AS DB_GRANT
		INNER JOIN	sys.database_principals AS DB_USER
					ON DB_GRANT.grantee_principal_id = DB_USER.principal_id
		LEFT JOIN	sys.objects AS O
					ON O.object_id = DB_GRANT.major_id
		LEFT JOIN	sys.types AS TY
					ON TY.user_type_id = DB_GRANT.major_id
		LEFT JOIN	sys.schemas AS S
					ON S.schema_id = DB_GRANT.major_id
		LEFT JOIN	sys.schemas AS SO
					ON SO.schema_id = O.schema_id
		LEFT JOIN	sys.schemas AS STY
					ON STY.schema_id = TY.schema_id
		WHERE		DB_USER.name <> 'public'
		AND		DB_GRANT.permission_name <> 'CONNECT'
	)
SELECT	user_name
	, class_desc
	, DB_object_type
	, state_desc
	, permission_name
	, object_name
	, state_desc + ' ' + permission_name
		+ ' ON ' + object_name + ' TO [' + user_name  + ']' COLLATE database_default AS sql_statement
FROM	CTE

@++ ;)

21/10/2013, 11h41
Kropernic

A priori, pour chaque DB, tu peux interroger sys.sysusers.

IL y a une colonne name et une colonne roles. Ca m'a l'air pas mal comme point de départ.
21/10/2013, 13h00
elsuket

C'st exact, sauf que cette table système est dépréciée depuis la sortie de SQL Server 2005, comme l'indique la documentation :

Citation:

Cette table système SQL Server 2000 est incluse sous la forme d'une vue pour la compatibilité descendante. Nous vous recommandons d'utiliser les vues système SQL Server actuelle. Pour trouver les vues système équivalentes, consultez Mappage des tables système SQL Server 2000 avec les vues système SQL Server 2005. Cette fonctionnalité sera supprimée dans une prochaine version de Microsoft SQL Server. Évitez d'utiliser cette fonctionnalité dans de nouveaux travaux de développement et prévoyez de modifier les applications qui utilisent actuellement cette fonctionnalité.

Les requêtes que je propose utilisent les nouvelles vues : autant se familiariser avec elles dès que possible ;)

@++ ;)
21/10/2013, 13h41
Kropernic

Citation:

Envoyé par elsuket

C'st exact, sauf que cette table système est dépréciée depuis la sortie de SQL Server 2005, comme l'indique la documentation :

Les requêtes que je propose utilisent les nouvelles vues : autant se familiariser avec elles dès que possible ;)

@++ ;)

On s'est croisé dans la publication (j'ai vérifié mes dires avant de cliquer sur envoyer la réponse alors ça a pris un peu de temps :aie:). Je n'avais pas vu votre réponse. De fait, votre méthode est nettement mieux !