Sécuriser les accès aux DB SQL

Introduction
Cela fait déjà quelques années que notre environnement de production (Cluster Sql) utilise uniquement le compte "sa" pour accéder aux bases de données.
En gros: la maintenance, le support et les applications utilisent tous le même compte pour se connecter au serveur Sql... a savoir "SA".

Toute personnes équipée d'un minimum de clairvoyance hurlerait au loup... et avec raison d'ailleurs.
Dans un environnement informatique, l'utilisation abusive de compte administrateurs présentent de nombreux dangers... le premier étant bien entendu de permettre à l'utilisateur de tous casser et de s'apercevoir trop tard de ses bêtises.
D'un autre côté, les clients (applications accédant à leur propre base de donnée) devraient tous avoir leur propre login Sql. Avec l'utilisation du compte SA, n'importe quel application cliente est capable de se connecter sur n'importe quelle base de donnée. Tant qu'il n'y a pas d'accident... personne ne se plaint. Cependant l'éventuel accès accidentel aux informations d'un tiers devrait être pris en compté géré avec la plus grande prudence "administrative".

Cette situation bien établie devrait bien entendu changer pour la sécurité de tous (client, comme intervenant).
Malheureusement, comme pour les feux rouges devant les écoles, cette situation n'évoluera que lorsqu'il y aura un accident grave :-).

Qu'a cela me tienne, cela ne m'empêche pas de fureter pour compléter ma boîte à outil.

Changement en douceur
La première étape serait de protéger les intervenants.

• Les personnes fournissant du support technique (d'investigation) ne devraient pas être capable de modifier l'information. Cela éviterait les possibles incidents en production.
  Pour ces intervenants, des comptes en lecture seule seront créés.
  Exemple:  Utiliser un compte rDomeu avec un nomenclature particulière. Le R indique la propriété particulière du compte... a savoir "Read (only)" et "Domeu" identifie l'utilisateur.
  
• Les personnes appliquant des actions administratives sur les DB (ou le serveur) devraient clairement s'identifier. A cette fin, des comptes d'administration distincts seront créés.
  L'utilisation de compte administratif permettra, en autre, de pouvoir effectuer un Audit des différentes opérations de maintenances (et par qui).
  Exemple: en utilisant le compte aFrc avec la nomenclature décrite ci-avant. Le A indique un compte d'Administration et "Frc" l'utilisateur.

La deuxième étape serait d'utiliser des comptes séparer pour les applications clientes (voir les clients eux mêmes).
Ainsi, il serait possible de s'assurer d'un client ne puisse pas accéder, même par accident, aux informations d'un autre.
Dans ce dernier cas, et toujours en utilisant la nomenclature décrite précedemment, la création de compte appCompany ou appCustomer permet d'identifier facilement le type de compte et le client (ou société attachée).

Nomenclature
Pour faciliter l'identification des comptes, il est possible d'utiliser une nomenclature rudimentaire, décrite ci-avant, mais que je vais reprendre ici.
Nomenclature: PropriétéDuCompte + CompanyCode + UtilisateurID
Excepté le premier élément, tous les autres utiliserons la convention Camel Case (permière lettre en majuscule).

Propriété du compte: Quelques lettre indetifiant les propriété principale du compte.

• sa: Compte Administratif de plein pouvoir (gestion du serveur et/ou gestion des DB).
• a: Compte Administratif destiné à permettre la modification de la définition d'une base de données. Ce type de compte peut être une alternative intermédiaire avant de donner le plein pouvoir.
  
• r: Read Only
• app: Compte pour Application

CompanyCode:
Optionel. Peut s'avérer pertinant si plusieurs sociétés (sous-traitant, client, etc) disposent d'accès sur le serveur Sql. Le CompanyCode peut avantageusement être remplacé par un code de département.
UtilisateurID: Permet d'identifier un utilisateur de façon univoque.

Exemples:

• saCsDomeu - Compte administratif système pour l'utilisateur  Domeu (code) travaillant dans la société CS (code d'identification).
• rAwFrc - Compte en lecture seule pour l'utilisateur Frc travaillant dans la société Aw (code d'identification de ma société).
• appMedicalc - Compte applicatif pour l'application Medicalc (le code société étant omis)

Création de compte read-only
Voici un script SQL permettant de créer des Login Sql et de les attacher en tant qu'utilisitateur des différentes bases de données.
L'atuce importante du script... est que les privilèges du groupe public (privilège par défaut assigné a l'utilisateur lorsqu'il est attaché à une DB) sont intégralement remplacés par le privilège db_datareader lorsque la stored procedure sp_addRoleMember est exécutée.   

