Authentification, connexion, utilisateur et privilèges

SQLPro

Le mécanisme d'authentification est le fait par lequel un compte de connexion tente d'accéder au serveur SQL. Dès qu'elle est authentifiée, la connexion se transforme en session et permet de naviguer dans une base de données du serveur par l'intermédiaire d'un utilisateur SQL. Chacun (compte de connexion et utilisateur) étant doté de privilèges… Définissons un peu tous ces termes…

Une connexion permet de se connecter à un serveur SQL Server. Elle est enregistrée dans la base master. Un utilisateur SQL permet de naviguer dans une base de données. Il est enregistré dans la base pour laquelle il accède aux objets. Une confusion courante consiste à mélanger les notions d'utilisateur (personnes physiques du SI, compte OS), ceux de SQL et les utilisateurs de bases de données qui sont des objets logiques dotés d'un profil de navigation au sein d'une base de données…

Commentez

- Titre : Authentification, connexion, utilisateur et privilèges

- Auteur : SQLPro

- Parution : 26 juin 2009

- Mise à jour : 26 juin 2009

- Licence : Copyright ® 2009 Frédéric Brouard. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.

Sommaire

- I. LES COMPTES DE CONNEXION

- II. MÉTADONNÉES DES COMPTES DE CONNEXION

- III. LES UTILISATEURS SQL

- IV. MÉTADONNÉES DES UTILISATEURS SQL

- V. QUE FAUT-IL PRIVILÉGIER : CONNEXION WINDOWS ou SQL ?

- VI. LES PRIVILÈGES

- VI-A. PRIVILÈGES AU NIVEAU SERVEUR

- VI-B. Liste des privilèges au niveau serveur :

- VI-C. PRIVILÈGES AU NIVEAU BASE DE DONNÉES

- VI-D. Liste des privilèges au niveau base

- VI-E. Quelques privilèges importants…

- VI-F. Suppression de privilèges

- VI-G. Combiner des privilèges et des objets

- VI-H. Emprunter l'identité d'un autre utilisateur (ou d'un compte de connexion)

- VI-I. Possibilité de rétrocession avec WITH GRANT OPTION

- VI-J. Privilégier les SCHEMA SQL

- VI-K. Cas des privilèges multiples

- VII. Recoler les morceaux…

- VIII. CONCLUSION

- IX. Compléments

- IX-A. Vues de gestion de la sécurité

- IX-B. -- vues niveau serveur

- IX-C. -- vues niveau base

- IX-D. Liste des principales commandes de gestion de la sécurité

- IX-E. Liste des principales procédures de gestion de la sécurité

- IX-F. Fonctions

I - LES COMPTES DE CONNEXION

Au niveau OS vous avez des utilisateurs Windows qui sont en fait des comptes de connexion SYSTÈME pour qu'une personne physique (ou un service) puisse accéder à des ressources physiques (fichiers, exécutables…).

Au niveau SGBDR SQL Server, vous avec des comptes de connexion au serveur qui peuvent être de deux sortes :

1) un mappage de compte Windows

C'est-à-dire un compte Windows enregistré dans le serveur SQL et donc autorisé à y pénétrer.

Syntaxe minimale de création d'un mapping d'utilisateur Windows à un compte de connexion SQL :

CREATE LOGIN <nom_utilisateur_Windows> FROM WINDOWS;

2) un login purement SQL

Ce compte de connexion permet de se connecter au serveur.

Syntaxe minimale

Un compte de connexion est enregistré dans SQL Server par un identifiant appelé SID (server_user_id) qui est une clef interne de type INT. Tant et si bien qu'entre deux serveurs SQL le même nom de connexion n'a pas forcément le même SID !

