Corso MariaDB / Creare utenti e concedere soltanto i privilegi necessari

Un account MariaDB è identificato dalla combinazione di nome utente e host, mentre GRANT assegna privilegi a un ambito preciso. Separare connessioni amministrative e applicative e verificare operazioni consentite e rifiutate usando davvero l'account limitato.

Distinguere identità, origine della connessione e ambito dei privilegi

Lo stesso nome utente su localhost e 127.0.0.1 può riferirsi a definizioni di account diverse. Metodo di connessione e risoluzione dei nomi influenzano la corrispondenza; CURRENT_USER() mostra l'account usato per verificare i privilegi. Usare root per le normali letture applicative ostacola l'applicazione dei soli privilegi necessari.

Ambito Esempio Uso
Globale *.* Amministrazione dell'intero server
Database shopcourse.* Lavoro in quel database
Tabella shopcourse.c43_products Lettura dei prodotti di questo esercizio
Colonna UPDATE(stock) Modifica della sola colonna specificata

Sostituire la password dimostrativa con un valore univoco. Usare il prompt della password o un file di opzioni con accesso limitato, evitando segreti reali negli argomenti dei comandi o nei documenti pubblicati. I riferimenti MariaDB di CREATE USER e GRANT descrivono identità e concessione dei privilegi.

Separare preparazione dei dati e amministrazione degli account

Collegarsi al server di esercitazione come course_user e selezionare shopcourse. La guida al client e ai file SQL UTF-8 descrive salvataggio ed esecuzione con SOURCE C:/mariadb-lab/lesson.sql;. Sostituire il percorso con quello reale ed eseguire SQL nel client MariaDB.

Prima di creare c43_customers, c43_products, c43_orders e c43_items, controllare SELECT VERSION(), DATABASE(); e SHOW TABLES;. Se gli oggetti esistono, esaminarne definizioni e righe. Usare soltanto tabelle di prova eliminabili e conservare quelle applicative. Eseguire questa preparazione completa tramite l'account di studio con i privilegi necessari sul database.

