Developpez.com

Télécharger gratuitement le magazine des développeurs, le bimestriel des développeurs avec une sélection des meilleurs tutoriels

Developpez.com - SGBD & SQL
X

Choisissez d'abord la catégorieensuite la rubrique :


Date de publication : 26/06/2009 , Date de mise à jour : 26/06/2009

Par SQLPro (autres articles) (SQL spot)
 

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

               Version hors-ligne (Miroir)

I. LES COMPTES DE CONNEXION
II. META DONNÉES DES COMPTES DE CONNEXION
III. LES UTILISATEURS SQL
IV. META DONNÉES DES UTIISATEURS 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 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 :

Vous pouvez aussi utiliser les fonctions suivantes :

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


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 :

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;

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 :

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; --> 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 :
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 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 :
GRANT SELECT                 ON T_CLIENT  TO U_LECTEUR, R_AJOUTEUR; 
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 :
-- 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

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


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


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

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)



               Version hors-ligne (Miroir)

Valid XHTML 1.0 TransitionalValid CSS!

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.

Contacter le responsable de la rubrique SGBD & SQL