Module 4

Protection des données avec

chiffrement et d'audit
Présentation du module

• Options pour l'audit d'accès aux données dans

SQL Server
• Implémentation d’Audit SQL server
• Gestion d’Audit SQL server
• Protection des données avec chiffrement
Leçon 1: Options d'audit d'accès aux données
dans SQL Server

• Discussion: Audit d'accès aux données

• Activation des critères communs de conformité
L’audit avec des Déclencheurs
• L’audit avec tableaux temporelles
démonstration de : Audit avec des tables
temporelles
Discussion: Audit des accès des données

• Pourquoi l’audit est il nécessaire?

• Quelles sont les méthodes que vous avez
utilisées pour l’audit ?
• Quelles sont les limites des méthodes utilisées?
• Quelles sont les normes exigeant l’audit
auxqelles votre organisation doit se conformer?
Activation de la conformité des critères
communs

• Le respect des critères communs:Common Criteria

• Ratifiée en tant que norme internationale en 1999
• Remplace C2
• La norme ISO 15408

• Activer l’ option de configuration “respect des

critères communs activé”à l'aide de sp_configure:
• Protection des informations résiduelles (RIP)
• Possibilité de consulter les statistiques de connexion
• GRANT sur Colonne ne remplace pas DENY sur table
le script supplémentaire doit être exécuté pour se
conformer aux critères communs au niveau d'assurance
level 4+ applelé : (EAL4 +)
Audit avec Triggers

• Les déclencheurs peuvent fournir une partie

d'une solution d'audit:
• Déclencheurs DML pour la modification des données
• Déclencheurs Ouverture de session pour le suivi
des connexions
• Déclencheurs DDL Pour la modification de schéma

• Limites:
• Impact sur les performances (déclencheurs DML)
• les utilisateurs expérimentés peuvent désactiver les
déclencheurs
• Le manque de déclencheurs pour SELECT
• Ordre de déclenchement doit être géré
Audit des tables temporelles
• Le moteur de base de données enregistre
automatiquement les dates valides des enregistrements
dans la base de données au fur et à mesure qu’ils sont
modifiées
• Configurées dans le cadre de la définition de table; aucun
code supplémentaire n’est requis

• tables temporelles : limitations:

• Pas d’audit pour les commandes SELECT
• INSERT, UPDATE et DELETE toutes vérifiées de la même
manière
• table L'histoirique sera dans la même base de données
• Suivi des utilisateurs nécessite l'ajout d'une colonne
SUSER_SNAME à la table pour contenir l'identité de
l'utilisateur
CREATE TABLE dbo.Manager
( ManagerId int NOT NULL PRIMARY KEY,
ManagerName nvarchar(50) NOT NULL,
ManagerPassword varbinary(200) NOT NULL,
ValidFrom datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
ValidTo datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
ChangedBy sysname NOT NULL CONSTRAINT DF_Employee_ChangedBy DEFAULT
(SUSER_SNAME()),
PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ManagerHistory));
GO
Démonstration: Audit des tables temporelles

Dans cette démonstration, vous verrez une

méthode pour l'utilisation de tables temporelles
comme un outil d'audit
 Leçon 2: Mise en œuvre d'audit de SQL Server
• Introduction aux Événements étendus
• Introduction à l’audit SQL Server
• Définition d'audit serveur
• Audit des actions et des groupes d'action
• Création des Spécifications de l'audit du serveur
• Création des Spécifications de l'audit de base de données
• Vues de gestion dynamique et vues système liées à l'audit
• Audit dans SQL Azure Database
• Démonstration: Utilisation d’audit SQL Server
• Evénements d’audit personnalisés
• Démonstration: Utilisation des Evénements d’audit
personnalisés
Introduction aux événements étendus

• Événements étendus est un objectif général,

cadre de suivi événementiel
• Audit SQL Server est basé sur les événements
étendus
• Terminologie:
• un événement
• Cible
• action
• Prédicat
• Type et carte
• Session
Introduction à l’audit SQL Server

• Audit au niveau du serveur

• Toutes les éditions de SQL Server

• Audit niveau de base de données

• Enterprise, Developer et d'évaluation

• Terminologie:
• serveur d'audit
• Spécification audit du serveur
• Spécification audit Base de données
• Actions
• Groupes d'action
• Cible
Définition d'un audit du serveur

• Spécifier:
• Cible
• délai de file d'attente
• Action en cas d'échec

• Définir STATE= ON pour active l’audit

