Narcis Miclaus

7 min de citit

SQL Server, de la zero Lecția 33 / 40

Bazele de securitate: autentificări, utilizatori, roluri

Principali, scheme, capcana rolului public, principiul privilegiului minim. Plus note specifice GDPR pentru o firmă din UE.

Securitatea în SQL Server este o carte întreagă. Lecția de față e rezumatul de un capitol pe care fiecare dezvoltator și DBA junior trebuie să-l știe: cum funcționează autentificarea, cum sunt organizate permisiunile, ce este rolul public și de ce e o capcană, plus considerațiile GDPR pentru o companie europeană precum Runehold.

Scara principalilor

SQL Server are două niveluri de „cine ești tu”:

  • Principali la nivel de server (alias autentificări/logins) — se autentifică pe instanța SQL Server. Conturi Windows, conturi Azure AD sau utilizatori cu autentificare SQL.
  • Principali la nivel de bază de date (alias utilizatori/users) — există în interiorul unei singure baze de date, mapați la o autentificare.

Nu te poți conecta la o bază de date fără o autentificare (la nivel de instanță) care să fie mapată la un utilizator (la nivel de bază de date) acolo. Gândește-te ca la „cardul tău de hotel îți dă acces în clădire (login), iar separat în camera ta (user)”.

Tipuri de autentificare

-- Login Windows (utilizator de domeniu sau local)
CREATE LOGIN [RUNEHOLD\anne.devries] FROM WINDOWS;

-- Login cu autentificare SQL (utilizator + parolă)
CREATE LOGIN app_user WITH PASSWORD = 'SecurePa$$w0rd!',
    CHECK_POLICY = ON,        -- aplică politica de parole Windows
    CHECK_EXPIRATION = ON;    -- expiră conform politicii

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

Autentificările Windows/Azure AD sunt preferate. Moștenesc politica de parole, MFA, dezactivarea, auditul din organizație. Autentificările SQL sunt rapid de configurat și un coșmar de mentenanță — nu expiră fără mecanisme externe, rotația parolei e o corvoadă etc.

Utilizatorii și maparea

USE Runehold;

-- Creează un utilizator mapat la o autentificare
CREATE USER anne_devries FOR LOGIN [RUNEHOLD\anne.devries];

-- Creează un utilizator „orfan" (rar, fără mapare la login — dezactivat implicit)
CREATE USER some_user WITHOUT LOGIN;

Numele utilizatorului din interiorul bazei de date nu trebuie să coincidă cu numele autentificării, deși de obicei coincide pentru claritate.

Schemele și schemele implicite

Fiecare utilizator are o schemă implicită — schema în care SQL Server se uită când scrii SELECT * FROM Customer fără un prefix de schemă.

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

Acum anne_devries, scriind SELECT * FROM Customer, găsește Sales.Customer. Fără implicit, ar căuta în dbo.Customer, n-ar găsi nimic și ar eșua.

Pentru conturile de serviciu ale aplicațiilor, dbo ca schemă implicită e în regulă. Pentru oameni, setarea schemei în care lucrează cel mai des e o îmbunătățire frumoasă a calității vieții.

Roluri la nivel de server și de bază de date

Rolurile grupează permisiunile, ca să acorzi „un rol”, nu „28 de permisiuni individuale”.

Roluri fixe la nivel de server

  • sysadmin — zeu. Poate face orice. Acordă cu zgârcenie, ideal nimănui în afară de DBA-ii de urgență.
  • serveradmin — majoritatea lucrurilor la nivel de server.
  • securityadmin — gestionează autentificări și permisiuni (dar se poate escalada singur; la fel de periculos ca sysadmin).
  • dbcreator — creează și restaurează baze de date.
  • processadmin — KILL pe sesiuni.
  • public — fiecare login face parte din el, nu se poate scoate. Acționează ca grupul „permisiuni implicite”.

Folosește-le cu zgârcenie. Pentru dezvoltatori: de obicei nimic la nivel de server (sau dbcreator pentru dev local). Pentru conturi de serviciu: nimic la nivel de server, decât dacă chiar e nevoie.

Roluri fixe la nivel de bază de date

  • db_owner — zeu în interiorul bazei de date.
  • db_datareader — SELECT pe tot.
  • db_datawriter — INSERT/UPDATE/DELETE pe tot.
  • db_ddladmin — rulează DDL.
  • db_backupoperator — BACKUP DATABASE / LOG.
  • db_securityadmin — gestionează permisiuni în BD.
  • public — din nou, grupul implicit.

Roluri personalizate

Bune practici:

USE Runehold;

-- Creează un rol pentru utilizatori de raportare doar-citire
CREATE ROLE ReadOnly_Reporting;
GRANT SELECT ON SCHEMA::Reporting TO ReadOnly_Reporting;
GRANT SELECT ON SCHEMA::Sales      TO ReadOnly_Reporting;

-- Adaugă un utilizator
ALTER ROLE ReadOnly_Reporting ADD MEMBER anne_devries;

-- Creează un rol pentru aplicație
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;

Rolurile personalizate, grupate în jurul rolurilor reale din echipă/aplicație, păstrează permisiunile gestionabile. Nu acorda niciodată direct utilizatorilor; întotdeauna prin roluri. Chiar dacă e un rol cu un singur membru azi.

Capcana rolului public

Fiecare utilizator e automat membru public în master și în fiecare bază de date utilizator. E grupul „implicit”.

Problemă: public în master are o acordare implicită surprinzătoare: GUEST avea acces la baza de date guest, iar public are acces la multe vizualizări de metadate, plus niște vizualizări de catalog. Un utilizator intern ostil, fără nimic acordat explicit, poate totuși enumera baze de date, unele nume de obiecte și metadate la nivel de serviciu.

