はじめに
こんにちは！今回は、SQLのDCL（Data Control Language）についてまとめます。DCLは、データベースにおけるアクセス制御（権限付与・剥奪）や、トランザクション管理（開始や終了、コミットやロールバックなど）を扱う大切な要素です。
本文
1. SQL-DCLとは？
SQL-DCLは、データベースの操作権限を設定し、トランザクションを制御するためのSQL文です。代表的なものとして以下が挙げられます。
- GRANT / REVOKE: アクセス権限の付与や取り消し
- START TRANSACTION / COMMIT / ROLLBACK: トランザクションの開始・確定・取り消し
これらを使いこなすことで、安全かつ柔軟にデータベースの利用者やアプリケーションを管理できます。
2. アクセス権限の管理
2.1 GRANT文
データベースやテーブル、ビューなどに対して、特定のユーザやロール（役割）に対してどの程度の操作を許可するかを設定します。許可される動作は以下の例があります。
- SELECT: 表示（データの参照）
- INSERT: 挿入
- UPDATE: 更新
- DELETE: 削除
- REFERENCES: 外部キーとして参照
- USAGE: ドメインや文字コード等の利用
- ALL PRIVILEGES: すべての権限（一括指定）
- WITH GRANT OPTION : 付与されたユーザが、さらに他のユーザへ同権限を再付与できるようにする
GRANTの基本構文
GRANT { ALL [PRIVILEGES] }
| SELECT
| INSERT [(列リスト)]
| DELETE
| UPDATE [(列リスト)]
| REFERENCES [(列リスト)]
| USAGE
ON { TABLE 表名
| VIEW ビュー名
| DOMAIN ドメイン名
| COLLATION 参照順序名
| CHARCTER SET 文字コード設定名
| TRANSLATION 置換名
}
TO { ユーザ名 | PUBLIC }
[WITH GRANT OPTION];
使用例
-- 「商品」テーブルに対して、「うさぎ」ユーザに全権限を付与
GRANT ALL PRIVILEGES ON 商品 TO うさぎ;
-- 社員テーブルに対してSELECT/UPDATE権限をユーザU1に付与
GRANT SELECT, UPDATE ON 社員 TO U1;
2.2 REVOKE文
一度付与した権限を取り消す場合に使用します。GRANTと対になる構文です。
-- うさぎから全権限を取り消す
REVOKE ALL PRIVILEGES ON 商品 FROM うさぎ;
2.3 ユーザ・ロールと権限
- ユーザ: 個別のアカウント
- ロール（ROLE）: 複数ユーザに共通の権限をまとめて付与する仕組み
ロールに権限をGRANTし、ロールをユーザにGRANTすることで、多人数の管理が簡単になります。
3. トランザクション管理
データベースにおけるトランザクションとは、まとまりのある一連の処理を指し、全処理が成功するとデータを確定（コミット）、途中で失敗するとすべてキャンセル（ロールバック）されることでデータの整合性を保ちます。
3.1 トランザクション開始
SQL標準では、START TRANSACTION を用いて明示的に開始できます（DBMSによっては BEGIN など別の構文を使う場合もあります）。
START TRANSACTION
[{ READ ONLY | READ WRITE }]
[ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }]
[DIAGNOSTIC SIZE <整数値>];
- READ ONLY: 更新しない（参照専用）
- READ WRITE: 通常の更新可能モード
- ISOLATION LEVEL: トランザクション分離レベル（下記参照）
3.2 分離レベル（Isolation Level）
複数のトランザクションが同時実行される際、どのタイミングのデータが見えるかをコントロールする仕組み。
-
READ UNCOMMITTED
- 未コミットデータ（ダーティリード）を読み取る
- 整合性は低いが、最もパフォーマンスが高い
- 例: 進行中の取引状況をリアルタイムで確認したい場合に適している
-
READ COMMITTED
- コミット済みデータのみ読み取る
- Oracle、PostgreSQL、SQL Serverでデフォルトのトランザクション分離レベル
- 例: 銀行で残高をチェックした際、別のトランザクションがコミットした後、もう一度残高を確認すると値が変わっている可能性がある
-
REPEATABLE READ
- 1回SELECTしたデータはトランザクション内で同じ内容を再度読み取れるよう保証
- MySQLのデフォルトのトランザクション分離レベル
- 例: 商品の在庫数をチェックして購入処理を行う際、他のトランザクションがその商品を購入しても、在庫数が変わらない
-
SERIALIZABLE
- 最も厳格なレベル
- 複数トランザクションが順番に実行したのと同じ結果を保証
- 整合性が高いが、デッドロックのリスクも高く、パフォーマンスが低下する
| 分離レベル | ダーティリード | ファジーリード | ファントムリード |
|---|---|---|---|
| READ UNCOMMITTED | ○ | ○ | ○ |
| READ COMMITTED | × | ○ | ○ |
| REPEATABLE READ | × | × | ○ |
| SERIALIZABLE | × | × | × |
※○: 発生する
- ダーティリード（Dirty Read）
- 他のトランザクションでコミットされていないデータや不完全なデータ、計算途中のデータを読み取ってしまう現象
- ファジーリード/ノンリピータブルリード（Fuzzy Read / Non-Repeatable Read）
- 同じトランザクション中でも同じデータを読み込むたびに値が変わってしまう現象
- ファントムリード（Phantom Read）
- 並行して動作する他のトランザクションが追加したり削除したデータが途中で見えてしまう現象
3.3 COMMITとROLLBACK
- COMMIT : トランザクション内容をデータベースに確定し終了
- ROLLBACK : トランザクション内容をすべて取り消し（開始前の状態に戻す）
START TRANSACTION;
-- いくつかのINSERT, UPDATE, DELETEを実行
UPDATE 社員
SET 氏名 = '山田太郎(改)'
WHERE 社員番号 = 1001;
-- 正常に終わればコミット
COMMIT;
-- あるいは失敗や条件不成立なら
ROLLBACK;
まとめ
SQL-DCL（Data Control Language）は、データベースのセキュリティやトランザクション管理に深く関わる言語です。
-
GRANT/REVOKE による権限設定
-
SELECT、INSERT、UPDATEなどの操作権限をユーザやロールに与えたり剥奪したりします -
WITH GRANT OPTIONで再配布権限も付与できます
-
-
トランザクション管理
-
START TRANSACTIONで開始し、COMMIT（確定）やROLLBACK（取消）で結果を反映または取り消します - 分離レベルを適切に設定することで同時実行時の整合性を確保します
-
これらを利用することで、多人数・多プロセスが同時にデータを操作しても安全かつ正確にデータを管理できるようになります。システムの要件に合わせて、権限設計やトランザクション分離レベルをうまく組み合わせ、堅牢なデータベース運用を実現しましょう。