タイトル: ユーザー関連
SEOタイトル: SQL ユーザー管理 DDL 完全ガイド | CREATE USER / GRANT / REVOKE と各 DB の差
| この記事の要点 |
|
SQL のユーザー管理 DDL の概要
RDBMS には「誰が接続して、何ができるか」を管理する仕組みがあり、ユーザー（アカウント）の作成・権限付与・削除はDDL (Data Definition Language) として SQL で記述します。基本となるコマンドは以下の 5 つです。
CREATE USER: ユーザー作成ALTER USER: パスワード変更、属性変更DROP USER: ユーザー削除GRANT: 権限付与REVOKE: 権限剥奪
ただし細かい構文は DB ごとに大きく異なるため、本記事では主要 4 DB（MySQL / PostgreSQL / Oracle / SQL Server）の差を整理します。
MySQL / MariaDB のユーザー管理
ユーザーの作成と削除
-- ユーザー作成: 'ユーザー名'@'接続元ホスト' が識別子
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'StrongP@ss1';
CREATE USER 'app_user'@'%' IDENTIFIED BY 'StrongP@ss1'; -- どこからでも
CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'StrongP@ss1'; -- 特定セグメント
-- 一覧確認
SELECT user, host FROM mysql.user;
-- パスワード変更
ALTER USER 'app_user'@'localhost' IDENTIFIED BY 'NewP@ss2';
-- ユーザー削除
DROP USER 'app_user'@'localhost';権限付与・剥奪
-- 全 DB の全テーブルに全権限 (管理者)
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;
-- 特定 DB の全テーブル
GRANT ALL PRIVILEGES ON shopdb.* TO 'app_user'@'%';
-- 特定テーブルの SELECT / INSERT のみ
GRANT SELECT, INSERT ON shopdb.orders TO 'readonly'@'%';
-- カラム単位の SELECT
GRANT SELECT (id, name) ON shopdb.users TO 'limited'@'%';
-- 反映
FLUSH PRIVILEGES; -- ALTER 系では自動で反映、念のため
-- 権限確認
SHOW GRANTS FOR 'app_user'@'%';
-- 権限剥奪
REVOKE INSERT ON shopdb.orders FROM 'readonly'@'%';認証プラグイン (MySQL 8.0)
MySQL 8.0 では既定の認証プラグインが caching_sha2_password に変わり、旧クライアントが接続できないケースが頻発しています。
-- プラグイン指定の作成
CREATE USER 'old_app'@'%'
IDENTIFIED WITH mysql_native_password BY 'pw'; -- 旧互換
CREATE USER 'new_app'@'%'
IDENTIFIED WITH caching_sha2_password BY 'pw'; -- 推奨（8.0 既定）
-- 既存ユーザーの認証方式を切替
ALTER USER 'old_app'@'%'
IDENTIFIED WITH caching_sha2_password BY 'pw';PostgreSQL のユーザー管理
PostgreSQL ではロールがユーザー / グループ両方を表す概念です。CREATE USER は CREATE ROLE ... LOGIN のシンタックスシュガーです。
-- ユーザー（ログイン可能なロール）作成
CREATE USER app_user WITH PASSWORD 'StrongP@ss1';
-- 同義
CREATE ROLE app_user LOGIN PASSWORD 'StrongP@ss1';
-- スーパーユーザー
CREATE ROLE admin SUPERUSER LOGIN PASSWORD 'StrongP@ss1';
-- 一覧確認
\du
SELECT rolname, rolsuper, rolcanlogin FROM pg_roles;
-- パスワード変更
ALTER USER app_user WITH PASSWORD 'NewP@ss2';
-- 接続制限
ALTER USER app_user CONNECTION LIMIT 50;
-- 削除
DROP USER app_user;権限付与
-- DB レベルの権限
GRANT CONNECT ON DATABASE shopdb TO app_user;
-- スキーマレベル
GRANT USAGE ON SCHEMA public TO app_user;
-- テーブルレベル
GRANT SELECT, INSERT ON public.orders TO app_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO app_user;
-- 今後作るテーブルにも自動付与
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO readonly_role;
-- ロールベース（グループロール）
CREATE ROLE readonly_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_role;
GRANT readonly_role TO app_user; -- app_user は readonly_role の権限を継承
-- 権限剥奪
REVOKE INSERT ON public.orders FROM app_user;Oracle のユーザー管理
-- ユーザー作成
CREATE USER app_user IDENTIFIED BY "StrongP@ss1"
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON users;
-- 基本権限の付与（事前定義ロール）
GRANT CONNECT, RESOURCE TO app_user;
-- CONNECT : セッション作成、テーブル作成等
-- RESOURCE : オブジェクト作成権限
-- DBA : ★ 強力すぎるので注意
-- 個別権限
GRANT SELECT, INSERT ON hr.employees TO app_user;
GRANT CREATE TABLE TO app_user;
GRANT CREATE SESSION TO app_user; -- 接続権
-- パスワード変更
ALTER USER app_user IDENTIFIED BY "NewP@ss2";
-- ロック / アンロック
ALTER USER app_user ACCOUNT LOCK;
ALTER USER app_user ACCOUNT UNLOCK;
-- 削除（所有オブジェクトもまとめて削除）
DROP USER app_user CASCADE;
-- 一覧
SELECT username, account_status, created FROM dba_users;SQL Server のユーザー管理
SQL Server は「ログイン (サーバレベル)」と「ユーザー (DB レベル)」の二段階構造です。同じ名前で対応付けるのが一般的ですが、別名にもできます。
-- 1. ログインを作成（master DB で）
USE master;
CREATE LOGIN app_login WITH PASSWORD = 'StrongP@ss1';
-- Windows 認証の場合
CREATE LOGIN [DOMAIN\app_user] FROM WINDOWS;
-- 2. 各 DB でユーザーを作成し、ログインに紐付け
USE shopdb;
CREATE USER app_user FOR LOGIN app_login;
-- 3. 権限付与
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO app_user;
-- 既定のロール
ALTER ROLE db_datareader ADD MEMBER app_user; -- 読み取り
ALTER ROLE db_datawriter ADD MEMBER app_user; -- 書き込み
-- パスワード変更
ALTER LOGIN app_login WITH PASSWORD = 'NewP@ss2';
-- ログイン無効化
ALTER LOGIN app_login DISABLE;
-- 削除（順序に注意）
USE shopdb; DROP USER app_user;
USE master; DROP LOGIN app_login;
-- 一覧
SELECT name, type_desc, is_disabled FROM sys.server_principals;
SELECT name, type_desc FROM sys.database_principals;権限の最小化（最小権限の原則）
セキュリティの観点では、アプリケーションが使う DB ユーザーには業務に必要な最小限の権限のみを与えるのが鉄則です。
| 役割 | 典型的な権限 |
|---|---|
| アプリ DB ユーザー（業務用） | SELECT / INSERT / UPDATE / DELETE のみ。CREATE / DROP は不要 |
| レポート / BI 用 | SELECT のみ |
| バッチ管理者 | + TRUNCATE / TEMP TABLE 作成 |
| マイグレーション用 | CREATE / ALTER / DROP（CI/CD 専用、本番はメンテ時間のみ有効） |
| 監査 / 監視 | 各種ビュー（pg_stat / sys.dm_xxx）の SELECT のみ |
ロール / グループの活用
ユーザーが増えると個別に GRANT するのは破綻します。役割ごとにロールを作り、ロールに権限を付与し、ユーザーをロールに所属させるのが定番です。
-- PostgreSQL の例
CREATE ROLE app_reader;
GRANT CONNECT ON DATABASE shopdb TO app_reader;
GRANT USAGE ON SCHEMA public TO app_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_reader;
CREATE ROLE app_writer;
GRANT app_reader TO app_writer; -- 読み権限を継承
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_writer;
-- ユーザーを所属させる
GRANT app_reader TO bob;
GRANT app_writer TO alice;パスワードポリシー
| DB | 機能 |
|---|---|
| MySQL 8.0 | validate_password コンポーネント、policy=LOW/MEDIUM/STRONG |
| PostgreSQL | passwordcheck contrib、外部認証 (LDAP / Kerberos / SCRAM-SHA-256) |
| Oracle | PROFILE で文字種 / 桁数 / 履歴 / 試行回数 |
| SQL Server | Windows パスワードポリシー継承 (CHECK_POLICY = ON) |
運用のベストプラクティス
- 本番 DB の root / sa / postgres は直接アプリから使わない
- アプリごとに専用ユーザー、本番 / ステージング / 開発で別アカウント
- パスワードは シークレットマネージャ（AWS Secrets Manager、HashiCorp Vault）で管理
- ユーザー作成 / 削除は IaC（Terraform / Ansible / Flyway 等） で履歴を残す
- 定期的に 未使用ユーザーの棚卸し とパスワードローテーション
- 監査ログ（MySQL Audit / Oracle Unified Auditing / SQL Server Audit）でアクセス記録
FAQ
Q: MySQL で同じユーザー名で host が違うのは別アカウント？
A: はい、'app'@'localhost' と 'app'@'%' は完全に別アカウントです。それぞれに権限とパスワードを設定する必要があります。
Q: PostgreSQL の ROLE と USER の違いは？
A: ROLE が上位概念で、USER は LOGIN 属性つきの ROLE のエイリアスです。CREATE USER = CREATE ROLE LOGIN。
Q: SQL Server のログインとユーザーを別名にする理由は？
A: 本番と検証で同じ DB スクリプトを動かすため、DB ユーザー名を固定にしてログイン名だけ環境ごとに変えたい場合などに利用します。
Q: アプリのパスワードを変えたら接続できなくなった
A: ALTER USER ... IDENTIFIED BY 直後、コネクションプールの既存接続は古いパスワードのまま生き残ります。アプリを再起動するか接続を全て破棄してください。