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;