À ce niveau les comptes de connexion peuvent être dotés de privilèges de niveau serveur (par exemple la création d'une base de données est une opération typiquement de niveau serveur…). En revanche, ils ne permettent pas de mettre en place un quelconque privilège sur une base.

Il est bien sûr possible de modifier ou supprimer un compte de connexion via ALTER ou DROP (LOGIN).

ATTENTION : vous ne pourrez créer des comptes de connexion purement SQL que si vous permettez au service SQL Server d'utiliser l'authentification mixte (Windows + SQL). Sans cela, les seules connexions possibles le sont uniquement par l'intermédiaire de comptes Windows. La modification nécessite de redémarrer le service SQL Server.

II - MÉTADONNÉES DES COMPTES DE CONNEXION

N'oubliez pas que les comptes de connexion sont enregistrés dans la base master. Pour lister les comptes de connexion, vous pouvez requêter les vues suivantes :

- sys.server_principals (ensemble des entités de sécurité au niveau serveur) ;

- sys.sql_logins (connexion purement SQL) ;

- sys.login_token (entité de sécurité niveau serveur associée au jeton de connexion).

Vous pouvez aussi utiliser les fonctions suivantes :

- SYSTEM_USER : renvoie le nom de la connexion courante ;

- SUSER_NAME() : identifie l'utilisateur pour la connexion ;

- SUSER_SNAME() : retourne le nom de connexion associé à un numéro d'identification de sécurité.

Pour ces deux dernières, il est possible de fournir un SID en argument.

Exemple :

Obtenir les comptes de connexion purement Windows :

SELECT * &#160;
FROM   sys.server_principals&#160;
WHERE  type_desc = 'WINDOWS_LOGIN'

ATTENTION

À un compte de connexion SQL, il est important d'associer au moins un utilisateur SQL dans au moins une base de données. Chaque utilisateur SQL d'une base étant différent des autres, sauf cas particulier (public, dbo…).

III - LES UTILISATEURS SQL

Il existe, dans chaque base de données, des utilisateurs SQL qui sont en fait des profils de navigation dotés de privilèges (autorisation d'exécution de commandes sur des objets). Ces utilisateurs SQL peuvent être liés à un compte de connexion.

Attention : un utilisateur est propre à la base qui le contient. Tant et si bien que pour naviguer d'une base à l'autre il faut créer autant d'utilisateurs que de bases et mapper tous ces utilisateurs au même compte de connexion. De la même façon que pour les comptes de connexion, ce n'est pas parce qu'un utilisateur SQL a le même nom dans deux bases différentes que c'est le même !

Pour créer un utilisateur SQL dans une base il faut :

1) se placer dans le contexte de la base dans laquelle l'utilisateur SQL doit être créé à l'aide de la commande USE ;

2) créer l'utilisateur SQL à l'aide de la commande CREATE USER

exemple :

USE <nom_base>;&#160;
CREATE USER <nom_utilisateur> FOR LOGIN <nom_connexion>;

Il est bien sûr possible de modifier ou supprimer un utilisateur SQL d'une base via les commandes ALTER ou DROP (USER), mais en se plaçant préalablement dans le contexte de la base de données cible.

Chaque utilisateur SQL doit avoir un nom unique, mais est identifié par un numéro (UID).

IV - MÉTADONNÉES DES UTILISATEURS SQL

N'oubliez pas que les utilisateurs sont enregistrés dans la base pour laquelle ils doivent accéder aux objets.

Pour lister les utilisateurs SQL, vous pouvez requêter les vues suivantes, dans chaque base de données :

sys.database_principals (ensemble des entités de sécurité au niveau base de données) ;

sys.user_token (entité de sécurité de base de données associée au jeton de l'utilisateur).

Vous pouvez aussi utiliser les fonctions suivantes :

- USER : utilisateur SQL actuel ;

- CURRENT_USER : synonyme de USER ;

- USER_NAME() : synonyme de USER, mais on peut préciser un user ID.

V - QUE FAUT-IL PRIVILÉGIER : CONNEXION WINDOWS ou SQL ?

Malgré tout le marketing Microsoftien, je suis définitivement contre l'utilisation des comptes Windows pour naviguer dans les bases de données de production (sauf administration), et cela pour de nombreuses raisons :

1) ce ne sont pas les utilisateurs (personnes physiques) qui accèdent au serveur de bases de données, mais des applications…

2) la gestion par compte NT nécessite de créer autant de comptes de connexion que d'utilisateurs dans le serveur SQL (travail fastidieux et encombrement si l'organisation est importante) ;

3) il est impensable de gérer directement les privilèges dans chacune des bases si vous avez de nombreux utilisateurs Windows. Vous devez donc passer par des rôles SQL ce qui alourdit encore plus le processus ;

4) le fait de recourir à des comptes de connexion purement SQL n'empêche pas de savoir de quels machine ou compte NT provient la connexion (SELECT client_net_address FROM sys.dm_exec_connections; SELECT host_name, nt_username, nt_domain FROM sys.dm_exec_sessions) ;

5) le passage par des comptes NT empêche de naviguer « anonymement » dans une base (emprunt de l'utilisateur GUEST). Ce n'est peut-être pas la meilleure des choses, mais c'est parfois nécessaire !

6) en cas de panne de votre système d'authentification Windows (AD par exemple), vous pourriez ne plus avoir accès aux serveurs SQL…

