Masquage dynamique des données
S’applique à :
SQL Server 2016 (13.x) et versions ultérieures d’Azure SQL Database Azure SQL Managed Instance Azure Synapse AnalyticsSQL Database dans Microsoft Fabric
Le masquage des données dynamiques (DDM) limite l'exposition des données sensibles en les masquant pour les utilisateurs sans privilège. Il peut être utilisé pour simplifier considérablement la conception et le codage de la sécurité dans votre application.
Ce contenu s'applique généralement aux concepts spécifiques à SQL Server de masquage des données dynamiques. Le contenu spécifique à d'autres plateformes est disponible :
- Pour le masquage dynamique des données dans Azure SQL Database, Azure SQL Managed Instance et Azure Synapse Analytics, consultez Démarrage avec le masquage des données dynamiques de SQL Database.
- Pour le masquage des données dynamiques dans Microsoft Fabric, consultez Masquage des données dynamiques dans l'entrepôt de données Fabric.
Vue d'ensemble du masquage des données dynamiques
Le masquage dynamique des données permet d’empêcher les accès non autorisés à des données sensibles. Pour cela, les clients peuvent spécifier la quantité de données sensibles à exposer avec un effet minimal sur la couche d’application. Il peut être configuré sur les champs de base de données désignés afin de masquer les données sensibles dans les jeux de résultats des requêtes. Avec la fonctionnalité DDM, les données figurant dans la base de données ne sont pas modifiées. La fonctionnalité DDM est facile à utiliser avec des applications existantes, car les règles de masquage sont appliquées dans les résultats de la requête. De nombreuses applications peuvent masquer des données sensibles sans modifier les requêtes existantes.
- Une stratégie de masquage des données centrale agit directement sur les champs sensibles de la base de données.
- Désignez les utilisateurs ou les rôles privilégiés qui ont accès aux données sensibles.
- Le masquage dynamique des données a des fonctions de masquage complet et partiel, ainsi qu’un masque aléatoire pour les données numériques.
- Des commandes Transact-SQL simples définissent et gèrent les masques.
Le masquage des données dynamiques vise à limiter l’exposition des données sensibles, en empêchant les utilisateurs qui ne doivent pas pouvoir y accéder de les consulter. En revanche, le masquage des données dynamiques n’a pas pour but d’empêcher des utilisateurs d’une base de données de se connecter directement à celle-ci ou d’exécuter des requêtes exhaustives ayant pour effet d’exposer des éléments de données sensibles. Le masquage des données dynamiques est complémentaire à d’autres fonctionnalités de sécurité de SQL Server (audit, chiffrement, sécurité au niveau des lignes, etc.). Il est vivement recommandé de l’utiliser avec celles-ci pour mieux protéger les données sensibles contenues dans la base de données.
Le masquage des données dynamiques est disponible dans SQL Server 2016 (13.x) et Azure SQL Database. Vous le configurez à l’aide de commandes Transact-SQL. Pour plus d’informations sur la configuration du masquage dynamique des données via le portail Azure, consultez Prise en main du masquage dynamique des données de base de données SQL (portail Azure).
Remarque
Microsoft Entra ID était anciennement appelé Azure Active Directory (Azure AD).
Définir un masque de données dynamique
Il est possible de définir une règle de masquage sur une colonne d’une table, afin de masquer les données qui y figurent. Quatre types de masques sont disponibles.
| Fonction | Description | Examples |
|---|---|---|
| Par défaut | Masquage complet en fonction des types de données des champs désignés. Pour les données de type chaîne (string), utilisez XXXX (ou moins) si la taille du champ est inférieure à 4 caractères (char, nchar, varchar, nvarchar, text, ntext).Pour les données de type numérique, utilisez une valeur zéro (bigint, bit, decimal, int, money, numeric, smallint, smallmoney, tinyint, float, real). Pour les données de type date et heure, utilisez 1900-01-01 00:00:00.0000000 (date, datetime2, datetime, datetimeoffset, smalldatetime, time).Pour les données de type binaire, utilisez un seul octet de valeur ASCII 0 (binary, varbinary, image). |
Exemple de syntaxe de définition de colonne : Phone# varchar(12) MASKED WITH (FUNCTION = 'default()') NULLExemple de syntaxe alter : ALTER COLUMN Gender ADD MASKED WITH (FUNCTION = 'default()') |
Méthode de masquage qui affiche la première lettre d’une adresse de messagerie et le suffixe de constante « .com », sous la forme d’une adresse de messagerie. aXXX@XXXX.com. |
Exemple de syntaxe de définition : Email varchar(100) MASKED WITH (FUNCTION = 'email()') NULLExemple de syntaxe alter : ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()') |
|
| Random | Fonction de masquage aléatoire à utiliser sur tout type de données numérique pour masquer la valeur d’origine à l’aide d’une valeur aléatoire dans une plage spécifiée. | Exemple de syntaxe de définition : Account_Number bigint MASKED WITH (FUNCTION = 'random([start range], [end range])')Exemple de syntaxe alter : ALTER COLUMN [Month] ADD MASKED WITH (FUNCTION = 'random(1, 12)') |
| Chaîne personnalisée | Méthode de masquage qui affiche les première et dernière lettres, et ajoute une chaîne de remplissage personnalisée au milieu. prefix,[padding],suffixSi la valeur d’origine est trop courte pour occuper la totalité du masque, une partie du préfixe ou du suffixe n’est pas exposée. |
Exemple de syntaxe de définition : FirstName varchar(100) MASKED WITH (FUNCTION = 'partial(prefix,[padding],suffix)') NULLExemple de syntaxe alter : ALTER COLUMN [Phone Number] ADD MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)')Cela transforme un numéro de téléphone comme 555.123.1234 en 5XXXXXXX. Autre exemple : ALTER COLUMN [Phone Number] ADD MASKED WITH (FUNCTION = 'partial(5,"XXXXXXX",0)') Cela transforme un numéro de téléphone comme 555.123.1234 en 555.1XXXXXXX. |
| Datetime | S’applique à : SQL Server 2022 (16.x) Méthode de masquage pour la colonne définie avec le type de données datetime, datetime2, date, time, datetimeoffset, smalldatetime. Cela permet de masquer la partie year => datetime("Y"), month=> datetime("M"), day=>datetime("D"), hour=>datetime("h"), minute=>datetime("m") ou seconds=>datetime("s") du jour. |
Exemple sur la façon de masquer l'année de la valeur datetime :ALTER COLUMN BirthDay ADD MASKED WITH (FUNCTION = 'datetime("Y")')Exemple sur la façon de masquer le mois de la valeur datetime : ALTER COLUMN BirthDay ADD MASKED WITH (FUNCTION = 'datetime("M")')Exemple sur la façon de masquer la minute de la valeur datetime : ALTER COLUMN BirthDay ADD MASKED WITH (FUNCTION = 'datetime("m")') |
autorisations
Les utilisateurs disposant de l’autorisation SELECT sur une table peuvent afficher les données de celle-ci. Les colonnes définies comme masquées affichent les données masquées. Accordez l'autorisation UNMASK à un utilisateur pour lui permettre de récupérer des données non masquées des colonnes pour lesquelles le masquage est défini.
Les utilisateurs administratifs et les rôles peuvent toujours afficher des données non masquées via l'autorisation CONTROL, qui inclut à la fois les autorisations ALTER ANY MASK et UNMASK. Les utilisateurs ou rôles administratifs, tels que sysadmin ou db_owner, ont par conception des autorisations de CONTRÔLE sur la base de données, et peuvent voir des données non masquées.
Vous n’avez pas besoin d’autorisation spéciale pour créer une table avec un masque de données dynamiques. Les autorisations de schéma standard CREATE TABLE et ALTER suffisent.
Pour ajouter, remplacer ou supprimer le masque d’une colonne, vous devez disposer des autorisations ALTER ANY MASK et ALTER sur la table. Il convient d’octroyer l’autorisation ALTER ANY MASK à un responsable sécurité.
Remarque
L'autorisation UNMASK n'influence pas la visibilité des métadonnées : l'octroi de l'autorisation UNMASK seule ne divulgue pas de métadonnées. L’autorisation UNMASK doit toujours être accompagnée d’une autorisation SELECT pour avoir un effet. Exemple : l’octroi d’UNMASK sur l’étendue de la base de données et l’octroi de SELECT sur une table individuelle aura le résultat que l’utilisateur ne peut voir que les métadonnées de la table individuelle à partir de laquelle il peut sélectionner, pas d’autres. Consultez également Configuration de la visibilité des métadonnées.
Bonnes pratiques et cas d’usage courants
La création d’un masque sur une colonne n’empêche pas les mises à jour de celle-ci. Par conséquent, si les utilisateurs reçoivent des données masquées quand ils interrogent une colonne masquée, ils peuvent mettre à jour les données s’ils disposent d’autorisations en écriture. Il convient néanmoins d’utiliser une stratégie de contrôle d’accès appropriée pour limiter les autorisations de mise à jour.
L'utilisation de
SELECT INTOou deINSERT INTOpour copier les données d'une colonne masquée dans une autre table a pour effet de masquer les données dans la table cible (en supposant qu'elles soient exportées par un utilisateur ne disposant pas des privilèges UNMASK).Un masquage dynamique des données est appliqué pendant l'exécution d'opérations d'importation et d'exportation dans SQL Server. Une base de données contenant des colonnes masquées produit un fichier de données exportées dont les données sont masquées (en supposant qu'elle est exportée par un utilisateur sans privilèges UNMASK), et la base de données importée contient des données masquées statiquement.
Interrogation de colonnes masquées
Utilisez la vue sys.masked_columns pour interroger les colonnes de table auxquelles une fonction de masquage a été appliquée. Celle-ci hérite de la vue sys.columns. Elle retourne toutes les colonnes de la vue sys.columns, ainsi que les colonnes is_masked et masking_function, en indiquant si les colonnes sont masquées et, dans ce cas, la fonction de masquage est définie. Cette vue présente uniquement les colonnes auxquelles une fonction de masquage est appliquée.
SELECT c.name, tbl.name as table_name, c.is_masked, c.masking_function
FROM sys.masked_columns AS c
JOIN sys.tables AS tbl
ON c.[object_id] = tbl.[object_id]
WHERE is_masked = 1;
Limitations et restrictions
Les utilisateurs avec des autorisations CONTROL SERVER ou CONTROL au niveau de la base de données peuvent voir des données masquées dans leur forme originale. Cela inclut les utilisateurs administrateurs ou les rôles, tels que sysadmin, db_owner, etc.
Il n’est pas possible de définir une règle de masquage pour les types de colonnes suivants :
Colonnes chiffrées (Always Encrypted)
FILESTREAM
COLUMN_SET, ou colonne éparse faisant partie d’un jeu de colonnes.
Un masque ne peut pas être configuré sur une colonne calculée, mais si la colonne calculée dépend d'une colonne avec un MASK, alors la colonne calculée renvoie des données masquées.
Une colonne avec masquage de données ne peut pas être une clé pour un index FULLTEXT.
Colonne d’une table externe PolyBase.
Pour les utilisateurs non dotés de l’autorisation UNMASK, les instructions dépréciées READTEXT, UPDATETEXT et WRITETEXT ne fonctionnent pas correctement sur une colonne configurée pour un masquage de données dynamiques.
L’ajout d’un masque de données dynamiques est implémenté comme un changement de schéma dans la table sous-jacente, et ne peut donc pas être effectué sur une colonne ayant des dépendances (comme une colonne référencée par une colonne calculée). La tentative d’ajout d’un masque de données dynamique sur des colonnes avec dépendance entraîne une erreur, ALTER TABLE ALTER COLUMN _columnname_ failed because one or more objects access this column. Pour contourner cette restriction, vous pouvez tout d’abord supprimer la dépendance, puis ajouter le masque de données dynamiques et recréer la dépendance. Par exemple, si la dépendance est liée à un index qui dépend de cette colonne, vous pouvez supprimer l’index, ajouter le masque, puis recréer l’index dépendant.
Chaque fois que vous projetez une expression faisant référence à une colonne pour laquelle une fonction de masquage de données est définie, l'expression est également masquée. Quelle que soit la fonction (par défaut, e-mail, aléatoire, chaîne personnalisée) utilisée pour masquer la colonne référencée, l’expression résultante sera toujours masquée avec la fonction par défaut.
Les requêtes entre bases de données couvrant deux bases de données Azure SQL Database différentes ou des bases de données hébergées sur différentes instances SQL Server et impliquant une opération quelconque de comparaison ou de jointure sur des colonnes MASKED fournissent des résultats incorrects. Les résultats retournés par le serveur distant sont déjà sous forme MASKED et ne conviennent pas aux opérations de comparaison ou de jointure effectuées localement.
Remarque
Dynamic Data Masking n’est pas pris en charge lorsque la table de base sous-jacente est référencée dans une vue indexée.
Remarque relative à la sécurité : ignorer le masquage à l’aide de techniques d’inférence ou de force brute
Le masquage dynamique des données est conçu pour simplifier le développement d’applications en limitant l’exposition des données dans un ensemble de requêtes prédéfinies utilisées par l’application. Bien que le masquage de données dynamiques puisse également s'avérer utile pour empêcher une exposition accidentelle des données sensibles lorsque vous accédez directement à une base de données de production, il est important de noter que les utilisateurs non privilégiés bénéficiant d'autorisations de requête ad hoc peuvent appliquer des techniques pour accéder aux données réelles. S'il est nécessaire d'octroyer un tel accès ad hoc, l'audit doit servir à superviser toutes les activités de base de données pour atténuer ce risque.
Par exemple, considérez un principal de base de données qui dispose de privilèges suffisants pour exécuter des requêtes ad hoc sur la base de données et essaie de « deviner » les données sous-jacentes, pour enfin déduire les valeurs réelles. Supposons que nous disposons d’un masque défini sur la colonne [Employee].[Salary], et que cet utilisateur se connecte directement à la base de données et commence à deviner les valeurs, pour enfin déduire la valeur [Salary] dans la table Employees :
SELECT ID, Name, Salary FROM Employees
WHERE Salary > 99999 and Salary < 100001;
Id Nom Salaire 62543 Jane Doe 0 91245 John Smith 0
Cet exemple montre que Dynamic Data Masking ne doit pas être utilisé seul pour sécuriser totalement des données sensibles contre des utilisateurs exécutant des requêtes ad hoc sur la base de données. La fonctionnalité convient pour empêcher une exposition accidentelle des données sensibles, mais elle ne protège pas contre des intentions malveillantes de déduire les données sous-jacentes.
Il est important de gérer correctement les autorisations sur la base de données, et de toujours suivre le principe des autorisations minimales nécessaires. En outre, n’oubliez pas de laisser l’option d’audit activée afin de suivre toutes les activités survenant sur la base de données.
Autorisations précises introduites dans SQL Server 2022
À partir de SQL Server 2022 (16.x), vous pouvez empêcher tout accès non autorisé à des données sensibles et contrôler cet accès en les masquant à un utilisateur non autorisé à différents niveaux de la base de données. Vous pouvez accorder ou révoquer l'autorisation UNMASK au niveau de la base de données, du schéma, de la table ou de la colonne à un utilisateur, un rôle de base de données, une identité Microsoft Entra ou un groupe Microsoft Entra. Cette amélioration permet de contrôler et de limiter précisément l’accès non autorisé aux données stockées dans la base de données et d’améliorer la gestion de la sécurité des données.
Examples
Créer un masque dynamique des données
L’exemple suivant crée une table avec trois types différents de masques dynamiques des données. L’exemple remplit la table, puis affiche le résultat.
-- schema to contain user tables
CREATE SCHEMA Data;
GO
-- table with masked columns
CREATE TABLE Data.Membership (
MemberID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED,
FirstName VARCHAR(100) MASKED WITH (FUNCTION = 'partial(1, "xxxxx", 1)') NULL,
LastName VARCHAR(100) NOT NULL,
Phone VARCHAR(12) MASKED WITH (FUNCTION = 'default()') NULL,
Email VARCHAR(100) MASKED WITH (FUNCTION = 'email()') NOT NULL,
DiscountCode SMALLINT MASKED WITH (FUNCTION = 'random(1, 100)') NULL
);
-- inserting sample data
INSERT INTO Data.Membership (FirstName, LastName, Phone, Email, DiscountCode)
VALUES
('Roberto', 'Tamburello', '555.123.4567', 'RTamburello@contoso.com', 10),
('Janice', 'Galvin', '555.123.4568', 'JGalvin@contoso.com.co', 5),
('Shakti', 'Menon', '555.123.4570', 'SMenon@contoso.net', 50),
('Zheng', 'Mu', '555.123.4569', 'ZMu@contoso.net', 40);
GO
Un nouvel utilisateur est créé et reçoit l’autorisation SELECT sur le schéma où réside la table. Les requêtes exécutées en tant que MaskingTestUser affichent les données masquées.
CREATE USER MaskingTestUser WITHOUT LOGIN;
GRANT SELECT ON SCHEMA::Data TO MaskingTestUser;
-- impersonate for testing:
EXECUTE AS USER = 'MaskingTestUser';
SELECT * FROM Data.Membership;
REVERT;
Le résultat montre les masques en modifiant les données de :
1 Roberto Tamburello 555.123.4567 RTamburello@contoso.com 10
en :
1 Rxxxxxo Tamburello xxxx RXXX@XXXX.com 91
où le nombre dans DiscountCode est aléatoire pour chaque résultat de requête.
Ajouter ou modifier un masque sur une colonne existante
Utilisez l'instruction ALTER TABLE pour ajouter un masque à une colonne existante de la table ou pour modifier le masque appliqué à cette colonne.
L’exemple suivant ajoute une fonction de masquage à la colonne LastName :
ALTER TABLE Data.Membership
ALTER COLUMN LastName ADD MASKED WITH (FUNCTION = 'partial(2,"xxxx",0)');
L’exemple suivant modifie une fonction de masquage appliquée à la colonne LastName :
ALTER TABLE Data.Membership
ALTER COLUMN LastName VARCHAR(100) MASKED WITH (FUNCTION = 'default()');
Octroyer les autorisations nécessaires à la visualisation des données non masquées
L’octroi de l’autorisation UNMASK permet à MaskingTestUser d’afficher les données non masquées.
GRANT UNMASK TO MaskingTestUser;
EXECUTE AS USER = 'MaskingTestUser';
SELECT * FROM Data.Membership;
REVERT;
-- Removing the UNMASK permission
REVOKE UNMASK TO MaskingTestUser;
Supprimer un masque dynamique des données
L’instruction suivante supprime le masque appliqué à la colonne LastName , créé dans l’exemple précédent :
ALTER TABLE Data.Membership
ALTER COLUMN LastName DROP MASKED;
Exemples d’autorisations précises
Créer un schéma pour contenir des tables utilisateur :
CREATE SCHEMA Data; GOCréer une table avec des colonnes masquées :
CREATE TABLE Data.Membership ( MemberID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED, FirstName VARCHAR(100) MASKED WITH (FUNCTION = 'partial(1, "xxxxx", 1)') NULL, LastName VARCHAR(100) NOT NULL, Phone VARCHAR(12) MASKED WITH (FUNCTION = 'default()') NULL, Email VARCHAR(100) MASKED WITH (FUNCTION = 'email()') NOT NULL, DiscountCode SMALLINT MASKED WITH (FUNCTION = 'random(1, 100)') NULL, BirthDay DATETIME MASKED WITH (FUNCTION = 'default()') NULL );Insérer des exemples de données :
INSERT INTO Data.Membership (FirstName, LastName, Phone, Email, DiscountCode, BirthDay) VALUES ('Roberto', 'Tamburello', '555.123.4567', 'RTamburello@contoso.com', 10, '1985-01-25 03:25:05'), ('Janice', 'Galvin', '555.123.4568', 'JGalvin@contoso.com.co', 5, '1990-05-14 11:30:00'), ('Shakti', 'Menon', '555.123.4570', 'SMenon@contoso.net', 50, '2004-02-29 14:20:10'), ('Zheng', 'Mu', '555.123.4569', 'ZMu@contoso.net', 40, '1990-03-01 06:00:00');Créer un schéma pour contenir des tables de service :
CREATE SCHEMA Service; GOCréer une table de service avec des colonnes masquées :
CREATE TABLE Service.Feedback ( MemberID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED, Feedback VARCHAR(100) MASKED WITH (FUNCTION = 'default()') NULL, Rating INT MASKED WITH (FUNCTION = 'default()'), Received_On DATETIME );Insérer des exemples de données :
INSERT INTO Service.Feedback(Feedback, Rating, Received_On) VALUES ('Good', 4, '2022-01-25 11:25:05'), ('Excellent', 5, '2021-12-22 08:10:07'), ('Average', 3, '2021-09-15 09:00:00');Créer différents utilisateurs dans la base de données :
CREATE USER ServiceAttendant WITHOUT LOGIN; GO CREATE USER ServiceLead WITHOUT LOGIN; GO CREATE USER ServiceManager WITHOUT LOGIN; GO CREATE USER ServiceHead WITHOUT LOGIN; GOAccorder des autorisations de lecture aux utilisateurs de la base de données :
ALTER ROLE db_datareader ADD MEMBER ServiceAttendant; ALTER ROLE db_datareader ADD MEMBER ServiceLead; ALTER ROLE db_datareader ADD MEMBER ServiceManager; ALTER ROLE db_datareader ADD MEMBER ServiceHead;Accorder différentes autorisations UNMASK aux utilisateurs :
--Grant column level UNMASK permission to ServiceAttendant GRANT UNMASK ON Data.Membership(FirstName) TO ServiceAttendant; -- Grant table level UNMASK permission to ServiceLead GRANT UNMASK ON Data.Membership TO ServiceLead; -- Grant schema level UNMASK permission to ServiceManager GRANT UNMASK ON SCHEMA::Data TO ServiceManager; GRANT UNMASK ON SCHEMA::Service TO ServiceManager; --Grant database level UNMASK permission to ServiceHead; GRANT UNMASK TO ServiceHead;Interroger les données dans le contexte de l’utilisateur
ServiceAttendant:EXECUTE AS USER = 'ServiceAttendant'; SELECT MemberID, FirstName, LastName, Phone, Email, BirthDay FROM Data.Membership; SELECT MemberID, Feedback, Rating FROM Service.Feedback; REVERT;Interroger les données dans le contexte de l’utilisateur
ServiceLead:EXECUTE AS USER = 'ServiceLead'; SELECT MemberID, FirstName, LastName, Phone, Email, BirthDay FROM Data.Membership; SELECT MemberID, Feedback, Rating FROM Service.Feedback; REVERT;Interroger les données dans le contexte de l’utilisateur
ServiceManager:EXECUTE AS USER = 'ServiceManager'; SELECT MemberID, FirstName, LastName, Phone, Email, BirthDay FROM Data.Membership; SELECT MemberID, Feedback, Rating FROM Service.Feedback; REVERT;Interroger les données dans le contexte de l’utilisateur
ServiceHeadEXECUTE AS USER = 'ServiceHead'; SELECT MemberID, FirstName, LastName, Phone, Email, BirthDay FROM Data.Membership; SELECT MemberID, Feedback, Rating FROM Service.Feedback; REVERT;Pour révoquer les autorisations UNMASK, utilisez les instructions T-SQL suivantes :
REVOKE UNMASK ON Data.Membership(FirstName) FROM ServiceAttendant; REVOKE UNMASK ON Data.Membership FROM ServiceLead; REVOKE UNMASK ON SCHEMA::Data FROM ServiceManager; REVOKE UNMASK ON SCHEMA::Service FROM ServiceManager; REVOKE UNMASK FROM ServiceHead;