【Oracle】表領域ごとにアクセス制御を行う方法｜QUOTA・ロール・権限の分離運用を完全解説

レイヤー	設定内容	主な用途
DEFAULT TABLESPACE	ユーザーのデフォルト格納先	オブジェクト作成先の誘導
QUOTA	表領域ごとの使用量上限	作成可能な領域の制限
ROLE / PRIVILEGE	システム権限・オブジェクト権限	操作自体の許可・禁止

ポイント: QUOTAが0の表領域にはオブジェクトを作成できません。この仕組みを利用して表領域を実質的に制限します。

-- 販売部門ユーザーの作成
CREATE USER user_sales
  IDENTIFIED BY "P@ssw0rd2024"
  DEFAULT TABLESPACE   ts_sales    -- オブジェクト格納先
  TEMPORARY TABLESPACE temp         -- ソート領域
  QUOTA 500M ON ts_sales;          -- デフォルト表領域に500MBを許可

-- 接続・リソース権限の付与
GRANT CREATE SESSION TO user_sales;
GRANT CREATE TABLE, CREATE VIEW, CREATE PROCEDURE TO user_sales;

注意: RESOURCEロールにはUNLIMITED TABLESPACE権限が含まれる場合があります（Oracle 11g以前）。QUOTAによる制限が無効になるため、RESOURCEの代わりに個別権限を付与することを推奨します。

-- 特定表領域に使用量を許可（MB / GB / UNLIMITED 指定可）
ALTER USER user_sales QUOTA 100M ON ts_sales;
ALTER USER user_sales QUOTA UNLIMITED ON ts_sales;

-- 別の表領域へのアクセスを禁止（QUOTA 0 = 作成不可）
ALTER USER user_sales QUOTA 0 ON ts_hr;
ALTER USER user_sales QUOTA 0 ON ts_finance;

-- QUOTAの確認
SELECT username, tablespace_name,
       ROUND(bytes / 1024 / 1024, 2) AS used_mb,
       CASE max_bytes WHEN -1 THEN 'UNLIMITED'
            ELSE TO_CHAR(ROUND(max_bytes / 1024 / 1024, 2)) || 'MB' END AS max_quota
  FROM dba_ts_quotas
 WHERE username = 'USER_SALES'
 ORDER BY tablespace_name;

設定値	意味	dba_ts_quotasのmax_bytes
QUOTA 0 ON ts_x	作成不可（禁止）	0
QUOTA 100M ON ts_x	100MBまで作成可能	104857600
QUOTA UNLIMITED ON ts_x	無制限に作成可能	-1
（未設定）	作成不可（デフォルト）	レコードなし

-- 1. ロールの作成
CREATE ROLE sales_role;
CREATE ROLE hr_role;
CREATE ROLE readonly_role;

-- 2. ロールへ権限を付与
GRANT CREATE TABLE, CREATE VIEW, CREATE PROCEDURE TO sales_role;
GRANT CREATE TABLE, CREATE VIEW TO hr_role;
GRANT CREATE SESSION TO readonly_role;

-- 3. ロールをユーザーに付与
GRANT sales_role   TO user_sales, user_sales2;
GRANT hr_role      TO user_hr;
GRANT readonly_role TO user_report;

-- ロールの削除（ユーザーへの付与も自動で外れる）
DROP ROLE sales_role;

SELECT grantee, privilege, admin_option
  FROM dba_sys_privs
 WHERE grantee = 'USER_SALES'
 ORDER BY privilege;

SELECT grantee, granted_role, admin_option, default_role
  FROM dba_role_privs
 WHERE grantee = 'USER_SALES';

SELECT role, privilege, admin_option
  FROM role_sys_privs
 WHERE role = 'SALES_ROLE'
 ORDER BY privilege;

-- 自セッションで有効なシステム権限（ロール経由含む）
SELECT privilege FROM session_privs ORDER BY privilege;

-- DBAがユーザーの全権限ツリーを確認（再帰的に辿る）
SELECT granted_role
  FROM dba_role_privs
 WHERE grantee = 'USER_SALES'
UNION ALL
SELECT privilege
  FROM dba_sys_privs
 WHERE grantee = 'USER_SALES';

-- システム権限の取り消し
REVOKE CREATE TABLE FROM user_sales;

