Les éléments de gestion de la sécurité dans SQL Server sont nombreux et exhaustifs. Encore faut-il savoir s’y repérer ! Petite revue de détail sur le sujet…..

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.
Bien que l’on puisse définir un compte de connexion sans pour autant lui associer un utilisateur, cette façon de faire revêt peu d’intérêt car on ne pourra pas faire grand chose une fois connecté ! Notons en outre qu’il est aussi possible de définir un utilisateur sans qu’il soit associé à compte de connexion.

Enfin, 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…

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 :
CREATE LOGIN <nom_connexion_SQL> WITH PASSWORD = '<un_mot_de_passe>';

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 est si bien qu’entre deux serveurs SQL le même nom de connexion n’a pas forcément le même SID !

A 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èges 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 compte 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.

META DONNÉES DES COMPTES DE CONNEXION

N’oubliez pas que les compte 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 *  

FROM   sys.server_principals 

WHERE  type_desc = 'WINDOWS_LOGIN'

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

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 est si bien que pour naviguer d’une base à l’autre il faut créer autant d’utilisateur 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 :

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)

META DONNÉES DES UTIISATEURS SQL

N’oubliez pas que les utilisateurs sont enregistré 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é 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.

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 compte 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 chacun 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 quel 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 serveur SQL…
Par analogie, imaginez un sinistre majeur, comme un incendie ou les pompiers serait empêché de venir secourir les victimes par le fait de trop zélés policiers qui exigerait qu’ils prouvent leur appartenance aux hommes du feux… Impensable ! En matière de sinistre on doit agir vite. Les pompiers ont l’absolu priorité. De même que les données doivent, même dans un mode très dégradé de sécurité, être accessible le plus rapidement possible.
Prenez l’exemple de l’hôpital ou la confidentialité est la règle absolue… Jusqu’au moment ou 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épend 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
…

LES PRIVILÈGES

Il ne sert à rien de créer des comptes de connexion ou des utilisateurs SQL si l’on ne prévoir pas de gérer finement les autorisations auxquels chacune des entité 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).

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 :
EXECUTE sp_addsrvrolemember 'C_COMPTA', 'bulkadmin';
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 :

Liste des privilèges au niveau serveur :

Générique serveur : (GRANT TO )

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 ON LOGIN:: TO )

CONTROL
IMPERSONATE
VIEW DEFINITION
ALTER

Sur objet système (GRANT ON [sys.] TO )

SELECT
EXECUTE

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 :

Vous pouvez créer votre propres 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; –> octroi l’usage de la commande SELECT sur l’objet T_CLIENT à l’utilisateur U_LECTEUR
GRANT SELECT ON T_FACTURE TO R_AJOUTEUR; –> octroi l’usage de la commande SELECT sur l’objet facture au rôle R_AJOUTEUR

Liste des privilèges au niveau base :

Générique database : (GRANT TO )
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 ON [ ]. [ ( column1 [ , column2 [, columns3 …] ] ) ] TO )

ALTER
CONTROL
DELETE
EXECUTE
INSERT
RECEIVE
REFERENCES
SELECT
TAKE OWNERSHIP
UPDATE
VIEW CHANGE TRACKING
VIEW DEFINITION

Au niveau du schéma : ( GRANT ON SCHEMA:: TO )

ALTER
CONTROL
DELETE
EXECUTE
INSERT
REFERENCES
SELECT
TAKE OWNERSHIP
UPDATE
VIEW DEFINITION

Quelques privilèges importants…

Tout le monde à ben compris que les privilèges SELECT, INSERT, UPDATE et DELETE correspondent aux même commandes sur les tables ou les vues. En revanche le privilèges MERGE n’existe pas, puisqu’une MERGE n’est somme toutes 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. Pourrat-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é du nom, du prénom et d’une date de naissance. Il reste alors deux possibilité 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.

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èges attribué.
La différence peut paraître subtile. Prenons un exemple…

La séquence suivante :

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

La séquence suivante :

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 ou 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.

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; 

GRANT INSERT, UPDATE, DELETE ON T_FACTURE TO R_AJOUTEUR;

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 :
EXECUTE AS USER = 'U_COMPTA';
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…

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 leur 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 octroi 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 : 

CREATE USER U_1 FOR LOGIN C_1; 

CREATE USER U_2 FOR LOGIN C_2; 

CREATE USER U_3 FOR LOGIN C_3; 

 

-- octroi des privilèges de lecture et mise à jour de la table des clients: 

GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.T_CLIENT TO U_1 WITH GRANT OPTION; 

 