CREATE SERVER AUDIT SecurityAudit
TO FILE
(FILEPATH = '\\MIA-SQL\AuditFiles\' ,MAXSIZE = 0 MB
,MAX_ROLLOVER_FILES = 2147483647 ,RESERVE_DISK_SPACE =
OFF)
WITH
(QUEUE_DELAY = 1000 ,ON_FAILURE = FAIL_OPERATION);
GO

ALTER SERVER AUDIT SecurityAudit

WITH (STATE = ON);
Actions d'audit et les groupes d'action

• Groupes d'action
• au niveau du serveur
• niveau de base de données
• Niveau d'audit

• Actions
• niveau de base de données

• Les actions et les groupes d'action sont liés à un

audit avec une spécification d'audit
Création Spécifications d'audit de serveur

• Spécifier:
• Audit
• Des groupes d'action à inclure
• Etat

CREATE SERVER AUDIT SPECIFICATION AuditLogins

FOR SERVER AUDIT SecurityAudit
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP)
WITH (STATE = ON);

Les spécifications d’audit serveur pour inclure un ou plusieurs groupes

d'actions dans l'audit
Création Spécifications de l'audit de base de
données

• Spécifier:
• Audit
• Groupes d'action
• Actions sur les objets sécurisables spécifiques
• Peut être filtré par les entités sécurisées de bases de données
spécifiques
• Etat

CREATE DATABASE AUDIT SPECIFICATION DBSecurity

FOR SERVER AUDIT SecurityAudit
ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),
ADD (SELECT ON SCHEMA::HumanResources BY db_datareader)
WITH (STATE = ON);

Les spécifications d’audit database pour inclure

un ou plusieurs groupes d'actions dans l'audit
Vues de gestion dynamique et vues système
liées à l'audit

• DMVs
• sys.dm_audit_actions
• sys.dm_audit_class_type_map
• sys.dm_server_audit_status

• Vues système
• sys.server_audits
• sys.server_file_audits
• sys.server_audit_specifications
• sys.server_audit_specifications_details
• sys.database_audit_specifications
• sys.database_audit_specification_details
Audit base de données SQL dans Azure

• Configuré par Azure Portal ou Azure PowerShell

