SQL Academy · Lezione

Ruoli e privilegi

Gestisca gli accessi con GRANT e REVOKE

Ruoli e privilegi è una lezione SQL Academy gratuita su CoddyKit. Questa è la lezione 1 di 4. Puoi leggere la lezione completa qui gratuitamente — poi esercitati direttamente nel browser con un editor di codice integrato e un tutor IA disponibile 24/7. Fa parte del percorso di apprendimento SQL Academy, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso SQL Academy include 4 lezioni in totale.

Cosa sono ruoli e privilegi

In SQL, i ruoli sono gruppi denominati di privilegi che possono essere assegnati agli utenti. I privilegi controllano quali azioni un utente o un ruolo può eseguire sugli oggetti del database, come tabelle, viste e funzioni.

Anziché concedere autorizzazioni a ogni utente individualmente, si crea un ruolo con i privilegi necessari e lo si assegna contemporaneamente a molti utenti. In questo modo, la gestione del controllo degli accessi su larga scala diventa molto più semplice.

Creazione di un ruolo

Utilizzi CREATE ROLE per definire un nuovo ruolo in PostgreSQL. Un ruolo può rappresentare un singolo utente o un gruppo di utenti, a seconda di come lo si configura.

Per impostazione predefinita, i ruoli vengono creati senza alcun privilegio: è necessario concedere esplicitamente loro l'accesso agli oggetti.

CREATE ROLE readonly_user;
CREATE ROLE app_writer;
CREATE ROLE db_admin;

Concessione dei privilegi sulle tabelle

L'istruzione GRANT concede a un ruolo o a un utente l'autorizzazione a eseguire operazioni specifiche su un oggetto del database. I privilegi comuni a livello di tabella includono SELECT, INSERT, UPDATE e DELETE.

È possibile concedere un singolo privilegio o più privilegi contemporaneamente, separandoli con virgole.

-- Grant SELECT only (read-only role)
GRANT SELECT ON employees TO readonly_user;

-- Grant multiple privileges
GRANT SELECT, INSERT, UPDATE ON orders TO app_writer;

Concessione di tutti i privilegi

Se un ruolo necessita di accesso completo a una tabella, è possibile usare GRANT ALL PRIVILEGES invece di elencare singolarmente ogni autorizzazione. In questo modo vengono concessi tutti i privilegi applicabili sull'oggetto specificato.

Faccia attenzione a ALL PRIVILEGES: lo conceda solo ai ruoli che richiedono realmente il controllo completo dell'oggetto.

-- Grant full access to db_admin on a table
GRANT ALL PRIVILEGES ON employees TO db_admin;

-- Or the shorthand form
GRANT ALL ON orders TO db_admin;

Revoca dei privilegi

L'istruzione REVOKE rimuove i privilegi precedentemente concessi a un ruolo o a un utente. È il modo per restringere l'accesso quando cambiano i requisiti o quando un ruolo non necessita più di determinate autorizzazioni.

Dopo un REVOKE, il ruolo interessato perde immediatamente il privilegio specificato sull'oggetto indicato.

-- Remove UPDATE access from app_writer
REVOKE UPDATE ON orders FROM app_writer;

-- Remove all privileges from a role
REVOKE ALL PRIVILEGES ON employees FROM readonly_user;

Assegnazione di un ruolo a un utente

In PostgreSQL, anche gli utenti sono ruoli: un ruolo con l'attributo LOGIN può connettersi al database. Si assegna un ruolo di gruppo a un ruolo di accesso tramite GRANT role TO user.

Una volta assegnato, l'utente eredita tutti i privilegi associati a quel ruolo, rendendo semplice gestire le autorizzazioni per molti utenti contemporaneamente.

-- Create a login user
CREATE ROLE alice WITH LOGIN PASSWORD 'secret123';

-- Assign the readonly role to alice
GRANT readonly_user TO alice;

-- Alice can now SELECT on tables granted to readonly_user

Revoca di un ruolo a un utente

Per rimuovere l'assegnazione di un ruolo a un utente, utilizzi REVOKE role FROM user. Dopodiché, l'utente non eredita più i privilegi associati a quel ruolo.

È utile quando un dipendente cambia responsabilità o lascia l'organizzazione: è possibile revocare il suo ruolo senza modificare le definizioni dei privilegi del ruolo stesso.

-- Remove the readonly role from alice
REVOKE readonly_user FROM alice;

-- Alice no longer has SELECT on tables via that role

Privilegi a livello di schema

Prima che un ruolo possa accedere agli oggetti contenuti in uno schema, deve disporre del privilegio USAGE su tale schema. Senza di esso, anche un ruolo con SELECT su una tabella specifica non può leggere i dati, perché non può risolvere il percorso dello schema.

Conceda sempre USAGE sullo schema insieme ai privilegi a livello di oggetto.

