【Oracle】ユーザー・権限・ロール完全ガイド｜CREATE USER・GRANT・REVOKE・システム権限・オブジェクト権限・ロール管理まで解説

-- 基本的なユーザー作成
CREATE USER app_user IDENTIFIED BY "SecurePass123#"
DEFAULT TABLESPACE app_data      -- デフォルト表領域（オブジェクトが作成される表領域）
TEMPORARY TABLESPACE temp        -- 一時表領域（ソート・ハッシュ結合のワーク領域）
QUOTA 500M ON app_data           -- app_data 表領域の使用上限
QUOTA UNLIMITED ON app_index;    -- app_index 表領域は無制限

-- パスワードを変更する
ALTER USER app_user IDENTIFIED BY "NewPass456#";

-- アカウントをロック / アンロックする
ALTER USER app_user ACCOUNT LOCK;     -- アカウントをロック（ORA-28000 が発生するようになる）
ALTER USER app_user ACCOUNT UNLOCK;   -- アンロックする

-- パスワードを期限切れにする（次回ログイン時にパスワード変更を強制する）
ALTER USER app_user PASSWORD EXPIRE;

-- デフォルト表領域・一時表領域・クォータを変更する
ALTER USER app_user DEFAULT TABLESPACE new_data;
ALTER USER app_user QUOTA 2G ON app_data;
ALTER USER app_user QUOTA 0 ON app_data;   -- 0 にするとオブジェクト作成不可（既存は残る）

-- ユーザーを削除する
DROP USER app_user;                   -- オブジェクトがある場合はエラー
DROP USER app_user CASCADE;           -- ユーザーと全オブジェクトを削除（注意: 不可逆）

-- ユーザーの情報を確認する
SELECT username, account_status, default_tablespace, temporary_tablespace,
       profile, created, lock_date, expiry_date
FROM DBA_USERS
WHERE username = 'APP_USER';

-- 基本的なシステム権限の付与
GRANT CREATE SESSION TO app_user;           -- ログイン（接続）する権限
GRANT CREATE TABLE TO app_user;             -- テーブルを作成する権限（自スキーマ）
GRANT CREATE VIEW TO app_user;              -- ビューを作成する権限
GRANT CREATE PROCEDURE TO app_user;         -- プロシージャ・関数・パッケージを作成する権限
GRANT CREATE SEQUENCE TO app_user;          -- シーケンスを作成する権限
GRANT CREATE TRIGGER TO app_user;           -- トリガーを作成する権限
GRANT CREATE INDEX TO app_user;             -- インデックスを作成する権限（自スキーマ）
GRANT CREATE ANY TABLE TO app_user;         -- 任意スキーマにテーブルを作成する権限
GRANT DROP ANY TABLE TO app_user;           -- 任意スキーマのテーブルを削除する権限

-- 複数ユーザーにまとめて付与する
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW TO app_user, report_user;

-- WITH ADMIN OPTION: 付与された権限をさらに他のユーザーに付与できる
GRANT CREATE TABLE TO admin_user WITH ADMIN OPTION;

-- システム権限の取り消し（WITH ADMIN OPTION 経由で付与した権限は連鎖的に取り消されない）
REVOKE CREATE TABLE FROM app_user;
REVOKE CREATE SESSION FROM app_user;

-- 代表的なシステム権限一覧
-- DBA              : データベース管理者権限（ほぼすべての操作が可能）
-- CONNECT          : CREATE SESSION のみを含むロール（古い慣習）
-- RESOURCE         : CREATE TABLE/VIEW/PROCEDURE/SEQUENCE など + UNLIMITED TABLESPACE
-- CREATE SESSION   : データベースへの接続
-- SYSDBA / SYSOPER : OS 認証レベルの最高権限（AS SYSDBA で接続する場合に必要）

-- テーブルへのアクセス権限を付与する
GRANT SELECT ON hr.employees TO report_user;            -- SELECT 権限
GRANT SELECT, INSERT, UPDATE ON hr.orders TO app_user;  -- 複数権限
GRANT DELETE ON hr.temp_log TO app_user;
GRANT ALL ON hr.employees TO hr_admin;                  -- すべてのオブジェクト権限

-- 特定の列のみに権限を付与する（列レベル権限: SELECT / INSERT / UPDATE のみ対応）
GRANT SELECT (employee_id, last_name, department_id) ON hr.employees TO report_user;
GRANT UPDATE (salary) ON hr.employees TO payroll_user;  -- salary 列のみ UPDATE 可能

-- ビューへの権限付与（テーブルと同じ）
GRANT SELECT ON hr.emp_summary_v TO report_user;

-- プロシージャ・関数・パッケージへの権限付与
GRANT EXECUTE ON hr.calculate_bonus TO app_user;
GRANT EXECUTE ON hr.salary_pkg TO app_user;