În firmele cu securitate strictă, întărești public revocând tot ce i s-a acordat în master. Atenție; unele aplicații depind de accesul la metadate. Testează mai întâi într-un mediu de staging.

Pentru majoritatea firmelor regula e mai simplă: nu acorda nimic lui public pe bazele de date utilizator. Orice permisiune ai acorda lui public e probabil mai bine acordată unui rol specific.

Permisiuni: GRANT, DENY, REVOKE

-- Acordă o permisiune
GRANT SELECT ON SCHEMA::Sales TO ReadOnly_Reporting;

-- Revocă (șterge o acordare specifică, nu un deny global)
REVOKE SELECT ON SCHEMA::Sales TO ReadOnly_Reporting;

-- Deny (blochează explicit, învinge orice grant)
DENY SELECT ON Sales.Customer TO some_role;
  • GRANT adaugă permisiune.
  • REVOKE șterge o acordare specifică.
  • DENY blochează explicit și învinge orice grant. Un utilizator cu SELECT acordat prin apartenența la rol și cu DENY pe același obiect primește DENY.

Folosește DENY cu zgârcenie — e eficient, dar derutant. Majoritatea tiparelor de acces ar trebui construite doar cu GRANT.

Privilegiul minim

Regulă: fiecare cont are exact permisiunile de care are nevoie pentru a-și face treaba. Nu mai mult.

Conturi de serviciu pentru aplicații:

  • Un rol care poate citi, insera, actualiza, șterge în propriile scheme.
  • EXECUTE pe propriile proceduri stocate.
  • Fără DDL. Fără db_owner. Fără db_ddladmin.

Conturi umane:

  • Acces de citire la ce trebuie să vadă.
  • Execute pe procedurile de raport.
  • Acces de scriere strict controlat, dacă e nevoie.

DBA-ii:

  • db_owner pe bazele lor de date. sysadmin pentru una sau două persoane care chiar au nevoie. Toți ceilalți primesc mai puțin.

Auditează asta periodic. Interoghează ce permisiuni are fiecare utilizator:

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')     -- sare peste roluri și application roles pentru claritate
ORDER BY pr.name, p.class_desc;

Rulează asta o dată pe trimestru. Șterge orice e neașteptat.

Baze de date conținute

O opțiune mai nouă (din SQL Server 2012) o reprezintă bazele de date conținute (contained databases) — baze de date ai căror utilizatori există în întregime în interiorul lor, fără a fi mapați la autentificări la nivel de server. Utile pentru Azure SQL Database (care e inerent contained) și pentru portabilitate: poți muta o bază de date contained pe alt server fără să recreezi autentificările.

Sintaxă:

-- Necesită sp_configure 'contained database authentication' = 1 la nivel de server
ALTER DATABASE Runehold SET CONTAINMENT = PARTIAL;

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

Fără autentificare la nivel de server. Utilizatorul se autentifică direct la baza de date.

Frumos pentru izolare. Tooling mai puțin matur în jurul auditului. Alege în funcție de uneltele tale de monitorizare/guvernanță.

Note specifice GDPR

Pentru o companie europeană precum Runehold, câteva lucruri sunt non-negociabile:

  1. Datele cu caracter personal trebuie criptate la repaus. TDE (Transparent Data Encryption), Always Encrypted (pentru coloane specifice) sau criptarea sistemului de fișiere. Practică standard acum.
  2. Conexiuni criptate. Forțează TLS între aplicații și bază. Encrypt=True în șirurile de conexiune.
  3. Logarea accesului. Fiecare citire de date personale de către un utilizator uman ar trebui logată. SQL Server Audit e unealta; un SIEM extern (Splunk, Datadog) citește și arhivează.
  4. Dreptul la ștergere — vezi lecția 31 despre backup-uri. Datele live șterse imediat; backup-urile expiră într-o fereastră definită.
  5. Portabilitatea datelor — trebuie să poți exporta datele unui client la cerere. Construiește interogarea de export acum; nu încerca să-ți dai seama când sună juriștii.

Niciuna dintre acestea nu schimbă modelul de securitate de bază, dar adaugă constrângeri. Pentru Runehold în mod specific, DPO-ul (Data Protection Officer) dictează care coloane se califică drept „date personale” și ce nivel de criptare primește fiecare. Consultă-l; nu inventa politici de capul tău.

Rulează asta pe propria mașină

USE Runehold;
GO

-- 1. Creează un rol read-only, adaugă permisiuni
CREATE ROLE RolesDemo_ReadOnly;
GRANT SELECT ON SCHEMA::Sales TO RolesDemo_ReadOnly;

-- 2. Creează un utilizator (fără login, doar pentru demo)
CREATE USER RolesDemo_User WITHOUT LOGIN;
ALTER ROLE RolesDemo_ReadOnly ADD MEMBER RolesDemo_User;

-- 3. Testează ce poate face utilizatorul
EXECUTE AS USER = 'RolesDemo_User';
SELECT TOP 1 * FROM Sales.Customer;  -- merge
SELECT TOP 1 * FROM Sales.Customer;
-- INSERT INTO Sales.Customer (Name, Country) VALUES ('X', 'NL');  -- refuzat
REVERT;

-- 4. Audit: ce permisiuni are acest utilizator?
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. Curățenie
ALTER ROLE RolesDemo_ReadOnly DROP MEMBER RolesDemo_User;
DROP USER RolesDemo_User;
DROP ROLE RolesDemo_ReadOnly;

Lecția următoare: tempdb — terenul de joacă partajat pe care îl folosește fiecare sesiune și sursa pentru mai multe probleme misterioase de performanță decât orice altă resursă.

Caută