Par analogie, imaginez un sinistre majeur, comme un incendie ou les pompiers seraient empêchés de venir secourir les victimes par le fait de trop zélés policiers qui exigeraient qu'ils prouvent leur appartenance aux hommes du feu… Impensable ! En matière de sinistre, on doit agir vite. Les pompiers ont l'absolue priorité. De même que les données doivent, même dans un mode très dégradé de sécurité, être accessibles le plus rapidement possible.

Prenez l'exemple de l'hôpital ou la confidentialité est la règle absolue… Jusqu'au moment où il vous faudra choisir entre l'authentification et la délivrance de la donnée, surtout si le patient est en train de se vider de son sang aux urgences et que l'on doit savoir rapidement son groupe sanguin !

Peut être le comprendrez-vous à vos dépens le jour ou ceci arrivera et que votre patron voudra d'urgence savoir s'il peut passer la commande du siècle pour son entreprise et en sera empêché par votre conception singulière de la sécurité !

NOTA : pour s'y retrouver…

Rien ne vaut une bonne norme de nommage. C'est pourquoi nous avons décidé d'utiliser la norme suivante :

C_… définit une connexion SQL. Exemple : C_COMPTA

U_… définit un utilisateur SQL. Exemple : U_LECTEUR

R_… définit un rôle de base de données. Exemple : R_ECRIVAIN

VI - LES PRIVILÈGES

Il ne sert à rien de créer des comptes de connexion ou des utilisateurs SQL si l'on ne prévoit pas de gérer finement les autorisations auxquelles chacune des entités de sécurité doit se limiter. C'est pourquoi il faut maintenant voir comment on affecte des privilèges aux connexions et utilisateurs.

Les privilèges sont les autorisations d'exécution de commande qu'une entité de sécurité (connexion ou utilisateur SQL) reçoit sur un objet. Par exemple l'autorisation de créer une base pour une connexion (niveau serveur) comme l'autorisation de lire une table par SELECT sur un schéma SQL (niveau base).

Comme nous venons de le voir il est possible de délivrer des privilèges au niveau serveur (donc sur un compte de connexion) ou bien au niveau base (donc à un utilisateur SQL).

VI-A - PRIVILÈGES AU NIVEAU SERVEUR

SQL Server permet de spécifier différents privilèges au niveau serveur, via des rôles ou directement.

SQL Server offre des rôles prédéfinis au niveau serveur dont voici la liste :

- sysadmin : droit de vie ou de mort sur le serveur (privilège le plus haut au niveau serveur) ;

