Discussion :

[Wil14]

Bonjour,

Je voudrais sécuriser mon application en utilisant un rôle d'application (accès en lecture/écriture).
Le problème est que mon application utilise plusieurs bases de données sur le même serveur.

Avez-vous une astuce pour contourner ce problème ?

[Ptit_Dje]

Le scope du role etant la base de donnees, il vous faudra definir ce role dans chacunes des bases utilisees par votre application.

[Wil14]

En effet, j'ai commencé par ca mais je ne passe pas :

Création du rôle + ajout à db_datareader sur Base1 et 2.
Dans base 1 :
-sp_setapprole 'MonRole', 'MonMotdePasse'
-SELECT * from Base2.dbo.MaTable
->Le principal de serveur "MonDomaine\UserNt" ne peut pas accéder à la base de données "Base2" dans le contexte de sécurité actuel.

[Ptit_Dje]

A priori je ne vois pas de solution.

A moins de donner des permissions au user guest dans les autres bases de donnees, ce qui n'est pas tres secure...

Because application roles are a database-level principal, they can access other databases only through permissions granted in those databases to the guest user account. Therefore, any database in which the guest user account has been disabled will be inaccessible to application roles in other databases

[Wil14]

En effet pas très sécure.
il faut donc que je change de rôle lorsque je change de base et adieu les requêtes avec jointure sur x base.

[mikedavem]

Vous pouvez passer par des procédures stockées qui pourront faire des requêtes inter-bases mais il faudra activer l'option de bases de données TRUSTWORTHY dans ce cas.

Une 2eme solution est de signer votre / vos procédures par des certificats.

++

[Ptit_Dje]

TRUSTWORTHY ne fonctionne pas avec les roles applicatifs.
De plus d'un point de vue sécurité, cette option de base n'est pas sans effets...

Signer une SP avec un certificat n'aide pas non plus dans un cas d'utilisation avec des roles applicatifs.

Pour ce qui est de faire des requêtes inter-bases, cela est possible simplement avec un login dédié ayant les droits nécessaires dans les différentes bases.

Mikedavem, peux tu expliquer les approches que tu as mentionnée et dans quel contexte tu replaces leur utilisation ?

[mikedavem]

TRUSTWORTHY ne fonctionne pas avec les roles applicatifs.
De plus d'un point de vue sécurité, cette option de base n'est pas sans effets...

Non effectivement mai il faut ruser en utilisant l'impersonnalisation dans les procédures stockées ... pas le choix. Effectivement il y a un prix à payer en activant cette option de bases de données. Elle ouvre quelques failles en matière de sécurité.

Pour ce qui est de faire des requêtes inter-bases, cela est possible simplement avec un login dédié ayant les droits nécessaires dans les différentes bases.

Peut être la solution la plus simple en effet et personnellement j'opterai pour cette dernière car les rôles d'applications ne sont pas fait pour faire du "cross database". Cependant si un certain niveau de sécurité est requis on peut utiliser les applications roles par le mécanisme d'impersonnalisation EXECUTE AS + certificats (pour éviter l'activation de l'option TRUSTWORTHY)

++

[Ptit_Dje]

Cependant si un certain niveau de sécurité est requis on peut utiliser les applications roles par le mécanisme d'impersonnalisation EXECUTE AS + certificats (pour éviter l'activation de l'option TRUSTWORTHY)

Personellement je comprend toujours pas et n'arrive pas à reproduire.
Sans doute car les certificats et TRUSTWORTHY ne sont pas des choses avec lesquelles j'ai tendance à travailler.

Si je reprend ta proposition, il faudrait:
- Creer un role d'application
- Creer un certificat
- Creer une sp qui fait une query inter-base utilisant execute as (as quoi ? SELF ? OWNER ? CALLER ?)
- Signer la procedure avec le certificat
- Granter au role d'application l'execution de la stored proc
- Se connecter a la base de données
- executer sp_setapprole
- executer la stored procedure

Est ce correct ?
Aurais tu un script de test à fournir ?

