Narcis Miclaus

7 min di lettura

SQL Server, dalle fondamenta Lezione 33 / 40

Sicurezza di base: login, utenti, ruoli

Principal, schemi, la trappola del ruolo public, principio del minimo privilegio. Più note specifiche GDPR per un'azienda dell'UE.

La sicurezza in SQL Server è un libro intero. Questa lezione è il riassunto da un capitolo solo che ogni sviluppatore e ogni DBA junior deve conoscere: come funziona l’autenticazione, come sono organizzate le autorizzazioni, cos’è il ruolo public e perché è una trappola, e le considerazioni in chiave GDPR per un’azienda europea come Runehold.

La scala dei principal

SQL Server ha due livelli di “chi sei”:

  • Server principal (alias login, accessi) — si autenticano sull’istanza di SQL Server. Account Windows, account Azure AD o nomi utente con autenticazione SQL.
  • Database principal (alias utenti) — esistono dentro un singolo database, mappati a un login.

Non puoi connetterti a un database senza un login (a livello di istanza) che mappa a un utente (a livello di database) lì dentro. Pensala come “la chiave magnetica dell’hotel ti fa entrare nell’edificio (login), e separatamente nella tua stanza (utente).”

Tipi di login

-- Login Windows (utente di dominio o locale)
CREATE LOGIN [RUNEHOLD\anne.devries] FROM WINDOWS;

-- Login con autenticazione SQL (nome utente + password)
CREATE LOGIN app_user WITH PASSWORD = 'SecurePa$$w0rd!',
    CHECK_POLICY = ON,        -- applica la policy password di Windows
    CHECK_EXPIRATION = ON;    -- scadenza secondo policy

-- Login Azure AD (su Azure SQL Database / MI)
CREATE LOGIN [anne@runehold.com] FROM EXTERNAL PROVIDER;

I login Windows/Azure AD sono da preferire. Ereditano la policy password della tua organizzazione, l’MFA, la disabilitazione, l’auditing. I login con autenticazione SQL si configurano in fretta e diventano un mal di testa di manutenzione: non scadono senza meccanismi esterni, la rotazione delle password è una seccatura, eccetera.

Utenti e mappatura

USE Runehold;

-- Crea un utente mappato a un login
CREATE USER anne_devries FOR LOGIN [RUNEHOLD\anne.devries];

-- Crea un utente "orfano" (raro, non mappato a un login — disabilitato di default)
CREATE USER some_user WITHOUT LOGIN;

Il nome dell’utente dentro il database non deve per forza coincidere con quello del login, anche se di solito lo si fa per chiarezza.

Schemi e schema di default

Ogni utente ha uno schema di default — lo schema che SQL Server cerca quando scrivi SELECT * FROM Customer senza un prefisso di schema.

CREATE USER anne_devries FOR LOGIN [RUNEHOLD\anne.devries]
    WITH DEFAULT_SCHEMA = Sales;

Adesso anne_devries che scrive SELECT * FROM Customer trova Sales.Customer. Senza il default, cercherebbe dbo.Customer, non lo troverebbe e fallirebbe.

Per gli account di servizio applicativi, dbo come schema di default va bene. Per le persone, impostarlo allo schema in cui lavorano di più è una piacevole comodità.

Ruoli a livello server e database

I ruoli raggruppano le autorizzazioni così concedi “il ruolo” e non “28 autorizzazioni individuali.”

Ruoli server fissi

  • sysadmin — dio. Può fare qualsiasi cosa. Concedi con parsimonia, idealmente a nessuno tranne i DBA “rompi-il-vetro”.
  • serveradmin — la maggior parte delle cose a livello server.
  • securityadmin — gestisce login e autorizzazioni (ma può auto-promuoversi; pericoloso quanto sysadmin).
  • dbcreator — crea e ripristina database.
  • processadmin — KILL delle sessioni.
  • public — ogni login ne fa parte, non si può rimuovere. Funge da contenitore delle “autorizzazioni di default”.

Usali con parsimonia. Per gli sviluppatori: di solito niente a livello server (o dbcreator per lo sviluppo locale). Per gli account di servizio: niente a livello server a meno che non serva davvero.

