Interdire le SELECT * ne fonctionne pas dans une procédure stockée

[seb.49]

Bonjour,

Je voudrais empecher le SELECT * sur les tables de ma base.

J'ai donc ajouté une colonne DUMMY_COLUMN dans mes tables
puis exécuter la commande suivante

Code :

DENY SELECT ON OBJECT::MA_TABLE(DUMMY_COLUMN) TO User1;

ca marche parfaitement quand je fait SELECT * FROM MA_TABLE mais quand ce meme SELECT * est dans une procédure stockée, je peux exécuter cette dernière sans problème.

Comment c'est possible et comment empecher le SELECT * dans la procédure stockée ?

[aieeeuuuuu]

Bonjour

interdisez l’exécution de la procédure stockée en question.

Code SQL :

1
2
 
DENY EXECUTE ON Ma_sp TO user1

[seb.49]

Merci mais c'est pas ce que je recherche.

Dans mon programme j'ai plein de procédure, je veux les corriger pour retirer les select * je dois donc pouvoir les exécuter


Ce que je veux en réalite, c'est lancer le programme et au fur et à mesure des plantages à cause des select * interdit corriger les procédures pour remplacer le "*" par la bonne liste de champs

[mikedavem]

Bonjour,

Pourquoi ne pas chercher les procédures qui contiennent l'instruction "SELECT *" et les corriger par la suite ?

Code :

1
2
3
4
5
6
7
SELECT 
 p.name,
 m.definition
FROM sys.sql_modules AS m
INNER JOIN sys.procedures AS p
 ON m.[object_id] = p.[object_id]
WHERE m.definition LIKE '%SELECT *%'

++

[aieeeuuuuu]

c'est lancer le programme et au fur et à mesure des plantages à cause des select * interdit corriger les procédures

vous gagnerez votre temps en générant les scripts de toutes vos procédures (avec SSMS, ça se fait en quelques clics), et de passer en revue les scripts générés...
vous risquerez moins d'en oublier en plus

voici une requete (que vous pourrez adapter) qui vous donne la liste des privileges pour un utilisateur donné :

Code SQL :

1
2
3
4
5
6
7
8
9
10
11
12
13
 
 
SELECT 
	OBJECT_NAME(c.object_id) AS NomTable, 
	c.name AS NomColonne, 
	permission_name, 
	state_desc
FROM sys.database_permissions p
LEFT OUTER JOIN sys.COLUMNS c
	ON  c.object_id = p.major_id
	AND c.column_id = p.minor_id
WHERE grantee_principal_id = user_id('toto')
ORDER BY OBJECT_NAME(c.object_id) , column_id

[seb.49]

Une meme procédure aujourd'hui avec le select * pourra disparaitre au profit de plusieurs procédure avec des listes de champs bien définie donc je dois intercepter les erreurs dans mon programme avant de corriger les procédures

[mikedavem]

Encore une fois pourquoi attendre l'erreur pour intercepter et pourquoi ne pas directement corriger à la source ? Qu'est ce qui vous empêche de faire cela ?

Qui corrige les procédures ? Vous ? Une autre équipe ?

++

[seb.49]

Peut importe le pourquoi du comment, est ce qu'on peut revenir à ma question initiale ?

[SQLpro]

1) oui
2) c'est pas possible.

La seule possibilité est donc de corriger le code des SP.

Pour trouver vos SELECT * :

Code :

1
2
3
4
SELECT *
FROM   INFORMATION_SCHEMA.ROUTINES
WHERE  ROUTINE_TYPE = 'PROCEDURE'
 AND   ROUTINE_DEFINITION LIKE '%SELECT %*%'

A +

[mikedavem]

D'ailleurs ceci est également valable pour les vues. Le SELECT * sur la table à l'intérieur d'une vue va également fonctionner sans aucun problème.

De plus il y a quand même des effets de bords à faire cela :

Les requêtes avec COUNT(*) ou COUNT(1) vont également planter ...
C'est également le cas des parties de codes comme IF EXISTS (SELECT * / 1 FROM ..) ....

++

[aieeeuuuuu]

Envoyé par seb.49

Peut importe le pourquoi du comment, est ce qu'on peut revenir à ma question initiale ?

Si vous insistez dans une solution tordue... (si on vous posait ces questions, c'était pour trouver une solution plus adéquate mais bon, ... )

Si vous voulez que les priviléges sur les objets soient vérifiés à l’intérieur de vos procédures stockées qui utilisent ces objets, il faut que le propriétaire des objets soit différent du propriétaire des procédures...

Vous pouvez donc par exemple rendre votre User1 (ou un autre utilisateur du moment qu'il n'est pas propriétaire des tables) propriétaire de vos SP :

Code SQL :

1
2
 
ALTER AUTHORIZATION ON LaProcedure TO User1

à faire pour chaque procédure...

ceci vous y aidera :

Code SQL :

1
2
3
4
 
SELECT 'ALTER AUTHORIZATION ON ' + name + ' TO User1;
'
FROM sys.procedures