Tout le monde connaît l'utilisation des rôles dans la sécurité des
applications. On n'y échappe pas avec SQL Server puisque l'on peut
définir (CREATE ROLE) et affecter
(sp_addrolemember) des rôles au sein de sa base de données. On
appelle ces rôles des : databases-roles.
SQL Server 2005 offre un autre type de rôle qui sont les :
application-roles. La philosophie de ces rôles est de donner
des droits à une application plutôt qu'aux utilisateurs en la mettant dans un
rôle particulier. J'ai longtemps tourné autour de cette fonctionnalité,
n'arrivant pas à me convaincre de son utilité. En effet, dans 99% des
applications que j'approche, l'application se connecte avec sa propre identité.
Donc, au niveau sécurité, je peux la configurer finement, indépendamment de ses
utilisateurs (qui eux peuvent être db_owner, sysadmin ou de parfaits inconnus
dans un autre contexte). En conséquence, j'avais écarté cette technique de ma
boîte à outils.
C'était sans compter sur mes stagiaires du moment qui ont voulu s'en
convaincre et nous avons repris la problématique du début. Donc reprenons :
Premièrement, créons un rôle d'application
CREATE APPLICATION ROLE app1 WITH PASSWORD =
'pass31', default_schema = Person
Nous parlons bien d'un rôle donc il est impossible de se connecter avec. Il
nous faut donc des logins classiques. Vous pouvez garder l'authentification
intégrée et autoriser les login windows ou bien en créer ou en réutiliser.
CREATE LOGIN [NT AUTHORITY\Authenticated
Users] FROM WINDOWS --tous les utilisateurs windows
CREATE LOGIN appUser WITH PASSWORD = 'appUser' --utilisateur
spécifique
La phase suivante que l'on a classiquement est de créer un utilisateur dans
la base de données et de la mapper sur un login. Cette phase est maintenant
inutile car on va faire en sorte que l'utilisateur soit vu comme l'application
par la base de données. Cela se fait via l'éxecution d'une procédure système
qui va faire du context switching.
EXEC sp_setapprole 'app1',
'pass31'
Maintenant, tout le code T-SQL est exécuté avec les droits du rôle
d'application. Pour vérifier, on peut appeler la fonction USER_NAME(). Pour
revenir au contexe d'éxécution précédent, il suffit de fermer la connexion ou
d'appeler la procédure système sp_unsetapprole (par contre, il
faudra conserver un token fourni par sp_setapprole et le
redonner à sp_unsetapprole).
Il ne reste plus qu'à intégrer cette technique dans une application. Pour
cela, on communique avec la base de données de façon classique sauf qu'en début
de connexion, on doit faire le changement de contexte comme présenté dans le
code ci-dessous. A noter qu'avec une architecture correcte (ie. une vraie
couche d'accès aux données), on masque aisément cette tuyauterie technique.
using (SqlConnection cnn = new
SqlConnection(
string.Format("data source=(local);initial catalog=Lab;user
id={0};password={1}", txtLogin.Text, txtPassword.Text)))
{
cnn.Open();
SqlCommand cmd = cnn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_setapprole";
cmd.Parameters.AddWithValue("@rolename", txtAppRole.Text);
cmd.Parameters.AddWithValue("@password", "pass31");
cmd.ExecuteNonQuery();
SqlDataAdapter adpater = new SqlDataAdapter("select top 50 firstname,
lastname, emailaddress from person.contact", cnn);
adpater.Fill(contactsDataSet);
cnn.Close();
}
Ici, c'est bien un utilisateur autorisé sur le serveur qui se connecte mais
les droits sont ceux de l'application. L'énorme avantage que je vois (mais
comment ai-je pu passer à côté) est le fait que si l'application tombe entre de
mauvaises mains, sans l'étape Login, elle ne pourra pas atteindre le niveau
base de données, même si le rôle d'application a tous les droits.
Aussi, si je récupère un login/mot de passe, je peux juste me connecter au
serveur mais je ne peux pas accéder aux bases. Inversement avec le rôle, avec
lequel on ne peut pas se connecter.
Passons quand même en revue les inconvénients. Le principal (et le
seul) est que je base toute l'authentification de mon application sur SQL
Server et donc je dois créer des login pour tous les utilisateurs. Bien sûr,
avec un peu d'architecture, on doit pouvoir rendre cela plus souple et
élégant.
Conclusion, grâce aux rôles d'application et au context switching,
on découple fortement la paire login/user et on rationnalise la gestion de
la sécurité des applications dans la base de données.
Merci Mathieu d'avoir insisté 