[mikedavem]

La mise en place du mécanisme d'auhentification à base de certificats n'est pas simple en soi mais elle permet d'être robuste en terme de sécurité. J'ai pu mettre en place ce genre de mécanisme sous une version différente ... pas simple au début.

Je me base sur la documentation msdn suivante qui explique le cross database selon 2 méthodes : par les database_owner et trustworthy ou les certificats.

Voici un script de test qui permet d'utiliser un rôle d'application dans un contexte cross-database :

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
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
 
-- Création base test + table t_test
create database test;
go
 
use test;
go
 
create table t_test ( col1 int );
go
 
-- Création base test2 + table t_test2
create database test2;
go
 
use test2;
go
 
create table t_test2 ( col1 int );
go
 
-- creation d'un login crossdb_login qui ne sera utilisé uniquement pour le contexte cross db.
-- Ce login peut être désactivé
use master;
go
 
create login crossdb_login with password = 'P@ssword';
alter login crossdb_login disable;
 
-- création de 2 utilisateurs cross_db dans les bases de données test et test2
use test
go
 
create user crossdb_user FOR LOGIN crossdb_login
go
 
use test2
go
 
create user crossdb_user FOR LOGIN crossdb_login
go
 
-- on donne le privilège select pour l'utilisateur crossdb_user dans la base de données test2
grant select on dbo.t_test2 to crossdb_user;
 
use test;
go
 
-- Création d'une procédure stockée dans la base test
-- pour lire la table t_test2 de la base test2 --> requête cross database
-- on impersonalise le contexte avec l'utilisateur crossdb_user (nécessaire qd on utilisera le role 
-- d'application)
CREATE PROC sp_test
WITH EXECUTE AS 'crossdb_user'
AS
  -- select from a table
  SELECT * FROM test2.dbo.t_test2
 
go
 
-- Création d'un certificat pour signer le module sp_test
CREATE CERTIFICATE signing_cert 
ENCRYPTION BY PASSWORD = 'P@ssword' 
WITH SUBJECT = 'signing cert'
go
 
-- signature du module sp_test avec le certificat signing_cert
ADD SIGNATURE TO sp_test 
BY CERTIFICATE signing_cert 
WITH PASSWORD = 'P@ssword'
 
-- On initie une sauvegarde du certificat qui sera utilisé par la suite
-- en tant que mapping avec un login signing_cert
BACKUP CERTIFICATE signing_cert TO FILE = 'E:\signing_cert.cer'
go
 
USE master;
go
 
-- Création du certificat au niveau serveur
CREATE CERTIFICATE signing_cert FROM FILE = 'E:\signing_cert.cer'
go
 
-- Création du login signing_cert mappé au certificat signing_cert
CREATE LOGIN signing_cert FROM CERTIFICATE signing_cert 
go
 
 
use test2
go
 
-- Création d'un utilisateur signing_cert à partir du certificat signing_cert (login)
CREATE USER signing_cert FOR LOGIN signing_cert;
go
 
-- On donne le droit d'authentification dans le context d'impersonnalisation à l'utilisateur signing_cert
GRANT AUTHENTICATE TO signing_cert 
go
 
-- TEST --
use test
go
 
-- Création d'un role d'application qui aura les droits d'exécution sur la proc sp_test
CREATE APPLICATION ROLE approle_test WITH PASSWORD = 'P@ssword'
go
 
-- On donne le droit d'exécution au role d'application approle_test
GRANT EXECUTE ON sp_test to approle_test
go
 
EXEC sp_setapprole 'approle_test', 'P@ssword';
 
-- On tente une requête cross database dans le contexte du role d'application
--> Une erreur se produit
SELECT * FROM test2.dbo.t_test2
 
-- On tente la lecture de la table t_test2 depuis la procédure stockée sp_test
-- signé avec un certificat 
-- dans le contexte d'impersonnalisation 
--> OK
EXEC sp_test;

++

[Ptit_Dje]

Nice ! Merci!