Rôle Public dans SQL Server

Les rôles de base de données sont similaires aux groupes Windows — au lieu de révoquer ou d'accorder l'accès à chaque utilisateur séparément, les administrateurs gèrent l'accès en révoquant ou en accordant des permissions aux rôles et en modifiant l'appartenance aux rôles. Utiliser des rôles facilite l'attribution et la révocation précises des privilèges pour les utilisateurs de la base de données. Et comme plusieurs utilisateurs peuvent être membres d'un rôle de base de données SQL, vous pouvez facilement gérer les droits pour tout un groupe d'utilisateurs en une seule fois.

Dans cet article, nous expliquons le rôle public dans Microsoft SQL Server et certaines des meilleures pratiques qui y sont associées. (Nous utilisons délibérément des lettres minuscules ici, car c'est ainsi que le rôle public est orthographié dans le monde de SQL Server, contrairement au monde d'Oracle.)

Types de rôles dans SQL Server

Microsoft SQL Server offre plusieurs types de rôles préconstruits :

• Rôles de serveur fixes — On les appelle rôles de serveur “fixes” parce que, à l'exception du rôle public, ils ne peuvent pas être modifiés ou supprimés
• Rôles de base de données fixes
• Application rôle — Un principal de base de données qu'une application peut utiliser pour fonctionner avec son propre ensemble de permissions (désactivé par défaut)
• Rôles définis par l'utilisateur (à partir de SQL Server 2012) — Seules les permissions de niveau serveur peuvent être ajoutées aux rôles définis par l'utilisateur

Où le rôle public s'insère

Toutes les plateformes de bases de données sont équipées d'un rôle prédéfini appelé public, mais la mise en œuvre de ce rôle varie selon la plateforme. Dans SQL Server, le rôle public fait partie du rôle de serveur fixe, et des autorisations peuvent être accordées à, refusées à ou révoquées des autorisations du rôle public de SQL Server.

Lorsqu'un identifiant SQL Server est créé, le rôle public lui est attribué et ne peut être révoqué. Si le principal du serveur ne se voit pas accorder ou refuser des permissions spécifiques sur un objet sécurisable, l'identifiant héritera automatiquement des permissions accordées au rôle public pour cet objet.

Permissions attribués au rôle Public

Afin de maintenir la sécurité de SQL Server et de se conformer à de nombreuses réglementations, y compris PCI DSS et HIPAA, vous devez connaître tous les rôles au niveau du serveur et de la base de données attribués à chaque utilisateur. Examinons les permissions au niveau du serveur attribuées au rôle public en utilisant une requête transact-SQL de Management Studio :

      SELECT sp.state_desc as "Permission State", sp.permission_name as "Permission",
sl.name "Principal Name",sp.class_desc AS "Object Class", ep.name "End Point"
FROM sys.server_permissions AS sp
  JOIN sys.server_principals AS sl
    ON sp.grantee_principal_id = sl.principal_id
  LEFT JOIN sys.endpoints AS ep
    ON sp.major_id = ep.endpoint_id
WHERE sl.name = 'public';
      

Comme le montre le tableau ci-dessus, seulement cinq permissions de niveau serveur sont attribuées au rôle public. Notez que la permission VIEW ANY DATABASE ne donne pas aux utilisateurs l'accès aux objets de la base de données ; elle leur permet uniquement de lister toutes les bases de données dans une instance de SQL Server. Par conséquent, si vous créez un nouveau login et n'assignez aucun autre rôle ou permission, l'utilisateur peut seulement se connecter à l'instance et est incapable de faire quoi que ce soit d'autre.

Permissions lorsqu'un identifiant SQL Server se voit attribuer une base de données par défaut

Maintenant, créons un identifiant SQL Server qui utilise l'authentification SQL en attribuant une base de données par défaut à cet utilisateur.

      USE [master]
GO
CREATE LOGIN [SQTest] WITH PASSWORD=N'nhggLboBn6SHolSWfipjzO/7GYw8M2RMbCt1LsCTK5M=', DEFAULT_DATABASE=[SBITS], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
      