• Seuls les événements de base de données
peuvent être audités (succès et / ou de l'échec):
• SQL simple
• SQL paramétré
• Procédure stockée
• Connexion
• Gestion des transactions

• Les journaux d'audit sont écrits dans les tables de

magasin
Démonstration: Utilisation de l'audit SQL Server

Dans cette démonstration, vous verrez comment

configurer SQL Server Audit
Événements d'audit sur mesure (personnalisés)

• Vous permettent de créer des entrées d'audit

personnalisées:
• Ajouter USER_DEFINED_AUDIT_GROUP à une
spécification d'audit
• Appel sp_audit_write à partir du code Transact-SQL

CREATE TRIGGER HR.BonusChecker ON HR.EmployeeBonus

AFTER INSERT, UPDATE
AS

IF EXISTS (SELECT * FROM inserted WHERE bonus > 1000)

BEGIN
EXEC sp_audit_write @user_defined_event_id = 12,
@succeeded = 1,
@user_defined_information = N'An employee bonus is
over $1000';
END
Démonstration: Utilisation des événements de
vérification personnalisée

Dans cette démonstration, vous verrez comment

travailler avec les événements d'audit personnalisé
Leçon 3: Gestion de l’ Audit SQL Server

• Récupération de données Audit

• Travailler avec la Structure des enregistrements
d’audit
• des questions de vérification potentiel SQL Server
• Problèmes potentiels de l’audit des serveurs SQL
• Démonstration : Affichage de la sortie de l’audit
SQL Server
Récupération des données d’audit

• Cibles : journal des événements

L'Observateur d'événements pour afficher les
journaux d'événements Windows

• Cibles : fichiers binaires:

• Récupérer des audits basés sur des fichiers en utilisant
la function sys.fn_get_audit_file

SELECT *
FROM sys.fn_get_audit_file('X:\AuditFiles\*',default,default);
Travailler avec la structure des enregistrements
d'audit
• Travailler avec les résultats de
sys.fn_get_audit_file comme avec tout autre jeu
de résultats
• Les grands dossiers d'audit
• Pour se conformer aux règles de journal d'événements
Windows, les valeurs des champs de caractères avec
plus de 4000 caractères sont divisés en plusieurs
enregistrements d'audit
• numéro de séquence colonne indique la séquence
nécessaire pour joindre les enregistrements fractionnés
ensemble
Problèmes potentiels d’audit SQL Server
• Activer et désactiver l’audit
• Modifiez la propriété ÉTAT ON ou OFF pour activer
ou désactiver les spécifications des audits de serveur
et d'audit

• Considérations pour SQL Server Audit:

• Audit GUID dans la restauration des scénarios
• Audit GUID dans les scénarios de mise en miroir
• Impact sur les performances décriture d'audit
• Si la configuration de l'audit empêche l'instance de
commencer, utilisez le commutateur -F
• Si une base de données est restaurée à une instance
qui ne supporte pas les vérifications de base de
données, l'audit est ignoré
Démonstration: Affichage de la sortie de la
vérification SQL Server

Dans cette démonstration, vous verrez:

• Comment afficher la sortie d'un audit avec une
cible de fichier
• Comment afficher la sortie d'un audit avec une
cible de journal d'événements Windows
Leçon 4: Protection des données par chiffrement

• Chiffrement Transparent des données

• Déplacement des bases de données cryptées
• Gestion des clés extensible
• Encrypté pour toujours (Always Encrypted)
• Masquage dynamique des donnée
• chiffrement avec SQL Azure Database
Démonstration: Utilisation du Dynamic Data
Masking
Chiffrement transparent des données
Transparent Data Encryption
• Clés:
• Clé principale de service (Service master key)
• Clé principale Base de données (Database master key)
• Certificat de serveur (Server certificate.)
• Clé de chiffrement de base de données (Database
encryption key)

• Pour activer TDE:

1. Créer un DMK
2. Créer un certificat de serveur
3. Créer un DEK
4. Chiffrer la base de données
Déplacement des bases de données chiffrées

1. Détachez la base de données source

2. Copier / déplacer les fichiers de base de
données
3. Créer un nouveau SMK dans la base de
données master du serveur cible
4. Générer un nouveau certificat de serveur à
partir d'une sauvegarde du certificat de serveur
sur le serveur source et sa clé privée associée
5. Joindre la base de données (attach)
Gestion de clé extensible (Extensible Key
Management :EKM)
• EKM permet aux clés de cryptae de stocker les
clés de chiffrement en toute sécurité dans des
modules de sécurité matériels tiers (HSM), ou
auprès des fournisseurs externes EKM
• Azure Key Vault peut être utilisé en tant que
fournisseur EKM pour SQL Server

• Nécessite une configuration supplémentaire SQL

Server:
• l’ option fournisseur EKM activé doit être activée
• Les informations d'identification doivent être créés
pour permettre à SQL Server d’accéder aux clés dans
le fournisseur EKM
Toujours crypté (Always Encrypted)

• Cas d'utilisation typiques Alway Encrypted

• Protéger les données sensibles de l'accès par les DBA’s

• Types de chiffrement
• Déterministe
• Aléatoire

• clés Toujours crypté

• Colonne clé Master
• Colonne clé de chiffrement

• Pilote toujours crypté

• Transparent à l'application

• restrictions
Masquage dynamique de données

• Formats de Masque :
• Défaut
• Email
• Chaîne personnalisée
• Aléatoire
• Affichage des données masquées:
• l'autorisation SELECT permet de voir les données masques (XXXX)
• l'autorisation UNMASK permet de voir les données non
masques(inhiber le masque)
• Restrictions
• toujours Encrypted
• FILESTREAM
• column_set
• Calculé colonnes
Chiffrement avec Azure base de données SQL

• TDE
• Pris en charge

• EKM
• Non pris en charge, utilisez Azure Key Vault

• toujours Encrypted
• Pris en charge

• Masquage dynamique de données

• Pris en charge
Démonstration: Utilisation de Dynamic Data
Masking

Dans cette démonstration, vous verrez comment

travailler avec Dynamic Data Masking
Atelier: Utilisation d'audit et de chiffrement

• Exercice 1: Travailler avec SQL Server Audit

Exercice 2: Chiffrer une colonne avec Toujours
Encrypted
• Exercice 3 : Chiffrer une base de données utilisant
TDE

Informations de connexion
Machine virtuelle: 20764C-MIA-SQL
Nom d'utilisateur: Adventureworks \ étudiants
Mot de passe: Pa55w.rd

Temps estimé: 90 minutes

Scénario Lab

Adventure Works Cycles est un fabricant mondial,

grossiste et détaillant de produits de cycle. Suite à
un audit de sécurité interne, la société a pour
objectif de mettre l'audit en place pour suivre
l'accès à la base de données, chiffrer une base de
données au repos, et de crypter des données
sensibles avec toujours Encrypted. Vous êtes un
administrateur de base de données pour
Adventure Works, chargés d'appliquer ces
changements.
Revue de laboratoire

• Quel type de cryptage toujours Encrypted chiffrera

toujours la même valeur de texte brut au même
texte Cypher (en supposant est utilisé la même clé
de cryptage)?
Révision du module et plats à emporter

• Critique Question (s) les meilleures pratiques