IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Développement SQL Server Discussion :

Droit sql pour chaque utilisateurs


Sujet :

Développement SQL Server

  1. #1
    Nouveau membre du Club
    Inscrit en
    Avril 2008
    Messages
    42
    Détails du profil
    Informations forums :
    Inscription : Avril 2008
    Messages : 42
    Points : 27
    Points
    27
    Par défaut 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

  2. #2
    Modérateur

    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Janvier 2005
    Messages
    5 826
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 42
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Janvier 2005
    Messages : 5 826
    Points : 12 371
    Points
    12 371
    Par défaut
    Bonjour,

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

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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
    @++

  3. #3
    Expert confirmé
    Avatar de Kropernic
    Homme Profil pro
    Analyste / Programmeur / DBA
    Inscrit en
    Juillet 2006
    Messages
    3 932
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 41
    Localisation : Belgique

    Informations professionnelles :
    Activité : Analyste / Programmeur / DBA
    Secteur : Distribution

    Informations forums :
    Inscription : Juillet 2006
    Messages : 3 932
    Points : 4 239
    Points
    4 239
    Par défaut
    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.
    Kropernic

  4. #4
    Modérateur

    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Janvier 2005
    Messages
    5 826
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 42
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Janvier 2005
    Messages : 5 826
    Points : 12 371
    Points
    12 371
    Par défaut
    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 :

    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

    @++

  5. #5
    Expert confirmé
    Avatar de Kropernic
    Homme Profil pro
    Analyste / Programmeur / DBA
    Inscrit en
    Juillet 2006
    Messages
    3 932
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 41
    Localisation : Belgique

    Informations professionnelles :
    Activité : Analyste / Programmeur / DBA
    Secteur : Distribution

    Informations forums :
    Inscription : Juillet 2006
    Messages : 3 932
    Points : 4 239
    Points
    4 239
    Par défaut
    Citation Envoyé par elsuket Voir le message
    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 ). Je n'avais pas vu votre réponse. De fait, votre méthode est nettement mieux !
    Kropernic

Discussions similaires

  1. Réponses: 11
    Dernier message: 27/03/2008, 23h48
  2. utile de créer une table pour chaque utilisateur ?
    Par Overstone dans le forum Langage SQL
    Réponses: 6
    Dernier message: 22/03/2008, 15h37
  3. mise à jour champs sql pour chaque ligne
    Par opsi dans le forum SQL Procédural
    Réponses: 2
    Dernier message: 20/03/2008, 23h44
  4. Réponses: 7
    Dernier message: 29/02/2008, 20h23
  5. [Requête] faire la somme des max pour chaque utilisateur
    Par novices dans le forum Langage SQL
    Réponses: 3
    Dernier message: 13/02/2007, 12h45

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo