Comprendre la notion d'application role sous Sql Server en 05 minutes

Une application rôle est au même titre que les database role une notion de la base de données qui va permettre de faciliter la gestion des permissions au niveau des objets depuis un point de vue applicatif.
Les exemples qui suivent sont utilisés dans le contexte d'AdventureWorks
Pour créer une application role :

create application role MonRole with password='MonMotdePasse' , DEFAULT_SCHEMA = Production

Pour vous donner un peu plus d'approche sur la notion, executez la commande suivante :

select * from sys.database_principals

L'interêt d'utiliser une application rôle

- Supposons que dans une société, le département HR accède la base via une application Web1, le département TRAVEL l'accède via un client WPF et le département SERVICE l'accède à partir d'une application WEB2. Chaque membre de ses départements est supposé n'avoir droit qu'aux objects qui les concernent dans la base, et qu'il ne doit pas avoir accès directement à la base ( dans ce sens, pensez sur le fait que sur un client WPF qui s'execute sur le poste de l'utilisateur, l'authentification peut etre les 'credentials' de l'utilisateur lui même ).

Dans ces conditions, il s'avère que le choix de la stratégie d'une application rôle est le moindre effort et la moins couteuse pour le département informatique.

Comment le mettre en oeuvre ? 

Dans ce sens, il faut créer une application role pour chaque département (cf plus haut).

create application role HR_Departement_Role with password='MonMotdePasse' , DEFAULT_SCHEMA = Production

 Il faut noter qu'une application rôle ne contiendra pas d'utilisateurs.

Dans le code application de l'application web destinée aux membres du HR par exemple 

- le code est laissé tres rudimentaire pour se concentrer au contexte principal de l'article

Si je l'execute, alors j'aurai l'erreur

 

L'erreur montre qu'un select n'est pas permis pour celui sous lequel s'execute le contexte courant.

Celui est  normal car une fois que nous ayions fait appel à l'activation de l'application role dans notre code (sp_setapprole), il faut que celui ci dispose de suffisamment de privilèges pour faire la requête select * from Product

Pour corriger 

grant select on schema::Production to HR_Departement_Role

 Cette commande attribue le droit de faire un select sur le schéma Production (sous lequel appartient la table Product) pour l'application role HR

Après relance, la requête retourne bien la liste voulue.

Ainsi nous avons vu qu'il nous a fallu :

- crèer les application roles et leur droit au niveau de la base

- ajouter 02 lignes de codes pour le changement de contexte d’exécution au niveau du code, et ce totalement indépendant de l'implémentation existante.

Intéressant non !