Authentification, connexion, utilisateur et privilèges

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; 
GRANT SHUTDOWN ON C_COMPTA;

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

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

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

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.

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; 
REVOKE SELECT ON T_CLENT TO U_1; 
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; 
DENY SELECT ON T_CLENT TO U_1; 
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.

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;

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…

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

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; 
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON SCHEMA::S_VENTE TO U_VENTE; 
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON SCHEMA::S_RH TO U_RH; 
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.

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 : 
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'; 
 
-- révocation 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é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…

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.

sys.server_principals

sys.server_permissions

sys.server_role_members

sys.sql_logins

sys.login_token

sys.database_principals

sys.database_role_members

sys.database_permissions

CREATE, ALTER, DROP LOGIN …

CREATE, ALTER, DROP USER …

CREATE, ALTER, DROP ROLE …

GRANT

REVOKE

DENY

EXECUTE AS

REVERT

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

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