Oracleデータベースにおけるスキーマとオブジェクト権限は、データベースの管理と操作において非常に重要な概念です。初心者にとってこれらの用語が難しく感じられることもありますが、理解すればデータベースの効率的な運用に役立ちます。本記事では、初心者向けにスキーマとは何か、オブジェクト権限の基本的な考え方、そしてそれらの操作方法についてわかりやすく解説します。
1. スキーマとは？
まず、データベースにおける「スキーマ」という概念から説明します。スキーマは、ユーザーが所有するデータベースオブジェクトの集合です。データベースユーザーごとに作成され、ユーザーに関連付けられるため、そのユーザーが作成したテーブル、ビュー、インデックス、シーケンスなどのすべてのオブジェクトは、そのユーザーのスキーマに所属します。
1.1 スキーマの役割
スキーマは、データベース内のオブジェクトを整理し、適切に管理するために非常に重要な役割を果たします。大規模なデータベースでは、多数のユーザーが異なる目的でデータベースにアクセスし、データを操作します。このとき、各ユーザーが所有するオブジェクトが独立して管理されるようにするため、スキーマの存在が必要です。
例えば、USER1が「TABLE1」というテーブルを作成した場合、そのテーブルは「USER1のスキーマ内にある」という形になります。同じように、USER2が同じ名前の「TABLE1」テーブルを作成しても、それぞれが別々のスキーマに所属するため、コンフリクトは発生しません。
以下のようにUSER1とUSER2はそれぞれ同一名のテーブルTABLE1を所有しています。
しかしそれぞれのテーブルをSELECTすると出力する結果が異なっており、別のテーブルということが分かります。
【実行例】SQL> conn / as sysdba
接続されました。
SQL> select owner,object_name from all_objects where owner like 'USER%';
OWNER OBJECT_NAME
-------------------- --------------------
USER1 TABLE1 ★
USER2 TABLE1 ★
SQL> conn user1/user1
接続されました。
SQL> select * from table1;
NO
----------
1
SQL> conn user2/user2
接続されました。
SQL> select * from table1;
NO
----------
2
SQL>
1.2 スキーマの命名規則
スキーマ名は通常、対応するユーザー名と一致します。Oracleデータベースでは、ユーザー名はそのままスキーマ名となり、明示的にスキーマを指定する場合には、「<スキーマ名>.<オブジェクト名>」という形式で記述されます。例えば、USER1が所有するテーブル「TABLE1」を参照する場合は「USER1.TABLE1」となります。
2. オブジェクト権限とは？
次に、オブジェクト権限について解説します。オブジェクト権限は、あるユーザーが特定のデータベースオブジェクトに対して行える操作を制御するための権限です。Oracleデータベースでは、オブジェクトに対してどのユーザーがどのような操作を行うことができるかを細かく制御できるため、適切に権限を設定することがセキュリティと効率性の向上につながります。
2.1 オブジェクト権限の種類
オブジェクト権限は、操作対象となるオブジェクトの種類によって異なります。以下に、一般的なオブジェクトとそれに関連する権限をいくつか紹介します。
- テーブルに対する権限
- SELECT: テーブルからデータを読み取る権限。
- INSERT: テーブルにデータを挿入する権限。
- UPDATE: テーブル内の既存データを更新する権限。
- DELETE: テーブル内のデータを削除する権限。
- ALTER: テーブルの構造を変更する権限（カラムの追加など）。
- DROP: テーブルを削除する権限。
- ビューに対する権限
- SELECT: ビューからデータを読み取る権限。
- INSERT: ビューにデータを挿入する権限。
- UPDATE: ビューを通じてデータを更新する権限。
- DELETE: ビューを通じてデータを削除する権限。
- シーケンスに対する権限
- SELECT: シーケンスの現在値を参照する権限。
- ALTER: シーケンスの設定を変更する権限。
- プロシージャや関数に対する権限
- EXECUTE: プロシージャや関数を実行する権限。
これらの権限を適切に割り当てることで、データベース内のセキュリティを確保しつつ、ユーザーの役割に応じた操作を可能にします。
2.2 権限の付与と取り消し
データベースの管理者（通常はDBA）は、ユーザーに対して適切なオブジェクト権限を付与（GRANT）したり、取り消し（REVOKE）したりすることができます。これにより、セキュリティポリシーや業務上の要件に応じて、どのユーザーがどのオブジェクトにアクセスできるかを細かく制御できます。
- GRANT文の構文
GRANT [権限] ON [オブジェクト名] TO [ユーザー名];
例: USER2に対して、USER1のテーブル「TABLE1」に対するSELECT権限を付与する場合：GRANT SELECT ON user1.table1 TO user2; - REVOKE文の構文
REVOKE [権限] ON [オブジェクト名] FROM [ユーザー名];
例: 上記で付与したSELECT権限を取り消す場合：REVOKE SELECT ON user1.table1 FROM user2;
2.3 WITH GRANT OPTION
権限を付与する際に、「WITH GRANT OPTION」を使用すると、その権限を付与されたユーザーがさらに他のユーザーに対して同じ権限を付与することができます。例えば、ユーザーAがユーザーBに対して「WITH GRANT OPTION」で権限を付与した場合、ユーザーBは他のユーザーにも同様の権限を付与することが可能です。
- WITH GRANT OPTIONを使ったGRANT文cssコードをコピーする
GRANT [権限] ON [オブジェクト名] TO [ユーザー名] WITH GRANT OPTION;
2.4 実行例
SQL> select owner,object_name from all_objects where owner like 'USER%';
OWNER OBJECT_NAME
-------------------- --------------------
USER1 TABLE1
USER2 TABLE1
SQL> conn user1/user1
接続されました。
SQL> select * from table1; ★user1スキーマのtable1
NO
----------
1
SQL> conn user2/user2
接続されました。
SQL> select * from table1; ★user2スキーマのtable1
NO
----------
2
SQL> conn user1/user1
接続されました。
SQL> grant select on user1.table1 to user2; ★user2にtable1のselect権限を付与
権限付与が成功しました。
SQL> conn user2/user2
接続されました。
SQL> select * from user1.table1; ★user1スキーマのtable1をselectできる
NO
----------
1
SQL> conn user1/user1
接続されました。
SQL> revoke select on user1.table1 from user2; ★select権限をはく奪
取消しが成功しました。
SQL> conn user2/user2
接続されました。
SQL> select * from user1.table1; ★selectできなくなる
select * from user1.table1
*
行1でエラーが発生しました。:
ORA-00942: 表またはビューが存在しません。
SQL>
3. スキーマとオブジェクト権限の関連性
スキーマとオブジェクト権限は、データベースのセキュリティを高め、組織内でのデータ管理を効率的に行うために連携して機能します。ユーザーごとにスキーマが設定され、各スキーマ内でどのオブジェクトに誰がアクセスできるかがオブジェクト権限で制御されます。
例えば、データベース内で重要な営業データを管理する「sales」テーブルがある場合、そのテーブルにアクセスできるユーザーを厳密に制御する必要があります。この場合、特定のユーザーだけがデータを参照でき、別のユーザーにはデータの更新権限を与えるといった柔軟な設定が可能です。
また、DBAはスキーマ内のすべてのオブジェクトに対して権限を管理する責任を持つため、適切な権限管理がセキュリティ上のリスクを回避し、データベース運用の効率を高めます。
4. 実践例
実際にOracleデータベースでスキーマやオブジェクト権限を操作する手順を以下に示します。
4.1 スキーマの確認
スキーマ内のオブジェクトを確認するには、次のSQLクエリを使用します。SELECT * FROM all_objects WHERE owner = 'ユーザー名';
このクエリは、指定したユーザーが所有するすべてのオブジェクトをリストアップします。
【実行例】
SQL> select owner,object_name from all_objects where owner='USER1';
OWNER OBJECT_NAME
-------------------- --------------------
USER1 TABLE1
SQL>
4.2 権限の付与
ユーザーBに対して、ユーザーAのテーブル「sales」に対するSELECT権限を付与する例：GRANT SELECT ON user1.table1 TO user2;
4.3 権限の取り消し
先ほど付与した権限を取り消すには、次のSQL文を実行します。REVOKE SELECT ON user1.table1 FROM user2;
5. まとめ
Oracleデータベースにおけるスキーマとオブジェクト権限の管理は、データベースの運用において重要な役割を果たします。スキーマはユーザーごとにデータベースオブジェクトを整理するためのものであり、オブジェクト権限はデータに対する操作権限を制御するための仕組みです。適切な権限設定により、セキュリティを確保しながら効率的なデータ管理を実現することができます。
初心者の方でも、スキーマや権限の概念を理解し、基本的なSQL文を使えるようになれば、より高度なデータベース操作が可能になるでしょう。
[参考]
SQL言語リファレンス – データベース・オブジェクト名および修飾子
SQL言語リファレンス – GRANT
SQL言語リファレンス – REVOKE
コメント