Oracleユーザー

日付:

 


✅ Oracleユーザーとスキーマの基本

🔷 1. データベースユーザーとは？

  • ユーザーは必ずしも人間ではなく、たとえばアプリケーション（例：HRシステム）のデータオブジェクト所有者として使うことも多い。

  • ユーザーがDBへアクセスするには、ユーザーアカウントを作成し、適切な権限を付与する必要がある。

🔷 2. CREATE USERコマンドで指定する内容

項目説明
ユーザー名一意な名前
認証方式通常はパスワード認証を使用
パスワードログイン用のパスワード
デフォルト表領域オブジェクトが作成されるデフォルトの場所
一時表領域ソートや一時表など一時的な操作に使用
クォータ表領域でユーザーが使用できる最大サイズ（UNLIMITEDで制限なし）
プロファイルリソース制限やパスワードポリシーの指定（省略時はDEFAULT）
アカウント状態ACCOUNT LOCK で無効化、ACCOUNT UNLOCK で有効化

🔷 3. ユーザーとスキーマの関係

  • ユーザーを作成すると、自動的に同名のスキーマ（オブジェクト保管場所）も作成される。

  • ユーザー ≠ スキーマ だが、名前は同じになる。

  • スキーマには以下のようなオブジェクトが含まれる：

    • テーブル、ビュー、シーケンス、プロシージャ、シノニム、インデックス、クラスター、DBリンク など

🔷 4. 管理用ユーザーと権限

  • Oracleにはインストール時に作成される**管理者アカウント（SYS、SYSTEMなど）**がある。

  • 例：SYSDBA や CDB 権限は、バックアップ・復元やDB起動/停止などの高レベル操作が可能。

🔷 5. マルチテナント環境におけるユーザー

種類説明作成方法の違い
共通ユーザー（Common User）すべてのCDBとPDBに存在し、グローバルに使用されるユーザーユーザー名はC##で始める必要がある
rootコンテナで作成し、CONTAINER=ALLを指定
ローカルユーザー（Local User）特定のPDB内のみに存在PDBに接続し、その中で作成する

🔷 6. ユーザー情報の確認

  • すべてのユーザー情報を確認するには、rootコンテナから以下のビューを使用：

sql
SELECT * FROM CDB_USERS;

このビューでは、各PDBごとのユーザー情報を確認できる。


✅ Oracleの権限とロール

🔷 1. 権限とは？

権限（Privilege） とは、特定のSQL文を実行したり、他のユーザーのオブジェクトにアクセスしたりする「権利」のこと。

🔹 権限の種類

分類説明例
システム権限（System Privilege）DB全体に対する操作権限CREATE TABLE, CREATE USER, CREATE ANY TABLEなど
オブジェクト権限（Object Privilege）特定のオブジェクトに対する操作権限SELECT, INSERT, DELETEなど（例：employeesテーブルに対するSELECT権限）

  • ANY句付きのシステム権限は、すべてのスキーマに対して有効。

  • ADMIN OPTION付きで付与すれば、他ユーザーへの再付与が可能。

🔷 2. 権限の付与方法（マルチテナント環境）

方法対象コマンド例
共通付与（Common）全CDB/PDBにまたがってユーザーに付与GRANT privilege TO user CONTAINER=ALL
ローカル付与（Local）特定のPDB内のユーザーにのみ付与PDBに接続し GRANT privilege TO user

🔷 3. ロール（Role）の利用

  • ユーザー1人1人に個別に権限を付与すると手間がかかるため、**ロール（Role）**を利用する。

  • ロールには複数の権限をまとめて管理でき、ユーザーにロールを付与すれば、一括で権限を与えることができる。

🔹 ロールの作成と付与の流れ

  1. ロール作成

sql
CREATE ROLE clerk;
CREATE ROLE hr_manager;

※IDENTIFIED BY password を指定すると、パスワード保護付きロールになる。

  1. 権限をロールに付与