- securityadmin : autorisé à gérer les connexions et leurs propriétés (assignation d'autorisations GRANT, DENY et REVOKE de niveau du serveur, mais aussi au niveau de chaque base de données. Ils peuvent aussi réinitialiser les mots de passe pour des connexions SQL) ;

- serveradmin : autorisé à modifier les options de configuration du serveur (via sp_configure) et arrêter le serveur ;

- setupadmin : autorisé à ajouter et supprimer des serveurs liés et exécuter certaines procédures stockées du système ;

- processadmin : autorisé à lire et arrêter les processus en cours d'exécution dans le moteur de base de données ;

- diskadmin : autorisé à gérer les fichiers des bases de données sur les disques ;

- dbcreator : autorisé à modifier, supprimer et restaurer n'importe quelle base de données. la restauration étant une forme de création de base de données ;

- bulkadmin : autorisé à exécuter l'instruction BULK INSERT.

Pour affecter un rôle prédéfini, utiliser la procédure stockée sp_addsrvrolemember.

Exemple

Aucun contexte de base n'est nécessaire.

Notez que par défaut, tous les membres du groupe Windows BUILTIN\Administrators (groupe générique de tous les administrateurs locaux et domaine Windows) sont membres du rôle serveur fixe sysadmin.

Il n'est pas possible de créer vos propres rôles au niveau serveur. Il est en revanche possible d'affecter des privilèges au niveau du compte de connexion, ce qui se fait par la commande GRANT dans le contexte de la base master.

Exemple :

USE master;&#160;
GRANT SHUTDOWN ON C_COMPTA;

VI-B - Liste des privilèges au niveau serveur :

Générique serveur : (GRANT <privilège> TO <connexion> )

ADMINISTER BULK OPERATIONS

ALTER ANY CONNECTION

ALTER ANY CREDENTIAL

ALTER ANY DATABASE

ALTER ANY ENDPOINT

ALTER ANY EVENT NOTIFICATION

ALTER ANY LINKED SERVER

ALTER ANY LOGIN

ALTER RESOURCES

ALTER SERVER STATE

ALTER SETTINGS

ALTER TRACE

AUTHENTICATE SERVER

CONNECT SQL

CONTROL SERVER

CREATE ANY DATABASE

CREATE DDL EVENT NOTIFICATION

CREATE ENDPOINT

CREATE TRACE EVENT NOTIFICATION

EXTERNAL ACCESS ASSEMBLY

SHUTDOWN

UNSAFE ASSEMBLY

VIEW ANY DATABASE

VIEW ANY DEFINITION

VIEW SERVER STATE

Associé à une connexion (GRANT <privilège> ON LOGIN::<connexion> TO <connexion> )

CONTROL

IMPERSONATE

VIEW DEFINITION

ALTER

Sur objet système (GRANT <privilège> ON [sys.]<objet_systeme> TO <connexion> )

SELECT

EXECUTE

VI-C - PRIVILÈGES AU NIVEAU BASE DE DONNÉES

SQL Server dispose de rôles prédéfinis au niveau de la base de données. Ils doivent être affectés par l'exécution de la procédure stockée sp_addrolemember.

Ces rôles sont les suivants :

- db_owner : droit de vie ou de mort sur la base de données, y compris suppression. (plus haut privilège au niveau bases de données) ;

- db_securityadmin : autorise de gérer la sécurité (appartenance au rôle, gestion des privilèges) ;

- db_accessadmin : autorise d'ajouter ou supprimer l'accès à la base de données des connexions Windows, des groupes Windows et des connexions SQL Server ;

- db_backupoperator: autorise d'effectuer des sauvegardes de la base de données ;

- db_ddladmin : autorise d'exécuter n'importe quelle commande DDL (Data Definition Language - CREATE, ALTER, DROP…) dans une base de données ;

- db_datawriter : autorise d'ajouter, supprimer et modifier des données dans toutes les tables ou vues utilisateur. Attention : ils ne peuvent pas lire (SELECT) dans ces mêmes tables ou vues ;

- db_datareader : autorise de lire toutes les données de toutes les tables utilisateur ;

- db_denydatawriter : interdit d'ajouter, modifier ou supprimer les données des tables ou vues utilisateur d'une base de données. Mais autorise à les lire ;

- db_denydatareader : interdit de lire les données des tables utilisateur d'une base de données. Mais autorise à les écrire.

Exemple :

USE mabase;&#160;
EXECUTE sp_addrolemember 'db_datareader', 'U_LECTEUR';

Vous pouvez créer votre propre rôle de base de données à l'aide de la commande CREATE ROLE. Exemple :

CREATE ROLE R_AJOUTEUR;

Vous pouvez octroyer des privilèges à un utilisateur SQL ou à un rôle de base de données à l'aide de la commande GRANT.

Exemple :

USE MaBase;
GRANT SELECT ON T_CLIENT TO U_LECTEUR; --> octroie l'usage de la commande SELECT sur l'objet T_CLIENT à l'utilisateur U_LECTEUR
GRANT SELECT ON T_FACTURE TO R_AJOUTEUR; --> octroie l'usage de la commande SELECT sur l'objet facture au rôle R_AJOUTEUR

VI-D - Liste des privilèges au niveau base

Générique database : (GRANT <privilège> TO <autorisé> )

L'autorisé pouvant être un utilisateur SQL, un rôle ou bien d'autres choses encore (certificat par exemple).

ALTER

ALTER ANY ASSEMBLY

ALTER ANY ASYMMETRIC KEY

ALTER ANY CERTIFICATE

ALTER ANY CONTRACT

ALTER ANY DATABASE DDL TRIGGER

ALTER ANY DATABASE EVENT NOTIFICATION

ALTER ANY DATASPACE

ALTER ANY FULLTEXT CATALOG

ALTER ANY MESSAGE TYPE

ALTER ANY REMOTE SERVICE BINDING

ALTER ANY ROLE

ALTER ANY ROUTE

ALTER ANY SCHEMA

ALTER ANY SERVICE

ALTER ANY SYMMETRIC KEY

ALTER ANY USER

AUTHENTICATE

BACKUP DATABASE

BACKUP LOG

CHECKPOINT

CONNECT

CONNECT REPLICATION

CONTROL

CREATE AGGREGATE

CREATE ASSEMBLY

CREATE ASYMMETRIC KEY

CREATE CERTIFICATE

CREATE CONTRACT

CREATE DATABASE

CREATE DATABASE DDL EVENT NOTIFICATION

CREATE DEFAULT

CREATE FULLTEXT CATALOG

CREATE FUNCTION

CREATE MESSAGE TYPE

CREATE PROCEDURE

CREATE QUEUE

CREATE REMOTE SERVICE BINDING

CREATE ROLE

CREATE ROUTE

CREATE RULE

CREATE SCHEMA

CREATE SERVICE

CREATE SYMMETRIC KEY

CREATE SYNONYM

CREATE TABLE

CREATE TYPE

CREATE VIEW

CREATE XML SCHEMA COLLECTION

DELETE

EXECUTE

INSERT

REFERENCES

SELECT

SHOWPLAN

SUBSCRIBE QUERY NOTIFICATIONS

TAKE OWNERSHIP

UPDATE

VIEW DATABASE STATE

VIEW DEFINITION

Tables et vues : ( GRANT <privilège> ON [ <schéma> ].<objet> [ ( column1 [ , column2 [, columns3 …] ] ) ] TO <autorisé> )

ALTER

CONTROL

DELETE

EXECUTE

INSERT

RECEIVE

REFERENCES

SELECT

TAKE OWNERSHIP

UPDATE

VIEW CHANGE TRACKING

VIEW DEFINITION

Au niveau du schéma : ( GRANT <privilège> ON SCHEMA::<schéma> TO <autorisé> )

ALTER

CONTROL

DELETE

EXECUTE

INSERT

REFERENCES

SELECT

TAKE OWNERSHIP

UPDATE

VIEW DEFINITION

VI-E - Quelques privilèges importants…

Tout le monde à bien compris que les privilèges SELECT, INSERT, UPDATE et DELETE correspondent aux mêmes commandes sur les tables ou les vues. En revanche le privilège MERGE n'existe pas, puisqu'une MERGE n'est somme toute qu'une combinaison de INSERT et UPDATE avec parfois du SELECT… De la même façon, on comprend bien l'usage du privilège EXECUTE sur les procédures stockées. En revanche le privilège REFERENCES est plus subtil. Un petit exemple va mieux nous faire comprendre son intérêt…

Imaginons une base de données avec les tables suivantes : T_CLIENT, T_FACTURE (faisant référence au client par le biais d'une contrainte d'intégrité référentielle de type FOREIGN KEY). Imaginons un utilisateur de nom U_SAISIE qui doit saisir des factures, mais n'a pas de privilèges de lecture sur la table des clients. Donnons à cet utilisateur le privilège INSERT sur la table T_FACTURE. Pourra-t-il saisir une commande ? Absolument pas ! En effet, car la vérification de la contrainte suppose la lecture des clefs de la table client par le biais de l'intégrité référentielle, et ce serait là un moyen de violer la sécurité par tâtonnement, notamment si la clef de la table des clients a été composée du nom, du prénom et d'une date de naissance. Il reste alors deux possibilités pour gérer ce cas : donner le privilège de lire les données de la table client, ou plus simplement d'en lire la clef pour la référence par le biais du privilège REFERENCES.

VI-F - Suppression de privilèges

C'est la commande REVOKE qui permet la révocation des privilèges. Cependant, SQL Server a rajouter une commande qui n'existe pas en standard (norme SQL) et qui permet l'interdiction de l'octroi de privilège. C'est la commande DENY. On dit qu'elle surpasse l'attribution, tandis que la commande REVOKE défait le privilège attribué.

La différence peut paraître subtile. Prenons un exemple…

La séquence suivante :

GRANT SELECT ON T_CLENT TO U_1;&#160;
REVOKE SELECT ON T_CLENT TO U_1;&#160;
GRANT SELECT ON T_CLENT TO U_1;

se termine par le fait que U_1 peut lire la table T_CLIENT.

La séquence suivante :

GRANT SELECT ON T_CLENT TO U_1;&#160;
DENY SELECT ON T_CLENT TO U_1;&#160;
GRANT SELECT ON T_CLENT TO U_1;

se termine par le fait que U_1 ne peut pas lire la table T_CLIENT.

Je ne saurais trop vous déconseiller fortement d'utiliser la commande DENY, car elle ne permet pas de comprendre clairement l'enchaînement des privilèges. Elle conduit donc trop souvent à des situations où comprendre d'où vient l'erreur n'est pas facile ! Notamment lorsque l'on a rétrocédé des privilèges… Voir le paragraphe parlant des privilèges multiples.

VI-G - Combiner des privilèges et des objets

La plupart des commandes GRANT permettent de combiner plusieurs privilèges et plusieurs « autorisés ». En revanche il n'est généralement pas possible de préciser plusieurs objets…

Exemples :

GRANT SELECT                 ON T_CLIENT  TO U_LECTEUR, R_AJOUTEUR;&#160;
GRANT INSERT, UPDATE, DELETE ON T_FACTURE TO R_AJOUTEUR;

VI-H - Emprunter l'identité d'un autre utilisateur (ou d'un compte de connexion)

Il est possible d'emprunter l'identité d'un utilisateur (ou d'un compte de connexion) en utilisant la commande EXECUTE AS.

Exemple

Il est possible de revenir à l'état antérieur avec la commande REVERT.

Bien entendu pendant le temps de cet emprunt, vos privilèges sont l'intersection des vôtres et de ceux de l'utilisateur emprunté et non une union…

VI-I - Possibilité de rétrocession avec WITH GRANT OPTION

En fait il faut comprendre qu'un privilège est attribué par un utilisateur (et plus généralement un autorisé) pour un autre autorisé. Il y a donc un chaînage des utilisateurs et leurs privilèges. La coupure de cette chaîne à un moment donné, affecte tous les privilèges descendants.

Mais jusqu'ici vous avez vu comment l'on octroie des privilèges à des autorisés, mais pas comment on permet a celui qui vient de se faire gratifier d'un nouveau privilège de le céder à son tour. C'est l'option WITH GRANT OPTION qui permet cela. Voici un exemple :

-- création de 3 utilisateurs associés aux connexions :&#160;
CREATE USER U_1 FOR LOGIN C_1;&#160;
CREATE USER U_2 FOR LOGIN C_2;&#160;
CREATE USER U_3 FOR LOGIN C_3;&#160;
&#160;
-- octroi des privilèges de lecture et mise à jour de la table des clients:&#160;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.T_CLIENT TO U_1 WITH GRANT OPTION;&#160;
&#160;
-- placement dans le contexte de U_1 :&#160;
EXECUTE AS USER = 'U_1';&#160;
&#160;
-- lancement d'un octroi à U_2 sous le compte de l'utilisateur U_1 :&#160;
GRANT SELECT ON dbo.T_CLIENT TO U_2 WITH GRANT OPTION;&#160;
&#160;
-- termine l'emprunt d'identité U_1 :&#160;
REVERT;&#160;
&#160;
-- placement dans le contexte de U_2 :&#160;
EXECUTE AS USER = 'U_2';&#160;
&#160;
-- lancement d'un octroi à U_3 sous le compte de l'utilisateur U_2 :&#160;
GRANT SELECT ON dbo.T_CLIENT TO U_3 WITH GRANT OPTION;&#160;
&#160;
-- lancement d'un second octroi à U_3 sous le compte de l'utilisateur U_2 :&#160;
GRANT UPDATE ON dbo.T_CLIENT TO U_3 WITH GRANT OPTION;&#160;
--> Impossible de trouver le schéma 'dbo', car il n'existe pas ou vous ne possédez pas d'autorisation.&#160;
&#160;
SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES&#160;
&#160;
GRANTOR    GRANTEE   TABLE_CATALOG   TABLE_SCHEMA     TABLE_NAME   PRIVILEGE_TYPE IS_GRANTABLE&#160;
---------- --------- --------------- ---------------- ------------ -------------- ------------&#160;
U_1        U_2       DB_TESTS        dbo              T_CLIENT     SELECT         YES&#160;
U_2        U_3       DB_TESTS        dbo              T_CLIENT     SELECT         YES

VI-J - Privilégier les SCHEMA SQL

Il est très intéressant d'octroyer les privilèges au niveau du schéma SQL. En effet, dans ce cas le privilège vise tous les objets du schéma qu'ils soient actuels ou futurs… Cela permet de synthétiser les commandes et d'organiser sa base par grandes fonctions logiques.

Exemple

Soit une base de données possédant les schémas SQL suivants :

S_REF --> toutes les tables de référence (code postaux, liste de département…) ;

S_COMPTA --> la comptabilité ;

S_VENTE --> les ventes ;

S_RH --> les ressources humaines.

Et les utilisateurs suivants :

U_RH, U_VENTE et U_COMPTA devant respectivement faire de la saisie/lecture dans les tables relatives à leur fonction. Les commandes à passer seront les suivantes :

GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON SCHEMA::S_COMPTA TO U_COMPTA;&#160;
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON SCHEMA::S_VENTE TO U_VENTE;&#160;
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON SCHEMA::S_RH TO U_RH;&#160;
GRANT SELECT ON SCHEMA::S_REF TO U_COMPTA, U_VENTE, U_RH;

Et le système continuera à fonctionner même si l'on y rajoute des tables, vues ou procédures stockées.

VI-K - Cas des privilèges multiples

Comme nous l'avons vu avec l'option WITH GRANT OPTION, l'attribution de privilèges vient toujours d'un autre autorisé… Que se passe-t-il si l'on attribue plusieurs fois le même privilège et qu'on ne le retire qu'une seule fois ? Comme un bon exemple vaut mieux qu'un long discours, lançons-nous…

-- création de 3 comptes de connexion :&#160;
CREATE LOGIN C_4 WITH PASSWORD = 'C4';&#160;
CREATE LOGIN C_5 WITH PASSWORD = 'C5';&#160;
CREATE LOGIN C_6 WITH PASSWORD = 'C6';&#160;
&#160;
-- création de 3 utilisateurs associés aux connexions :&#160;
CREATE USER U_4 FOR LOGIN C_4;&#160;
CREATE USER U_5 FOR LOGIN C_5;&#160;
CREATE USER U_6 FOR LOGIN C_6;&#160;
&#160;
-- octroi des privilèges de lecture et mise à jour de la table des clients :&#160;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.T_CLIENT TO U_4, U_5 WITH GRANT OPTION;&#160;
&#160;
-- placement dans le contexte de U_4 :&#160;
EXECUTE AS USER = 'U_4';&#160;
&#160;
-- lancement d'un octroi à U_6 sous le compte de l'utilisateur U_4 :&#160;
GRANT SELECT ON dbo.T_CLIENT TO U_6;&#160;
&#160;
-- termine l'emprunt d'identité U_4 :&#160;
REVERT;&#160;
&#160;
-- placement dans le contexte de U_5 :&#160;
EXECUTE AS USER = 'U_5';&#160;
&#160;
-- lancement d'un octroi à U_6 sous le compte de l'utilisateur U_5 :&#160;
GRANT SELECT ON dbo.T_CLIENT TO U_6;&#160;
&#160;
-- termine l'emprunt d'identité U_5 :&#160;
REVERT;&#160;
&#160;
-- placement dans le contexte de U_4 :&#160;
EXECUTE AS USER = 'U_4';&#160;
&#160;
-- révocation de l'octroi préalablement accordé à U_6 sous le compte de l'utilisateur U_4 :&#160;
REVOKE SELECT ON dbo.T_CLIENT TO U_6;&#160;
&#160;
-- termine l'emprunt d'identité U_4 :&#160;
REVERT;&#160;
&#160;
-- placement dans le contexte de U_6 :&#160;
EXECUTE AS USER = 'U_6';&#160;
&#160;
-- test si U_6 peut lire la table client ;&#160;
SELECT * FROM dbo.T_CLIENT&#160;
-- aucun doute, la lecture par U_6 est possible&#160;
&#160;
SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES&#160;
&#160;
GRANTOR    GRANTEE   TABLE_CATALOG   TABLE_SCHEMA     TABLE_NAME   PRIVILEGE_TYPE IS_GRANTABLE&#160;
---------- --------- --------------- ---------------- ------------ -------------- ------------&#160;
U_5        U_6       DB_TESTS        dbo              T_CLIENT     SELECT         NO

Vous devez comprendre que les privilèges se cumulent et que le retrait d'un privilège donné par X n'affecte pas les privilèges donnés par Y. Beaucoup d'utilisateurs naïfs sont choqués par cette façon de faire, mais elle est nécessaire pour garder la cohérence de la base. En effet, d'une part nous sommes sur des objets logiques et d'autre part la sécurité est cloisonnée de telle sorte que chacun des utilisateurs SQL soit responsable directement des privilèges qu'il accorde ou révoque pour les besoins fonctionnels d'un service par exemple. Néanmoins, il n'est pas possible de donner plusieurs fois le même privilège venant du même utilisateur, même si l'absence d'erreur d'exécution dans ce cas particulier semble l'indiquer.

Enfin, l'utilisation de la commande DENY spécifique à SQL Server est très tentante, mais comme je l'ai déjà dit, elle est susceptible de vous poser plus de problèmes qu'elle n'en résoudra…

VII - Recoler les morceaux…

Comme nous l'avons dit, il y a une dichotomie entre les comptes de connexion et les utilisateurs. Mais que se passe-t-il si jamais je dois restaurer une base de production dotée de nombreux utilisateurs sur un autre serveur dont la base master ne contient pas tous les comptes de connexion originaux ?

Vous comprenez que la dichotomie compte de connexion dans master / utilisateur dans base peut être ennuyeuse dans ce cas. Rassurez-vous, il existe plusieurs techniques pour ce cas de figure.

Faire un script des logins : cela consiste à demander à SQL Server de créer le script des comptes de connexion de cette base et le relancer en cas de besoin.

Utiliser la procédure sp_change_users_login (ou alter user) : pour régénérer le mapping entre les utilisateurs et les comptes de connexion après avoir recréé lesdits comptes de connexion.

VIII - CONCLUSION

Voici un petit tour de fait sur le sujet de la sécurité sous SQL Server. Mais il y aurait encore beaucoup de choses à dire sur la sécurité dans SQL Server, et notamment parler des rôles d'application de la dépersonnalisation, de l'authentification par accréditations (credentials) et encore du cryptage des données !

IX - Compléments

IX-A - Vues de gestion de la sécurité

INFORMATION_SCHEMA.TABLE_PRIVILEGES : pour les privilèges donnés directement à un utilisateur pour une table ou une vue.

INFORMATION_SCHEMA.COLUMN_PRIVILEGES : pour les privilèges donnés directement à un utilisateur pour certaines colonnes d'une table ou d'une vue.

IX-B - -- vues niveau serveur

sys.server_principals

sys.server_permissions

sys.server_role_members

sys.sql_logins

sys.login_token

IX-C - -- vues niveau base

sys.database_principals

sys.database_role_members

sys.database_permissions

IX-D - Liste des principales commandes de gestion de la sécurité

CREATE, ALTER, DROP LOGIN …

CREATE, ALTER, DROP USER …

CREATE, ALTER, DROP ROLE …

GRANT

REVOKE

DENY

EXECUTE AS

REVERT

IX-E - Liste des principales procédures de gestion de la sécurité

sp_addapprole

sp_addlinkedsrvlogin

sp_addlogin

sp_addremotelogin

sp_addrole

sp_addrolemember

sp_addserver

sp_addsrvrolemember

sp_adduser

sp_approlepassword

sp_change_users_login

sp_changedbowner

sp_changeobjectowner

sp_dbfixedrolepermission

sp_defaultdb

sp_defaultlanguage

sp_denylogin

sp_dropalias

sp_dropapprole

sp_droplinkedsrvlogin

sp_droplogin

sp_dropremotelogin

sp_droprole

sp_droprolemember

sp_dropserver

sp_dropsrvrolemember

sp_dropuser

sp_grantdbaccess

sp_grantlogin

sp_helpdbfixedrole

sp_helplinkedsrvlogin

sp_helplogins

sp_helpntgroup

sp_helpremotelogin

sp_helprole

sp_helprolemember

sp_helprotect

sp_helpsrvrole

sp_helpsrvrolemember

sp_helpuser

sp_MShasdbaccess

sp_password

sp_remoteoption

sp_revokedbaccess

sp_revokelogin

sp_setapprole

sp_srvrolepermission

sp_validatelogins

IX-F - Fonctions

- SYSTEM_USER : renvoie le nom de la connexion courante ;

- SUSER_NAME() : identifie l'utilisateur pour la connexion ;

- SUSER_SNAME() : retourne le nom de connexion associé à un numéro d'identification de sécurité ;

- USER : utilisateur SQL actuel ;

- CURRENT_USER : synonyme de USER ;

- USER_NAME() : synonyme de USER, mais on peut préciser un user ID ;

- IS_MEMBER(…) : indique si l'utilisateur actuel est membre du groupe Microsoft Windows ou du rôle de base de données Microsoft SQL Server spécifié ;

- IS_SRVROLEMEMBER(…) : indique si une connexion SQL Server appartient au rôle serveur fixe spécifié ;

- sys.fn_builtin_permissions(…) : retourne une description de la hiérarchie des autorisations intégrées du serveur ;

- Has_perms_by_name(…) : évalue l'autorisation effective de l'utilisateur actuel sur un élément sécurisable.

NOTA : cet article n'a pas la prétention de l'exhaustivité ! En effet, il faudrait encore parler de l'impersonnalisation, la dépersonnalisation, l'utilisation de proxies et de certificats, voire du cryptage !!! Si ce sujet vous passionne, l'ouvrage qui en parle le plus est : « Securing SQL Server 2005 » chez SyngPress(2007).