Authentification, connexion, utilisateur et privilèges

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

1 commentaire Donner une note à l'article (4)

Article lu   fois.

L'auteur

Profil ProSite personnelSite personnel

Liens sociaux

Viadeo Twitter Facebook Share on Google+   

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 :

 
Sélectionnez
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 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.

II. META DONNÉ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 :

 
Sélectionnez
SELECT * &#160;
FROM   sys.server_principals&#160;
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 une base de données. Chaque utilisateur SQL d'une base étant différente 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 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 :

 
Sélectionnez
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. META DONNÉES DES UTILISATEURS 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.

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 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 serveurs 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 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 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

VI. 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).

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 :

 
Sélectionnez
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 :

 
Sélectionnez
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 :

 
Sélectionnez
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 :

 
Sélectionnez
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

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 à ben 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è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.

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èges attribué.

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

La séquence suivante :

 
Sélectionnez
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 :

 
Sélectionnez
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 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.

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 :

 
Sélectionnez
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 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 :

 
Sélectionnez
-- 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è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 :

 
Sélectionnez
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èges et qu'on ne le retire qu'une seule fois ? Comme un bon exemple vaut mieux qu'un long discours, lançons nous...

 
Sélectionnez
-- 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;
-- revocation 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é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...

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

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ôle 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)

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+   

Livres
SQL - développement
SQL - le cours de référence sur le langage SQL
Avant d'aborder le SQL
Définitions
SGBDR fichier ou client/serveur ?
La base de données exemple (gestion d'un hôtel)
Modélisation MERISE
Mots réservés du SQL
Le SQL de A à Z
Les fondements
Le simple (?) SELECT
Les jointures, ou comment interroger plusieurs tables
Groupages, ensembles et sous-ensembles
Les sous-requêtes
Insérer, modifier, supprimer
Création des bases
Gérer les privilèges ("droits")
Toutes les fonctions de SQL
Les techniques des SGBDR
Les erreur les plus fréquentes en SQL
Les petits papiers de SQLPro
Conférence Borland 2003
L'héritage des données
Données et normes
Modélisation par méta données
Optimisez votre SGBDR et vos requêtes SQL
Le temps, sa mesure, ses calculs
QBE, le langage de ZLOOF
Des images dans ma base
La jointure manquante
Clefs auto incrémentées
L'indexation textuelle
L'art des "Soundex"
Une seule colonne, plusieurs données
La division relationnelle, mythe ou réalité ?
Gestion d'arborescence en SQL
L'avenir de SQL
Méthodes et standards
Les doublons
SQL Server
Eviter les curseurs
Un aperçu de TRANSACT SQL V 2000
SQL Server 2000 et les collations
Sécurisation des accès aux bases de données SQL Server
Des UDF pour SQL Server
SQL Server et le fichier de log...
Paradox
De vieux articles publiés entre 1995 et 1999 dans la défunte revue Point DBF

  

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.