Ruoli database fissi

  • db_owner — dio dentro il database.
  • db_datareader — SELECT su tutto.
  • db_datawriter — INSERT/UPDATE/DELETE su tutto.
  • db_ddladmin — esegue DDL.
  • db_backupoperator — BACKUP DATABASE / LOG.
  • db_securityadmin — gestisce le autorizzazioni nel DB.
  • public — di nuovo, il contenitore di default.

Ruoli personalizzati

Buona pratica:

USE Runehold;

-- Crea un ruolo per utenti di reporting in sola lettura
CREATE ROLE ReadOnly_Reporting;
GRANT SELECT ON SCHEMA::Reporting TO ReadOnly_Reporting;
GRANT SELECT ON SCHEMA::Sales      TO ReadOnly_Reporting;

-- Aggiungi un utente
ALTER ROLE ReadOnly_Reporting ADD MEMBER anne_devries;

-- Crea un ruolo per l'applicazione
CREATE ROLE App_Runehold;
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::Sales      TO App_Runehold;
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::Catalog    TO App_Runehold;
GRANT EXECUTE                         ON SCHEMA::Sales    TO App_Runehold;
ALTER ROLE App_Runehold ADD MEMBER app_user;

I ruoli personalizzati raggruppati attorno ai ruoli reali di team/applicazione mantengono le autorizzazioni gestibili. Non concedere mai direttamente agli utenti; sempre tramite ruoli. Anche se è un ruolo con un solo membro oggi.

La trappola del ruolo public

Ogni utente è automaticamente membro di public in master e in ogni database utente. È il contenitore “di default”.

Problema: public in master ha una concessione di default sorprendente: GUEST un tempo aveva accesso al database guest, e public ha accesso a molte view di metadati, oltre ad alcune view di catalogo. Un utente interno ostile a cui non è stato concesso nulla esplicitamente può comunque enumerare i database, alcuni nomi di oggetti e i metadati a livello di servizio.

Negli ambienti ad alta sicurezza si “indurisce” public revocando tutto ciò che gli è stato concesso in master. Attenzione; alcune applicazioni dipendono dall’accesso ai metadati. Testa prima in un ambiente di staging.

Per la maggior parte delle aziende la regola è più semplice: non concedere nulla a public sui database utente. Qualsiasi autorizzazione che concederesti a public è probabilmente meglio concederla a un ruolo specifico.

Autorizzazioni: GRANT, DENY, REVOKE

-- Concede un'autorizzazione
GRANT SELECT ON SCHEMA::Sales TO ReadOnly_Reporting;

-- Revoca (rimuove una specifica concessione, non un deny generale)
REVOKE SELECT ON SCHEMA::Sales TO ReadOnly_Reporting;

-- Deny (blocca esplicitamente, prevale su qualsiasi grant)
DENY SELECT ON Sales.Customer TO some_role;
  • GRANT aggiunge un’autorizzazione.
  • REVOKE rimuove una specifica concessione.
  • DENY blocca esplicitamente, e prevale su qualsiasi grant. Un utente con SELECT concesso tramite ruolo e DENY sullo stesso oggetto ottiene DENY.

Usa DENY con parsimonia — è efficace ma confonde. La maggior parte degli schemi di accesso dovrebbe essere costruita con il solo GRANT.

Minimo privilegio

Regola: ogni account ha esattamente le autorizzazioni che gli servono per fare il suo lavoro. Non di più.

Account di servizio applicativi:

  • Un ruolo che può leggere, inserire, aggiornare, eliminare nel/i proprio/i schema.
  • EXECUTE sulle proprie stored procedure.
  • Niente DDL. Niente db_owner. Niente db_ddladmin.

Account umani:

  • Accesso in lettura a ciò che devono vedere.
  • Execute sulle procedure di reporting.
  • Accesso in scrittura strettamente controllato se necessario.

DBA:

  • db_owner sui propri database. sysadmin per una o due persone che ne hanno davvero bisogno. Tutti gli altri ne ottengono meno.

Verifica periodicamente. Interroga quali autorizzazioni ha ogni utente:

SELECT
    pr.name                                             AS principal_name,
    pr.type_desc                                        AS principal_type,
    p.class_desc,
    OBJECT_SCHEMA_NAME(p.major_id) + '.' + OBJECT_NAME(p.major_id) AS object_name,
    p.permission_name,
    p.state_desc