-- Allow readonly_user to see inside the public schema
GRANT USAGE ON SCHEMA public TO readonly_user;

-- Then grant table-level privilege
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

Privilegi predefiniti per gli oggetti futuri

Quando vengono create nuove tabelle in uno schema, i ruoli esistenti non ottengono automaticamente l'accesso a esse. Utilizzi ALTER DEFAULT PRIVILEGES per fare in modo che gli oggetti futuri creati da un ruolo specifico siano automaticamente accessibili a un altro ruolo.

In questo modo si evita un problema comune: una tabella appena creata rimane invisibile ai ruoli dell'applicazione finché qualcuno non si ricorda di eseguire manualmente GRANT.

-- Future tables created by the current user will be SELECTable by readonly_user
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO readonly_user;

-- Future tables will allow INSERT/UPDATE/DELETE for app_writer
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_writer;

La WITH GRANT OPTION

Per impostazione predefinita, un ruolo che riceve un privilegio non può trasferirlo ad altri ruoli. L'aggiunta di WITH GRANT OPTION consente al destinatario di concedere a sua volta quel privilegio ad altri.

Utilizzi questa opzione con cautela: il ruolo diventa un punto di delega affidabile per il controllo degli accessi, il che può complicare le verifiche se l'opzione viene usata eccessivamente.

-- app_writer can now grant SELECT on orders to other roles
GRANT SELECT ON orders TO app_writer WITH GRANT OPTION;

-- app_writer can then do:
-- GRANT SELECT ON orders TO reporting_role;

Visualizzazione dei privilegi concessi

PostgreSQL memorizza le informazioni sui privilegi nelle viste dei cataloghi di sistema. È possibile interrogare information_schema.role_table_grants per vedere quali ruoli hanno ricevuto quali privilegi su quali tabelle.

Il comando con barra rovesciata \dp tablename in psql mostra inoltre l'elenco dei privilegi di accesso per una tabella in un formato compatto.

-- List all table-level grants in the current database
SELECT grantee, table_schema, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE table_schema = 'public'
ORDER BY grantee, table_name;

Verifica rapida

Verifichi la Sua comprensione di ruoli e privilegi in SQL.

Riepilogo: ruoli e privilegi

In questa lezione ha imparato come SQL gestisce il controllo degli accessi tramite ruoli e privilegi:

CREATE ROLE definisce un nuovo ruolo o account utente
GRANT assegna privilegi (SELECT, INSERT, UPDATE, DELETE, ALL) sugli oggetti a un ruolo
REVOKE rimuove tali privilegi
GRANT role TO user assegna un ruolo di gruppo a un utente di accesso, che ne eredita i privilegi
I ruoli necessitano di USAGE sullo schema prima di poter accedere agli oggetti che contiene
ALTER DEFAULT PRIVILEGES garantisce che gli oggetti futuri siano automaticamente accessibili
WITH GRANT OPTION consente al destinatario di delegare ulteriormente il privilegio

Mantenere i privilegi al minimo e basarli sui ruoli è un principio fondamentale della progettazione sicura dei database: conceda solo ciò che è necessario e revochi tempestivamente ciò che non serve più.

Domande Frequenti

La lezione «Ruoli e privilegi» è gratuita?

Sì — il testo completo di «Ruoli e privilegi» è gratuito qui sul web. Per esercitarvi in modo interattivo (un editor di codice integrato e un tutor IA 24/7) e sbloccare il resto del corso SQL Academy, passa a CoddyKit PRO. Il corso SQL Academy include 4 lezioni in totale.

Cosa imparerò in «Ruoli e privilegi»?

Gestisca gli accessi con GRANT e REVOKE Eserciti SQL Academy con codice pratico che esegui direttamente nel browser, e un tutor IA 24/7 risponde alle tue domande mentre lavori sulla lezione.

Ho bisogno di esperienza per iniziare SQL Academy?

Non è richiesta alcuna esperienza precedente. SQL Academy su CoddyKit è strutturato per principianti e studenti avanzati, quindi puoi iniziare da qui o dall'inizio e procedere al tuo ritmo. Questa è la lezione 1 di 4.

Quanto tempo richiede la lezione «Ruoli e privilegi»?

La maggior parte delle lezioni CoddyKit richiede circa 5–10 minuti. Ogni lezione è breve e interattiva, quindi fai progressi costanti e riprendi esattamente da dove hai lasciato su web e app.

Posso scrivere ed eseguire codice in questa lezione SQL Academy?

Sì. Ogni lezione SQL Academy include un editor di codice integrato, quindi scrivi ed esegui codice reale direttamente nel tuo browser e ricevi feedback istantaneo dall'IA — nessuna configurazione locale necessaria.

Tutte le lezioni di questo corso

← Torna a SQL Academy