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é 😉
Business Geek 