Ruolo Pubblico in SQL Server

I ruoli del database sono simili ai gruppi di Windows — anziché revocare o concedere l'accesso a ogni utente separatamente, gli amministratori gestiscono l'accesso concedendo o revocando i permessi dai ruoli e modificando l'appartenenza ai ruoli. Utilizzare i ruoli rende più semplice concedere e revocare i privilegi agli utenti del database in modo accurato. E poiché più utenti possono essere membri di un ruolo del database SQL, è possibile gestire facilmente i diritti per un intero gruppo di utenti in una sola volta.

In questo post, spieghiamo il ruolo di public in Microsoft SQL Server e alcune delle migliori pratiche ad esso correlate. (Stiamo deliberatamente utilizzando lettere minuscole qui, poiché così viene scritto il ruolo public nel mondo di SQL Server, a differenza del mondo Oracle.)

Tipi di ruoli in SQL Server

Microsoft SQL Server offre diversi tipi di ruoli predefiniti:

• Fixed server roles — They are called “fixed” server roles because, except for the public role, they cannot be modified or drop
• Ruoli del database fissi
• Applicazione ruolo — Un principale del database che può essere utilizzato da un'applicazione per eseguire con il proprio set di autorizzazioni (disabilitato per impostazione predefinita)
• Ruoli definiti dall'utente (a partire da SQL Server 2012) — Solo i permessi a livello di server possono essere aggiunti ai ruoli definiti dall'utente

Dove si colloca il Ruolo Pubblico

Tutte le piattaforme di database includono un ruolo predefinito chiamato public, ma l'implementazione di questo ruolo varia a seconda della piattaforma. In SQL Server, il ruolo public fa parte del ruolo fisso del server, e i permessi possono essere concessi, negati o revocati dalle autorizzazioni del ruolo public di SQL Server.

Quando viene creato un login di SQL Server, al login viene assegnato il ruolo public che non può essere revocato. Se al principal del server non vengono concessi o negati permessi specifici su un oggetto protetto, il login erediterà automaticamente i permessi per quell'oggetto che sono concessi al ruolo public.

Permessi assegnati al ruolo Public

Per mantenere la sicurezza di SQL Server e conformarsi a molte normative, inclusi PCI DSS e HIPAA, è necessario conoscere tutti i ruoli a livello di server e database assegnati a ciascun utente. Esaminiamo i permessi a livello di server assegnati al ruolo pubblico utilizzando una query transact-SQL di 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';
      

Come mostra la tabella sopra, solo cinque permessi a livello di server sono assegnati al ruolo pubblico. Notare che il permesso VIEW ANY DATABASE non consente agli utenti di accedere a nessun oggetto del database; permette solo di elencare tutti i database in un'istanza di SQL Server. Pertanto, se si crea un nuovo login e non si assegnano altri ruoli o permessi, l'utente può solo accedere all'istanza e non è in grado di fare altro.

Permessi quando a un Login di SQL Server viene assegnato un Database predefinito

Ora, creiamo un login di SQL Server che utilizzi l'autenticazione SQL assegnando un database predefinito a quell'utente.

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

Se effettuiamo l'accesso come quell'utente e elenchiamo tutti i permessi a livello di database per il database predefinito dell'utente SBITS, possiamo vedere quali permessi hanno. Come mostrato di seguito, l'utente Stealth non ha alcun permesso sul database SBITS anche se quello è il database predefinito dell'utente. In altre parole, il fatto che un database utente predefinito sia assegnato a un login utente, non significa che l'utente sarà in grado di vedere gli oggetti del database o i dati.

Per verificarlo, possiamo utilizzare lo script seguente:

      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
      

Permessi ereditati dal ruolo pubblico sul database Master

Poiché il login utente Stealth fa parte del ruolo public per impostazione predefinita, vediamo i permessi ereditati dal ruolo public sul database 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.

Semplificazione della revisione e gestione dei ruoli

Invece di ricorrere a complessi script personalizzati per capire i problemi relativi al ruolo pubblico in un'istanza alla volta, prendi in considerazione l'uso di Netwrix Access Analyzer. Questa piattaforma di governance dell'accesso ai dati può elencare tutti i ruoli e i privilegi di SQL Server, inclusi i ruoli pubblici di SQL Server e i ruoli pubblici del database di SQL Server, e produrre dettagliati rapporti di autorizzazione pronti all'uso. Fornisce una visione unificata del ruolo pubblico su tutti i SQL Server della tua impresa e ti aiuta a risolvere eventuali problemi con un clic.

Migliori pratiche per il ruolo pubblico in SQL Server

Raccomandiamo le seguenti migliori pratiche quando si tratta di ruolo pubblico in SQL Server:

• Non concedere ulteriori privilegi al ruolo pubblico al di fuori dei privilegi predefiniti, in nessuna circostanza. Se necessario, utilizzare un ruolo definito dall'utente.
• Non modificare i permessi a livello di server del ruolo pubblico perché ciò potrebbe impedire agli utenti di connettersi al database.
• Rivedi i permessi pubblici ogni volta che aggiorni il tuo SQL Server poiché Microsoft apporta spesso modifiche al ruolo pubblico.

FAQ

Cos'è il ruolo di database pubblico?

I permessi concessi al ruolo pubblico del database sono ereditati da ogni utente del database.

Quali permessi ha il ruolo pubblico in SQL Server?

Ogni login di SQL Server appartiene al ruolo del server pubblico. Quando a un principale del server non sono stati concessi o negati permessi specifici su un oggetto protetto, l'utente eredita i permessi concessi a quel ruolo pubblico sull'oggetto.