MariaDB講座 / ユーザーを作成し、必要な権限だけを付与する
MariaDBのアカウントはユーザー名とHostの組で識別し、GRANTで範囲を定めて権限を与えます。管理とアプリケーションの接続を分け、制限した本人の接続で、許可と拒否の両方を確認します。
身元・接続元・権限範囲を分ける
同じ名前でもlocalhostと127.0.0.1は別の定義になり得ます。接続方式や名前解決も一致に関係し、CURRENT_USER()が権限判定用アカウントです。常用の読取をrootで行うと最小権限を確認しにくくなります。
範囲は全体*.*、DB shopcourse.*、表shopcourse.c43_products、列UPDATE(stock)などで違います。接続元の切り分けとPythonの制限接続も参照できます。仮パスワードは固有の値に変え、本物を命令引数や公開ログへ残さず、入力プロンプトや適切に保護した設定ファイルを使います。
データ準備と管理者操作を分ける
course_userでshopcourseと未使用のc43_表名を確認し、完全な実習例を実行します。同名表があれば先に内容と定義を保存して調べます。
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);
顧客名と都市、商品名は原文の韓国語サンプルを保っています。商品1～4はノート、ペン、マグカップ、ライト、顧客1・3はソウル、2は釜山、4は大田です。UTF-8で保存・接続し、IDと数値で結果を照合します。
CREATE USERとGRANTは必要な権限を持つ別の管理者接続で行います。shop_reader43が既存なら用途を確認し、無断で上書き・削除せず別の実習名を選びます。
一つの表のSELECTだけを許可する
管理者で、仮パスワードを変えて実行します。
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';
c43_productsの読取だけで、顧客・注文の読取やINSERT、UPDATE、DDLは与えていません。通常は追加のFLUSH PRIVILEGESは不要で、権限表を直接編集しません。アカウント定義の出力にも秘密が含まれ得るため公開範囲を確認します。
制限された接続で両方の結果を試す
端末で新しい接続を作ります。course_userのまま試して判断しません。
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;
次の二文は制限接続で一つずつ実行し、どちらも拒否を確認します。
SELECT * FROM c43_customers; UPDATE c43_products SET stock=stock WHERE product_id=1;
拒否されたからとALLを与えず、必要な作業かを確認します。ロールを使う場合は直接権限と有効なロールも読みます。
取消後の新接続とアカウントの寿命
管理者で読取を取り消し、新しい制限接続で拒否を確かめます。
REVOKE SELECT ON shopcourse.c43_products FROM 'shop_reader43'@'127.0.0.1'; SHOW GRANTS FOR 'shop_reader43'@'127.0.0.1';
DB権限を失うと接続時のshopcourse選択自体が拒否される場合があります。その場合はDBを選ばず接続して完全修飾表名を試します。既存接続への反映は範囲で違い得るため、一つの開いた接続だけで判断しません。USAGEはアカウントがあるだけで実権限がない状態を示すことがあります。
DROP USERは依存するDEFINERオブジェクトや保存データまで片付ける機能ではありません。接続、VIEW、ルーチンを確認し、実習専用と確かめたアカウントだけを、接続を閉じて管理者が削除します。
DROP USER 'shop_reader43'@'127.0.0.1';
表も必要データを保存し、この記事のものだけを子から親へ削除します。DROP TABLEは通常のROLLBACKでは戻せません。
DROP TABLE shopcourse.c43_items; DROP TABLE shopcourse.c43_orders; DROP TABLE shopcourse.c43_products; DROP TABLE shopcourse.c43_customers;