FROM sys.database_permissions  AS p
JOIN sys.database_principals   AS pr ON pr.principal_id = p.grantee_principal_id
WHERE pr.type NOT IN ('R', 'A')     -- salta ruoli e ruoli applicativi per chiarezza
ORDER BY pr.name, p.class_desc;

Esegui questo una volta a trimestre. Rimuovi qualsiasi cosa inattesa.

Database “contained”

Un’opzione più recente (da SQL Server 2012) sono i database contained — database i cui utenti esistono interamente al loro interno, non mappati a login a livello server. Utili per Azure SQL Database (che è intrinsecamente contained) e per la portabilità: puoi spostare un database contained su un altro server senza ricreare i login.

Sintassi:

-- Richiede sp_configure 'contained database authentication' = 1 a livello server
ALTER DATABASE Runehold SET CONTAINMENT = PARTIAL;

USE Runehold;
CREATE USER app_user WITH PASSWORD = 'SecurePa$$w0rd!';

Niente login a livello server. L’utente si autentica direttamente al database.

Bello per l’isolamento. Strumenti di auditing meno maturi attorno. Scegli in base ai tuoi strumenti di monitoraggio / governance.

Note specifiche GDPR

Per un’azienda europea come Runehold, alcune cose non sono negoziabili:

  1. I dati personali devono essere cifrati a riposo. TDE (Transparent Data Encryption), Always Encrypted (per colonne specifiche) o cifratura del filesystem. Pratica standard ormai.
  2. Connessioni cifrate. Forza il TLS tra applicazioni e database. Encrypt=True nelle stringhe di connessione.
  3. Logging degli accessi. Ogni lettura di dati personali da parte di un utente umano andrebbe loggata. SQL Server Audit è lo strumento; un SIEM esterno (Splunk, Datadog) legge e archivia.
  4. Diritto alla cancellazione — vedi la lezione 31 sui backup. I dati live cancellati subito; i backup escono di scena in una finestra definita.
  5. Portabilità dei dati — devi essere in grado di esportare i dati di un cliente su richiesta. Costruisci la query di esportazione adesso; non provare a inventarla quando ti chiama il legale.

Niente di tutto questo cambia il modello di sicurezza di base, ma aggiunge vincoli. Per Runehold nello specifico, il DPO (Data Protection Officer) detta quali colonne contano come “dati personali” e che livello di cifratura ottiene ognuna. Consultalo; non inventare policy per conto tuo.

Prova questo sulla tua macchina

USE Runehold;
GO

-- 1. Crea un ruolo in sola lettura, aggiungi autorizzazioni
CREATE ROLE RolesDemo_ReadOnly;
GRANT SELECT ON SCHEMA::Sales TO RolesDemo_ReadOnly;

-- 2. Crea un utente (senza login, solo per la demo)
CREATE USER RolesDemo_User WITHOUT LOGIN;
ALTER ROLE RolesDemo_ReadOnly ADD MEMBER RolesDemo_User;

-- 3. Testa cosa può fare l'utente
EXECUTE AS USER = 'RolesDemo_User';
SELECT TOP 1 * FROM Sales.Customer;  -- funziona
SELECT TOP 1 * FROM Sales.Customer;
-- INSERT INTO Sales.Customer (Name, Country) VALUES ('X', 'NL');  -- negato
REVERT;

-- 4. Audit: che autorizzazioni ha questo utente?
SELECT p.class_desc,
       OBJECT_SCHEMA_NAME(p.major_id) + '.' + OBJECT_NAME(p.major_id) AS obj,
       p.permission_name, p.state_desc
FROM sys.database_permissions AS p
JOIN sys.database_principals AS pr ON pr.principal_id = p.grantee_principal_id
WHERE pr.name IN ('RolesDemo_User', 'RolesDemo_ReadOnly');

-- 5. Pulizia
ALTER ROLE RolesDemo_ReadOnly DROP MEMBER RolesDemo_User;
DROP USER RolesDemo_User;
DROP ROLE RolesDemo_ReadOnly;

Prossima lezione: tempdb — il parco giochi condiviso che ogni sessione usa, e la fonte di più problemi di performance misteriosi di qualsiasi altra singola risorsa.

Cerca