Discussion :

[albanhig]

Bonjour,
je m apelle Alban et je suis en stage de dev.

J'ai une problématique que je n'arrive aps à résoudre. En effet, mon groupe de dev développe une CRM (c'est à la mode on dirait), ce qui n'a presque rien a voir avec ma question, mais au moins ca vous met en condition ^^

Bref, on m'a demandé de développer une sorte de module en VB.NET qui permettrait de gérer les droits des utilisateurs pour SQL-Server 2000.

Donc pour changer les droits, y a pas trop de problémes, je balance des "grant" et des "revoke" donc tout va bien.

Par contre, pour récupérer les droits des utilisateurs, probleme.

J'avais trouvé une requete qui etait censé me permettre de faire cela à cette adresse : http://sqlpro.developpez.com/cours/sqlaz/dcl/#L4
Pour les flemmard, voici la requete :
SELECT GRANTOR, GRANTEE, TABLE_NAME, ' <TABLE>' AS COLUMN_NAME, PRIVILEGE_TYPE, IS_GRANTABLE
FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
WHERE GRANTEE IN ('DUBOIS', 'DUVAL', 'DULAC')

Malheureusement, cela ne marche pas, le DataReader dans lequel je range cette requete n'a jamais de ligne. (hasRows = false)

Donc voila, si quelqu'un a deja ete confronté à ce probleme, ce serait sympa de partager l information.

Sur google, y a pas grand chose sur ce sujet.

Merci ^^

[ZERS]

Bonjour,

voici un code qui permet de lister les permissions sur tous les objets d'une base, et si nécessaire tous les colonnes de chacune des tables.
Il te suffira d'ajouter une clause WHERE si tu veux filtrer le résultat par nom d'utilisateur ou nom d'objet
A+

PS : fait attention à la différnce entre le LOGIN et l'UTILISATEUR dans la base. Par exemple le login c'est 'sa', mais l'utilisateur c'est 'dbo'. Donc si nécessaire ajoute à mon code une jointure avec syslogins de master et prefixe les tables par la base de données dans laquelle tu recherches les droits sur les objets.

PSBIS : si tu veux faire un filtre sur le nom d'une table ou d'un utilisateur compare les chaines de caractères en forcant en majuscule systématiquement. Ca evite les histoires de noms avec majuscules, minuscules

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

WHERE UPPER(su.name) = 'DUPOND'

Le code :

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
 
SELECT 
	so.name,
	so.type,
	su.name,
	CASE ISNULL(pro.action, 0)
	when 26  then 'REFERENCES'
	when 178 then 'CREATE FUNCTION'
	when 193 then ' SELECT'
	when 195 then ' INSERT'
	when 196 then ' DELETE'
	when 197 then  'UPDATE'
	when 198 then  'CREATE TABLE'
	when 203 then  'CREATE DATABASE'
	when 207 then  'CREATE VIEW'
	when 222 then  'CREATE PROCEDURE'
	when 224 then  'EXECUTE'
	when 228 then  'BACKUP DATABASE'
	when 233 then  'CREATE DEFAULT'
	when 235 then  'BACKUP LOG'
	when 236 then  'CREATE RULE'
	when 0 then 'TOUS'
	END 'DROIT',
	case isnull(pro.COLUMNS, 5)
	when 1 then co.name
	when 0 then 'ALL'
	when 5 then 'OBJET ENTIER'
	END 'COLONNE'
FROM sysobjects so 
INNER JOIN sysusers su
ON so.uid = su.uid
LEFT JOIN sysprotects pro 
ON pro.id = so.id
LEFT JOIN syscolumns co
ON pro.COLUMNS = co.colID
AND pro.id = co.id
ORDER BY so.name, su.name

[albanhig]

Pfiou, quand je vois ta requete, je me dis que j ai encore un long chemin devant moi avant d en arriver.

En tout cas merci beaucoup, j aurais ete incapable de pondre ca tout seul ^^

[rudib]

Bonjour,

Je me permets quelques précisions :
- Le problème de la requête de Zers, c'est qu'elle se base sur les tables systèmes, qui normalement ne sont plus visibles sur SQL Server 2005. Donc ça risque de poser problème pour la pérennité de ton soft. La première requête que tu as donnée doit marcher. As-tu essayé sans le IN de la clause WHERE ?
- Tu n'as pas besoin e faire un UPPER() si la collation de la base est en CI (case insensitive). Peut-être même si c'est en CS, je ne sais pas comment les tables systèmes réagissent à la casse.

[ZERS]

Salut rudi,

les tabmes system ne sont plus dispos en sql2005 ?
Ouah, pour tous les scripts de maintenances qui se basent dessus, bonjour le rework

[rudib]

Elles devaient être cachées, mais je crois qu'elles sont finalement encore visibles de justesse, pour éviter justement les problèmes dont tu parles. Elles sont officiellement remplacées par des vues et fonctions système (sys.) et les vues INFORMATION_SCHEMA.

[notrustinyou]

Salut a tous !
Je sais que ce thread est un peu vieux, mais j'ai trouvé utilse de le mettre à jour, car j'ai eu besoin des droits utilisateurs sur les Tables et Colonnes, lors d'un developpement.
J'ai fouillé le net, je suis tombé sur ce thread et j'ai commencé à bosser en utilisant la requête founit plus haut.
Le soucis c'est que je n'arrivais pas à récupéré les droits exact sur les colonnes.
Donc voici 2 requêtes que j'utilise à l'heure actuelle.
En espérant que ca puisse servir à d'autres


Pour les tables:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT     user_name(p.grantor) AS GRANTOR, user_name(p.uid) AS GRANTEE, db_name() AS TABLE_CATALOG, user_name(o.uid) AS TABLE_SCHEMA, 
                      o.name AS TABLE_NAME, 
                      CASE p.action WHEN 26 THEN 'REFERENCES' WHEN 193 THEN 'SELECT' WHEN 195 THEN 'INSERT' WHEN 196 THEN 'DELETE' WHEN 197 THEN 'UPDATE'
                       END AS PRIVILEGE_TYPE, CASE WHEN p.protecttype = 205 THEN 'NO' ELSE 'YES' END AS IS_GRANTABLE
FROM         sysprotects p, sysobjects o
WHERE     (is_member(user_name(p.uid)) = 1 OR
                      p.grantor = user_id()) AND (p.protecttype = 204 OR
                      (p.protecttype = 205 AND NOT EXISTS
                          (SELECT     *
                            FROM          sysprotects p2
                            WHERE      p2.id = p.id AND p2.uid = p.uid AND p2.action = p.action AND p2.columns = p.columns AND p2.grantor = p.grantor AND 
                                                   p2.protecttype = 204))) AND p.action IN (26, 193, 195, 196, 197) AND p.id = o.id AND o.xtype IN ('U', 'V') AND 0 != (permissions(o.id) 
                      & CASE p.action WHEN 26 THEN 4 WHEN 193 THEN 1 WHEN 195 THEN 8 WHEN 196 THEN 16 WHEN 197 THEN 2 END)

Pour les colonnes:

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
SELECT     user_name(p.grantor) AS GRANTOR, user_name(p.uid) AS GRANTEE, db_name() AS TABLE_CATALOG, user_name(o.uid) AS TABLE_SCHEMA, 
                      o.name AS TABLE_NAME, c.name AS COLUMN_NAME, 
                      CASE p.action WHEN 193 THEN 'SELECT' WHEN 197 THEN 'UPDATE' ELSE 'REFERENCES' END AS PRIVILEGE_TYPE, 
                      CASE WHEN p.protecttype = 205 THEN 'NO' ELSE 'YES' END AS IS_GRANTABLE
FROM         sysprotects p, sysobjects o, syscolumns c
WHERE     (is_member(user_name(p.uid)) = 1 OR
                      p.grantor = user_id()) AND (p.protecttype = 204 OR
                      (p.protecttype = 205 AND NOT EXISTS
                          (SELECT     *
                            FROM          sysprotects p2
                            WHERE      p2.id = p.id AND p2.uid = p.uid AND p2.action = p.action AND p2.columns = p.columns AND p2.grantor = p.grantor AND 
                                                   p2.protecttype = 204))) AND p.action IN (26, 193, 197) AND p.id = o.id AND o.xtype IN ('U', 'V') AND o.id = c.id AND (((CONVERT(tinyint, 
                      substring(p.columns, 1, 1)) & 1) = 0 AND (CONVERT(int, substring(p.columns, c.colid / 8 + 1, 1)) & power(2, c.colid & 7)) != 0) OR
                      ((CONVERT(tinyint, substring(p.columns, 1, 1)) & 1) != 0 AND (CONVERT(int, substring(p.columns, c.colid / 8 + 1, 1)) & power(2, c.colid & 7)) = 0)) AND 
                      0 != (permissions(o.id, c.name) & CASE p.action WHEN 26 THEN 4 WHEN 193 THEN 1 WHEN 197 THEN 2 END)

Voilà