sql
GRANT CREATE SESSION TO clerk;
GRANT SELECT, UPDATE ON employees TO clerk;
GRANT INSERT, DELETE ON employees TO hr_manager;

  1. ロール同士の入れ子付与（ロール→ロール）

sql
GRANT clerk TO hr_manager;

  1. ユーザーへのロール付与

ユーザーロール付与方法
Alex, Tomclerkローカル付与（PDB内）
Olgahr_managerローカル付与
Donaldvip_security（パスワード付きロール）ローカル付与＋後で有効化が必要

🔷 4. ロールの有効化とデフォルト設定

  • ユーザーがログインすると、デフォルトロールのみ有効。

  • パスワード付きロールなどは、明示的に有効化が必要。

sql
-- セッション内でロールを有効化
SET ROLE vip_security IDENTIFIED BY password;


-- ロールをデフォルトに設定（次回以降自動有効化）
ALTER USER user DEFAULT ROLE role_name;

🔷 5. 実際の検証例

  • Olga（hr_managerロール）：

    • CREATE SESSION、INSERT、SELECTは可能

    • LOCATIONSテーブルのSELECTは不可（権限がないため）

  • Alex（clerkロール）：

    • CREATE SESSION、SELECT可能

    • INSERT不可（clerkロールに付与されていない）

  • Donald（vip_securityロール）：

    • 初期はCREATE SESSIONが直接付与されておらずログイン不可 → 解決

    • ロールがパスワード付きなので明示的なSET ROLEが必要

🔐 Oracleユーザー権限の確認と管理

🔍 使用する主なビュー（CDB環境）

  1. CDB_SYS_PRIVS ビュー

    • 全ユーザーやロールに付与されたシステム権限（例：CREATE SESSION など）を確認できる。

    • **フィルター（WHERE句）**を使えば、今回作成したユーザーのみの権限に絞り込むことも可能。

    • 主なカラム：

      • GRANTEE: 権限が与えられたユーザーやロール名

      • PRIVILEGE: 付与された権限名

      • ADMIN_OPTION: 管理権限も付与されているか

      • COMMON: 共通ユーザーへの付与かどうか

      • INHERITED: 他のコンテナから継承されたか

      • CON_ID: 対象のコンテナID

  2. CDB_TAB_PRIVS ビュー

    • ユーザーやロールが特定のテーブルに対して持っている権限を確認できる。

    • カラムの意味は上記と類似しており、説明は省略可。

  3. CDB_ROLE_PRIVS ビュー

    • どのユーザーやロールが、どのロールを付与されているか を確認できる。

🧑‍💻 ユーザー自身の権限のみを見たい場合

  • 上記の CDB_ ビューは すべてのユーザーの情報を見られる ため、大量の出力になることがある。

  • 自分の権限だけを確認したい場合は、以下のように USER_ プレフィックスのビューを使用する。

    • 例：

      • USER_SYS_PRIVS

      • USER_TAB_PRIVS

      • USER_ROLE_PRIVS

⚠️ セキュリティの基本：最小権限の原則（Principle of Least Privilege）

  • ユーザーには 業務に必要最低限の権限のみを付与すべき。

  • 無用な権限の付与はセキュリティリスクにつながる。


🧹 オブジェクトのクリーンアップ（片付け作業）

  1. 権限の取り消し（REVOKE）

    • まず、ロールやユーザーからテーブル権限を取り消すには、REVOKE 文を使います。

    • 例として、CLERK ロールから REPLACE テーブルに対する SELECT 権限を取り消しました。

  2. ロールの削除

    • DROP ROLE 文を使って、ロールを削除します。

  3. ユーザーの削除

    • DROP USER 文を使って、ユーザーを削除します。

    • CASCADE オプションを付けると、そのユーザーが所有しているスキーマ内のオブジェクトもすべて削除されます。

    • 今回のユーザーはスキーマオブジェクトを持っていないため、CASCADE オプションなしで削除可能です。

🔐 Oracleデータベースにおけるユーザー認証の方法