-- ロールの取り消し
REVOKE sales_role FROM user_sales;

-- オブジェクト権限の取り消し
REVOKE SELECT ON hr.employees FROM user_sales;

-- QUOTAを0にして表領域への書き込みを禁止
ALTER USER user_sales QUOTA 0 ON ts_sales;

-- UNLIMITED TABLESPACE を持つユーザーを確認
SELECT grantee, privilege
  FROM dba_sys_privs
 WHERE privilege = 'UNLIMITED TABLESPACE'
   AND grantee NOT IN ('SYS', 'SYSTEM')
 ORDER BY grantee;

-- 取り消す（RESOURCEロール経由の場合はロールごと取り消し）
REVOKE UNLIMITED TABLESPACE FROM user_sales;
REVOKE RESOURCE FROM user_sales; -- RESOURCEが原因の場合

注意: Oracle 12c以降ではRESOURCEロールにUNLIMITED TABLESPACEは含まれなくなりましたが、旧バージョンからの移行環境では注意が必要です。

-- ユーザーの表領域別使用量とクォータ
SELECT
  q.username,
  q.tablespace_name,
  ROUND(q.bytes        / 1024 / 1024, 2) AS used_mb,
  CASE q.max_bytes WHEN -1 THEN 'UNLIMITED'
       ELSE TO_CHAR(ROUND(q.max_bytes / 1024 / 1024, 2))
  END AS quota_mb,
  CASE q.max_bytes
    WHEN -1 THEN 'N/A'
    WHEN  0 THEN '禁止'
    ELSE TO_CHAR(ROUND(q.bytes * 100 / q.max_bytes, 1)) || '%'
  END AS usage_rate
  FROM dba_ts_quotas q
 ORDER BY q.username, q.tablespace_name;

SELECT username, tablespace_name,
       ROUND(bytes / 1024 / 1024, 2) AS used_mb,
       ROUND(max_bytes / 1024 / 1024, 2) AS quota_mb,
       ROUND(bytes * 100 / max_bytes, 1) AS pct
  FROM dba_ts_quotas
 WHERE max_bytes > 0
   AND  bytes * 100 / max_bytes >= 90
 ORDER BY pct DESC;

-- DDL操作（テーブル作成・削除）を監査
AUDIT CREATE TABLE, DROP TABLE BY user_sales;

-- ログイン成功・失敗を監査
AUDIT SESSION BY user_sales;

-- 監査ログの確認
SELECT db_user, action_name, obj_name, timestamp
  FROM dba_audit_trail
 WHERE db_user = 'USER_SALES'
 ORDER BY timestamp DESC;

-- ① 表領域の作成（DBとして別途実施）
-- CREATE TABLESPACE ts_sales ... / ts_hr ... / ts_finance ...

-- ② ロールの定義
CREATE ROLE r_sales_rw;   -- 販売: 読み書き
CREATE ROLE r_report_ro;  -- 報告: 読み取り専用

GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW TO r_sales_rw;
GRANT CREATE SESSION TO r_report_ro;

-- ③ ユーザーの作成
CREATE USER user_sales IDENTIFIED BY "SalesPw#1"
  DEFAULT TABLESPACE ts_sales TEMPORARY TABLESPACE temp
  QUOTA 200M ON ts_sales;

CREATE USER user_report IDENTIFIED BY "RptPw#1"
  DEFAULT TABLESPACE ts_sales TEMPORARY TABLESPACE temp
  QUOTA 0 ON ts_sales; -- 読み取り専用なので書き込み不可

-- ④ ロールの付与
GRANT r_sales_rw  TO user_sales;
GRANT r_report_ro TO user_report;

-- ⑤ 他部門表領域への書き込みを明示的に禁止
ALTER USER user_sales QUOTA 0 ON ts_hr;
ALTER USER user_sales QUOTA 0 ON ts_finance;

手段	目的	主なコマンド
DEFAULT TABLESPACE	デフォルト格納先の指定	CREATE/ALTER USER
QUOTA	表領域への書き込み量制限	ALTER USER … QUOTA
ROLE	権限セットの一元管理	CREATE ROLE / GRANT
REVOKE	権限の取り消し	REVOKE … FROM
AUDIT	操作ログの記録	AUDIT … BY user