Si nous nous connectons en tant que cet utilisateur et listons toutes les permissions au niveau de la base de données pour la base de données par défaut de l'utilisateur SBITS, nous pouvons voir quelles permissions ils ont. Comme le montre l'exemple ci-dessous, l'utilisateur Stealth n'a aucune permission sur la base de données SBITS même si c'est la base de données par défaut de l'utilisateur. En d'autres termes, le fait qu'une base de données utilisateur par défaut soit attribuée à une connexion utilisateur ne signifie pas que l'utilisateur pourra voir les objets de la base de données ou les données.

Pour vérifier cela, nous pouvons utiliser le script suivant :

      EXECUTE AS LOGIN= 'SQTest';
GO
USE SBITS
GO
SELECT dp.state_desc AS "Class Description", dp.permission_name AS "Permission Name",
SCHEMA_NAME(ao.schema_id) AS "Schema Name", ao.name AS "Object Name"
FROM sys.database_permissions dp
 LEFT JOIN sys.all_objects ao
  ON dp.major_id = ao.object_id
 JOIN sys.database_principals du
  ON dp.grantee_principal_id = du.principal_id
WHERE du.name = 'TestLoginPerms'
 AND ao.name IS NOT NULL
ORDER BY ao.name;
REVERT
      

Permissions hérités par le rôle Public sur la base de données Master

Puisque le nom d'utilisateur Stealth fait partie du rôle public par défaut, voyons les permissions héritées par le rôle public sur la base de données master.

      USE master;
GO
 SELECT sp.state_desc AS "Permission State", sp.permission_name AS "Permission",
 SCHEMA_NAME(ao.schema_id) AS 'Schema', ao.name AS "Object Name"
 FROM sys.database_permissions sp
  LEFT JOIN sys.all_objects ao
     ON sp.major_id = ao.object_id
   JOIN sys.database_principals dp
     ON sp.grantee_principal_id = dp.principal_id
 WHERE dp.name = 'public'
 AND ao.name IS NOT NULL
 ORDER BY ao.name
      

In SQL Server 2016, there are 2,089 permissions stored in the master database granted to the public role. While it might seem daunting, they are all SELECT permissions and do not allow the user Stealth to make any changes in the master database. However, it is good practice to revoke some of the permissions based on the security policies in your organization. However, exercise caution while revoking them because some are required by users for normal operations in certain circumstances.

Simplification de la révision et de la gestion des rôles

Plutôt que de recourir à des scripts personnalisés complexes pour comprendre les problèmes liés au rôle public dans une instance à la fois, envisagez d'utiliser Netwrix Access Analyzer. Cette plateforme de gouvernance de l'accès aux données peut énumérer tous les rôles et privilèges SQL Server, y compris les rôles publics SQL Server et les rôles de base de données publics SQL Server, et produire des rapports de droits détaillés prêts à l'emploi. Elle offre une vue consolidée du rôle public à travers tous les SQL Servers de votre entreprise et vous aide à remédier à tout problème en un clic.

Meilleures pratiques pour le rôle Public dans SQL Server

Nous recommandons les meilleures pratiques suivantes en ce qui concerne le rôle public dans SQL Server :

• N'accordez aucun privilège supplémentaire au rôle public en dehors des privilèges par défaut, en aucune circonstance. Si nécessaire, utilisez un rôle défini par l'utilisateur.
• Ne modifiez pas les autorisations au niveau du serveur pour le rôle public car cela pourrait empêcher les utilisateurs de se connecter à la base de données.
• Vérifiez les permissions publiques à chaque fois que vous mettez à niveau votre SQL Server puisque Microsoft apporte souvent des modifications au rôle public.

FAQ

Quel est le rôle de base de données publique ?

Les autorisations accordées au rôle de base de données public sont héritées par chaque utilisateur de la base de données.

Quelles permissions le rôle public possède-t-il dans SQL Server ?

Chaque connexion SQL Server appartient au rôle de serveur public. Lorsqu'un principal de serveur n'a pas reçu ou s'est vu refuser des permissions spécifiques sur un objet sécurisable, l'utilisateur hérite des permissions accordées à cet objet pour le rôle public.