🔸主な認証方法

  1. 🧾 パスワード認証

    • 最も一般的な方法。

    • 各ユーザーは接続時にパスワードを入力する必要があります。

    • 本コースでも使用した認証方法です。

  2. 🖥️ OS（オペレーティングシステム）認証

    • Oracle Universal InstallerがOSグループを作成し、特定の システム権限 にマッピング。

    • Linux/Unixでは、/etc/group にグループが定義されており、例として dba グループはデータベース管理者グループ。

    • Oracleユーザーがこのグループに属していれば、パスワードなしで管理者として認証可能（OS認証）。

  3. 🔐 パスワードファイル認証（Password File Authentication）

    • 管理者ユーザーの資格情報を 外部ファイル（パスワードファイル） に保存。

    • データベースがダウンしていても認証可能。

    • 例：SYSDBA 権限を持つユーザーは、このパスワードファイルに記録される。

    • V$PWFILE_USERS ビューで、管理権限を持つユーザーを確認可能。

  4. 📜 SSL認証やディレクトリベースの認証（強力な認証）

    • Oracle認証アダプタを使って SSL証明書やLDAP（ディレクトリサービス） など外部の認証方式を利用可能。

    • ネットワーク上のクライアント、サーバー、ユーザーを一元管理して安全性を高める。

🛡️ Oracleにおけるリソース制限とプロファイル管理

🔸プロファイルで制御可能なリソース

💻 CPUリソース

  • CPU_PER_SESSION: セッション単位のCPU使用制限（例：1000 = 10秒）

  • CPU_PER_CALL: 各コール単位のCPU使用制限

🌐 ネットワーク & メモリ

  • CONNECT_TIME: セッションの接続時間制限（分）

  • IDLE_TIME: 一定時間操作がなければ自動ログオフ（分）

  • SESSIONS_PER_USER: 同時接続数制限

  • PRIVATE_SGA: SGAで使用できる最大メモリ量（共有サーバー使用時）

💾 ディスクI/O

  • READS_PER_SESSION: セッション単位の読み取り回数制限

  • READS_PER_CALL: コール単位の読み取り回数制限

🔐 パスワード関連の制限（Password Management）

  • PASSWORD_LIFE_TIME: パスワードの有効期間（日数）

  • PASSWORD_GRACE_TIME: 有効期限後の猶予期間（日数）

  • PASSWORD_REUSE_TIME: 再利用不可な期間

  • PASSWORD_REUSE_MAX: 同じパスワード再利用に必要な変更回数

  • FAILED_LOGIN_ATTEMPTS: ログイン失敗でロックされる回数

  • PASSWORD_LOCK_TIME: ロック期間（日数）

  • INACTIVE_ACCOUNT_TIME: アカウントの非アクティブ期間（日数）

  • PASSWORD_VERIFY_FUNCTION: パスワードの複雑性をチェックする関数

    例：8文字以上、英字・数字・特殊文字を含むか確認。

🧩 マルチテナント環境でのプロファイル

  • 共通プロファイル（Common Profile）: CDB全体で共有（現在と将来のすべてのPDBに適用）

  • ローカルプロファイル（Local Profile）: 特定のPDB内のみで有効

ユーザーにも共通またはローカルでプロファイルを割り当て可能。

🧪 例：アイドルセッション制御

  • 長時間アイドル状態のユーザーセッションを制限するために、IDLE_TIME を設定したプロファイル（例：Idle_Killer）を作成。

  • ALTER PROFILE 文で後から内容を変更可能。

  • プロファイルのリソース制限を有効にするには、初期化パラメータ RESOURCE_LIMIT = TRUE を設定する必要がある（ALTER SYSTEM で変更可）。

🗃️ ユーザーのテーブルスペース使用量制限（クォータ）

  • クォータにより、各ユーザーがテーブルスペース上で使用できる最大容量を設定可能。

  • 現在の使用状況は DBA_TS_QUOTAS や USER_TS_QUOTAS ビューで確認可能。

  • 例：USERSテーブルスペースに10GBまで使用可能と設定。

  • 必要に応じて増やしたり、UNLIMITED にすることも可能。