USE shopcourse;
CREATE TABLE c43_customers (
 customer_id INT NOT NULL PRIMARY KEY,
 customer_name VARCHAR(50) NOT NULL,
 city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c43_products (
 product_id INT NOT NULL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(30) NOT NULL,
 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
 stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c43_orders (
 order_id INT NOT NULL PRIMARY KEY,
 customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL,
 shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
 CONSTRAINT fk_c43_order_customer FOREIGN KEY (customer_id)
  REFERENCES c43_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c43_items (
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 quantity INT NOT NULL CHECK (quantity > 0),
 unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
 PRIMARY KEY (order_id, product_id),
 CONSTRAINT fk_c43_item_order FOREIGN KEY (order_id)
  REFERENCES c43_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c43_item_product FOREIGN KEY (product_id)
  REFERENCES c43_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c43_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c43_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c43_orders
 (order_id,customer_id,ordered_at,status,shipping_fee) VALUES
 (101,1,'2026-01-10 10:00:00','paid',1000),
 (102,1,'2026-01-20 11:00:00','paid',1000),
 (103,2,'2026-02-05 12:00:00','paid',1000),
 (104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c43_items VALUES
 (101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
 (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);

Il codice conserva nomi e città coreani della fonte. I prodotti 1–4 sono quaderno, penna, tazza e lampada. I clienti 1 e 3 appartengono a Seoul, il cliente 2 a Busan e il cliente 4 a Daejeon. Mantenere i testi UTF-8 e confrontare gli ID.

CREATE USER e GRANT richiedono una connessione amministrativa con privilegi adeguati, distinta dall'account che prepara i dati. Se shop_reader43 esiste già, scegliere un altro nome di prova e aggiornare tutti i riferimenti: non sovrascrivere né eliminare account esistenti. Conservare la connessione amministrativa per revocare i privilegi e rimuovere il nuovo account alla fine.

Concedere SELECT soltanto sulla tabella prodotti

Eseguire il blocco seguente come amministratore dopo aver sostituito la password dimostrativa. Trattare le definizioni e le concessioni dell'account come informazioni che possono includere dati di autenticazione: non pubblicare segreti o hash di autenticazione.

CREATE USER 'shop_reader43'@'127.0.0.1' IDENTIFIED BY 'Replace_With_Unique_Lab_Password!';
GRANT SELECT ON shopcourse.c43_products TO 'shop_reader43'@'127.0.0.1';
SHOW GRANTS FOR 'shop_reader43'@'127.0.0.1';

L'account può leggere c43_products, ma non riceve accesso a clienti o ordini, INSERT, UPDATE o modifiche strutturali. Dopo CREATE USER e GRANT non è normalmente necessario FLUSH PRIVILEGES. Usare le istruzioni di gestione degli account supportate invece di modificare direttamente le tabelle dei privilegi.

La concessione sulla tabella è diversa da shopcourse.* o *.*. Verificare che host, database e tabella di SHOW GRANTS corrispondano allo scopo. Non aggiungere WITH GRANT OPTION a un lettore che non deve trasferire privilegi ad altri account.

Aprire la connessione limitata e provare successi e rifiuti

Eseguire il comando client seguente nel terminale del sistema operativo e inserire la nuova password al prompt. Adeguare la porta se il server di prova non usa 3306. Non continuare nella sessione course_user per trarre conclusioni sui privilegi del lettore.

mariadb --protocol=tcp --host=127.0.0.1 --port=3306 --user=shop_reader43 --password --default-character-set=utf8mb4 shopcourse
SELECT USER(),CURRENT_USER();
SELECT product_id,product_name FROM c43_products ORDER BY product_id;

I quattro prodotti devono risultare leggibili. Confrontare CURRENT_USER() con l'account creato per verificare l'identità effettiva della prova. USER() descrive utente e origine della connessione, mentre CURRENT_USER() identifica il contesto usato nel controllo dei privilegi.

Eseguire separatamente ciascuna istruzione seguente nella sessione limitata: entrambe devono essere rifiutate.

SELECT * FROM c43_customers;
UPDATE c43_products SET stock=stock WHERE product_id=1;

Un UPDATE che assegna a stock il suo stesso valore richiede comunque il privilegio UPDATE e non aggira il limite del lettore. Non concedere subito ALL PRIVILEGES davanti a un rifiuto: stabilire se l'esigenza richiede lettura o scrittura. Se si usano ruoli, verificare anche i privilegi dei ruoli attivi oltre alle concessioni dirette.

Verificare la revoca e valutare il ciclo di vita dell'account

Come amministratore revocare la lettura della tabella, poi verificare il rifiuto con una nuova connessione limitata.

REVOKE SELECT ON shopcourse.c43_products FROM 'shop_reader43'@'127.0.0.1';
SHOW GRANTS FOR 'shop_reader43'@'127.0.0.1';

Dopo la revoca dell'unico privilegio sul database, scegliere shopcourse all'apertura può essere rifiutato immediatamente. In tal caso collegarsi senza il nome del database predefinito alla fine del comando e provare SELECT * FROM shopcourse.c43_products;. L'autenticazione può ancora riuscire mentre la lettura della tabella viene rifiutata.

L'aggiornamento dei privilegi nelle sessioni aperte può dipendere dall'ambito: non affidarsi a un'unica vecchia sessione. USAGE può indicare un account esistente senza privilegi sostanziali a quell'ambito. DROP USER non elimina automaticamente tutti i dati o gli oggetti che dipendono dall'account come DEFINER.

Prima di dismettere un account, esaminare connessioni applicative, viste e routine collegate. La guida a VIEW spiega perché il contesto di sicurezza della definizione influisce sui privilegi necessari.

Eliminare soltanto account e tabelle nuovi dell'esercizio

Accertarsi che shop_reader43 sia stato creato esclusivamente per questa prova, chiudere le sue connessioni e rimuoverlo come amministratore. Gli altri account applicativi non appartengono alla pulizia.

DROP USER 'shop_reader43'@'127.0.0.1';

Prima di ripetere, controllare database selezionato, definizioni effettive e righe, salvando ciò che va conservato. Eliminare soltanto gli oggetti c43_ di questo articolo, partendo dalle tabelle figlie. DROP TABLE elimina struttura e dati e non viene annullato da un normale ROLLBACK: separare la pulizia dalle prove sulle transazioni.

DROP TABLE shopcourse.c43_items;
DROP TABLE shopcourse.c43_orders;
DROP TABLE shopcourse.c43_products;
DROP TABLE shopcourse.c43_customers;