-- PUBLIC への権限付与（全ユーザーが使えるようになる）
GRANT SELECT ON hr.departments TO PUBLIC;  -- すべてのユーザーが参照可能になる

-- WITH GRANT OPTION: 付与された権限をさらに他のユーザーに付与できる
-- （REVOKE 時は連鎖的に取り消される: WITH ADMIN OPTION とここが違う）
GRANT SELECT ON hr.employees TO app_admin WITH GRANT OPTION;

-- オブジェクト権限の取り消し
REVOKE SELECT ON hr.employees FROM report_user;
REVOKE EXECUTE ON hr.calculate_bonus FROM app_user;
REVOKE SELECT ON hr.departments FROM PUBLIC;

-- シノニムを作成してスキーマ名なしでアクセスできるようにする
CREATE SYNONYM employees FOR hr.employees;        -- プライベートシノニム（自スキーマのみ）
CREATE PUBLIC SYNONYM employees FOR hr.employees; -- パブリックシノニム（全ユーザー）

-- カスタムロールを作成する
CREATE ROLE app_read_role;         -- パスワードなしのロール
CREATE ROLE app_write_role;
CREATE ROLE secure_role IDENTIFIED BY role_pass;  -- パスワード付きロール（使用時に SET ROLE が必要）

-- ロールに権限を付与する（システム権限・オブジェクト権限どちらも付与できる）
GRANT CREATE SESSION TO app_read_role;
GRANT SELECT ON hr.employees TO app_read_role;
GRANT SELECT ON hr.departments TO app_read_role;
GRANT SELECT ON hr.jobs TO app_read_role;

GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW TO app_write_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON hr.employees TO app_write_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON hr.orders TO app_write_role;

-- ロールを別のロールに付与する（ロールの階層化）
GRANT app_read_role TO app_write_role;  -- app_write_role は app_read_role の権限も含む

-- ユーザーにロールを付与する
GRANT app_read_role TO report_user;
GRANT app_write_role TO app_user;
GRANT app_read_role TO app_user;   -- 複数ロールを付与できる

-- ロールを取り消す
REVOKE app_read_role FROM report_user;

-- ロールを削除する（ロールを付与されたユーザーの権限も自動的に失われる）
DROP ROLE app_read_role;

-- デフォルトロール: ログイン時に自動で有効になるロールを設定する
ALTER USER app_user DEFAULT ROLE app_read_role;        -- 特定ロールのみ有効
ALTER USER app_user DEFAULT ROLE ALL;                  -- 付与済みのすべてのロール（デフォルト）
ALTER USER app_user DEFAULT ROLE ALL EXCEPT secure_role; -- secure_role 以外を有効にする
ALTER USER app_user DEFAULT ROLE NONE;                 -- ログイン時にロールを有効にしない

-- セッション内でロールを有効・無効にする
SET ROLE app_write_role;                   -- 指定ロールのみ有効にする
SET ROLE secure_role IDENTIFIED BY role_pass;  -- パスワード付きロール
SET ROLE ALL;                              -- 付与済みロールをすべて有効にする
SET ROLE NONE;                             -- すべてのロールを無効にする

-- ユーザーに付与されているシステム権限を確認する
SELECT grantee, privilege, admin_option
FROM DBA_SYS_PRIVS
WHERE grantee = 'APP_USER'
ORDER BY privilege;

-- ユーザーに付与されているオブジェクト権限を確認する
SELECT grantee, owner, table_name, privilege, grantable
FROM DBA_TAB_PRIVS
WHERE grantee = 'APP_USER'
ORDER BY owner, table_name, privilege;

-- ユーザーに付与されているロールを確認する
SELECT grantee, granted_role, admin_option, default_role
FROM DBA_ROLE_PRIVS
WHERE grantee = 'APP_USER';

-- ロールに付与されているシステム権限を確認する
SELECT grantee, privilege, admin_option
FROM DBA_SYS_PRIVS
WHERE grantee = 'APP_READ_ROLE';

-- ロールに付与されているオブジェクト権限を確認する
SELECT grantee, owner, table_name, privilege
FROM DBA_TAB_PRIVS
WHERE grantee = 'APP_READ_ROLE';

-- 自分自身（現在のセッション）の有効な権限を確認する
SELECT * FROM SESSION_PRIVS;          -- 現在有効なシステム権限（ロール経由含む）
SELECT * FROM USER_SYS_PRIVS;         -- 自分に直接付与されたシステム権限
SELECT * FROM USER_ROLE_PRIVS;        -- 自分に付与されたロール
SELECT * FROM USER_TAB_PRIVS_RECD;   -- 自分が受け取ったオブジェクト権限

-- 特定テーブルへのアクセス権限を一覧する（誰が SELECT できるか確認）
SELECT grantee, privilege, grantable
FROM DBA_TAB_PRIVS
WHERE owner = 'HR' AND table_name = 'EMPLOYEES'
ORDER BY grantee, privilege;