USE [master]
GO
CREATE LOGIN [rCsDomeu] WITH PASSWORD=N'myPassword', DEFAULT_DATABASE=[Cust003April], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
USE [AdventureWorks]
GO
CREATE USER [rCsDomeu] FOR LOGIN [rCsDomeu]
GO
USE [AdventureWorks]
GO
-- By adding the user to specific role, he automatically removed
-- from the Public role.
EXEC sp_addrolemember N'db_datareader', N'rCsDomeu'
GO
USE [Cust003April]
GO
CREATE USER [rCsDomeu] FOR LOGIN [rCsDomeu]
GO
EXEC sp_addrolemember N'db_datareader', N'rCsDomeu'
GO

Création de compte administratif
Voici un script SQL permettant de créer un login administratif (Administration Système).
Les atuces importante du script:

• ajouter l'utilisateur dans le serverrole sysadmin lui confère les pleins pouvoirs (sur le serveur, alteration des DB, manipulation des données). Aucuns autre rôle n'est nécessaire.
• Avec le serverrole dbcreator l'utilisateur peut uniquement créer des bases de données et en modifier le schéma (puisqu'il en est le createur).
  Par contre, il ne pourra pas modifier le schéma des DB dont il n'est pas owner (cela inclus les opérations Insert et Select). Aucuns autre rôle n'est nécessaire dans ce cas particulier.
  

 

USE [master]
GO
CREATE LOGIN [saCsDomeu] WITH PASSWORD=N'myPassword', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
EXEC master..sp_addsrvrolemember @loginame = N'saDomeu', @rolename = N'sysadmin'
GO

Création de compte administratif limité
Voici un script SQL permettant de créer un login administratif d'ordre limité.
Le but étant ici d'autoriser une utilisateur particulier à manipuler le schéma d'une base de donnée précise.
Les atuces du script:

• L'utilisation du role db_owner donne à l'utilisateur le plein pouvoir sur la base de donnée (altération du schéma mais également select + insert).
  Cependant, ce n'est pas la solution que je recommenderais.
  
• Avec le serverrole dbcreator l'utilisateur peut uniquement créer des bases de données et en modifier le schéma (puisqu'il en est le createur).
  Par contre, il ne pourra pas modifier le schéma des DB dont il n'est pas owner (cela inclus les opérations Insert et Select). Aucuns autre rôle n'est nécessaire dans ce cas particulier.
  

 

USE [master]
GO
CREATE LOGIN [aCsDomeu] WITH PASSWORD=N'myPassword', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
USE Cust003April
GO
CREATE USER [aCsDomeu] FOR LOGIN [aCsDomeu]
GO
EXEC sp_addrolemember N'db_owner', N'aCsDomeu'
GO

En alternative au role db_owner, il existe le role db_ddlAdmin:

• Il est possible de déclarer un utilisateur ayant le role db_ddladmin sur une DB.
  Il sera ainsi possible a cet utilisateur d'altérer le schéma (uniquement) d'une base de donnée.
  Par contre, il ne lui sera pas possible de lire ou modifier des informations dans les tables (même celles que l'utilisateur crée).
• Il est possible d'agrémenter les droits d'un tel utilisateur avec les roles db_datareader et db_datawriter en fonction des besoins.
  

USE [master]
GO
CREATE LOGIN [aCsDomeu] WITH PASSWORD=N'myPassword', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
USE Cust003April
GO
CREATE USER [aCsDomeu] FOR LOGIN [aCsDomeu]
GO
EXEC sp_addrolemember N'db_ddladmin', N'aCsDomeu'
-- Add the following rights on need
-- EXEC sp_addrolemember N'db_datareader', N'aCsDomeu'
-- EXEC sp_addrolemember N'db_datawriter', N'aCsDomeu' 
GO

Serveur Roles
Voila, en fin de ce lonf article, je vais quand même ajouter quelques informations concernant les roles serveurs qui peuvent être ajouter à l'aide de la stored procedure master..sp_addsrvrolemember :

• SysAdmin: Donne les pleins pouvoirs sur le serveur SQL. 
• ServerAdmin: Changer la config serveur Sql + shutdown.
• SetupAdmin: Ajouter/retirer des link serveur; gérer la réplication, gérer les stored proc étendues (xp).
• SecurityAdmin: Gestion des logins, lecture des logs d'erreur, Audit.
• ProcessAdmin: Gestion des processus Sql.
• DBCreator: Creation, modification et resize des DB (attention, il y a des limitations; voir la section "création de compte administratif").
• DiskAdmin: Gestion des fichiers.