-- placement dans le contexte de U_1 : 

EXECUTE AS USER = 'U_1'; 

 

-- lancement d'un octroi à U_2 sous le compte de l'utilisateur U_1 : 

GRANT SELECT ON dbo.T_CLIENT TO U_2 WITH GRANT OPTION; 

 

-- termine l'emprunt d'identité U_1 : 

REVERT; 

 

-- placement dans le contexte de U_2 : 

EXECUTE AS USER = 'U_2'; 

 

-- lancement d'un octroi à U_3 sous le compte de l'utilisateur U_2 : 

GRANT SELECT ON dbo.T_CLIENT TO U_3 WITH GRANT OPTION; 

 

-- lancement d'un second octroi à U_3 sous le compte de l'utilisateur U_2 : 

GRANT UPDATE ON dbo.T_CLIENT TO U_3 WITH GRANT OPTION; 

--> Impossible de trouver le schéma 'dbo', car il n'existe pas ou vous ne possédez pas d'autorisation. 

 

SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES 

 

GRANTOR    GRANTEE   TABLE_CATALOG   TABLE_SCHEMA     TABLE_NAME   PRIVILEGE_TYPE IS_GRANTABLE 

---------- --------- --------------- ---------------- ------------ -------------- ------------ 

U_1        U_2       DB_TESTS        dbo              T_CLIENT     SELECT         YES 

U_2        U_3       DB_TESTS        dbo              T_CLIENT     SELECT         YES

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èges 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 fonction logiques.
Exemple :
Soit une base de données possédant les schémas SQL suivants :
S_REF –> toutes les table de référence (code postaus, 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 :

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

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èges 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 : 

CREATE LOGIN C_4 WITH PASSWORD = 'C4'; 

CREATE LOGIN C_5 WITH PASSWORD = 'C5'; 

CREATE LOGIN C_6 WITH PASSWORD = 'C6'; 

 

-- création de 3 utilisateurs associés aux connexions : 

CREATE USER U_4 FOR LOGIN C_4; 

CREATE USER U_5 FOR LOGIN C_5; 

CREATE USER U_6 FOR LOGIN C_6; 

 

-- octroi des privilèges de lecture et mise à jour de la table des clients : 

GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.T_CLIENT TO U_4, U_5 WITH GRANT OPTION; 

 

-- placement dans le contexte de U_4 : 

EXECUTE AS USER = 'U_4'; 

 

-- lancement d'un octroi à U_6 sous le compte de l'utilisateur U_4 : 

GRANT SELECT ON dbo.T_CLIENT TO U_6; 

 

-- termine l'emprunt d'identité U_4 : 

REVERT; 

 

-- placement dans le contexte de U_5 : 

EXECUTE AS USER = 'U_5'; 

 

-- lancement d'un octroi à U_6 sous le compte de l'utilisateur U_5 : 

GRANT SELECT ON dbo.T_CLIENT TO U_6; 

 

-- termine l'emprunt d'identité U_5 : 

REVERT; 

 

-- placement dans le contexte de U_4 : 

EXECUTE AS USER = 'U_4'; 

 

-- revocation de l'"octroi préalablement accordé à U_6 sous le compte de l'utilisateur U_4 : 

REVOKE SELECT ON dbo.T_CLIENT TO U_6; 

 

-- termine l'emprunt d'identité U_4 : 

REVERT; 

 

-- placement dans le contexte de U_6 : 

EXECUTE AS USER = 'U_6'; 

 

-- test si U_6 peut lire la table client ; 

SELECT * FROM dbo.T_CLIENT 

-- aucun doute, la lecture par U_6 est possible 

 

SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES 

 

GRANTOR    GRANTEE   TABLE_CATALOG   TABLE_SCHEMA     TABLE_NAME   PRIVILEGE_TYPE IS_GRANTABLE 

---------- --------- --------------- ---------------- ------------ -------------- ------------ 

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ées 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ées de telle sorte que chacun des utilsateurs 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èges 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 tentant, mais comme je l’ais déjà dit, elle est susceptible de vous poser plus de problèmes qu’elle n’en résoudra…

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é 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 besoins.

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.

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ôle d’application de la dépersonnalisation, de l’authentification par accréditations (credentials) et encore du cryptage des données !

* * *

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
— vues niveau serveur :
sys.server_principals
sys.server_permissions
sys.server_role_members
sys.sql_logins
sys.login_token
— vues niveau base :
sys.database_principals
sys.database_role_members
sys.database_permissions

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

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

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)

-------- 

Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/ 

Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server 

www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation 

* * * * *  Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence  * * * * *