2024年11月1日 23ai
Facebook にシェア
LINEで送る

はじめに

こんにちは。Oracle Databaseの検証チームです。
今回は、Oracle 23aiの新機能「スキーマ権限」について検証してみました。

今回の執筆者は Oracle Cloud Infrastructure Foundations 2022 Certified Associateを取得しました！に登場した事業部員です！

スキーマ権限とは

Oracle 23aiでは新たにスキーマ権限が実装され、ANY権限を付与する際に対象の スキーマ を限定できるようになりました。

従来の方法

従来のシステム権限付与の方法として、主に以下の2点があります。

  1. ①スキーマ内の各表およびビューに対し、個別に権限を付与
  2. ②ANY権限を付与し、任意の表の選択、更新

①の方法だと個別に権限付与する必要があるため、大量のオブジェクトがある場合は手間がかかり、テーブルが新規作成されるたびに権限を付与していく運用が必要となります。

②の方法ではデータベース内のすべての表にアクセスできるため、セキュリティの観点から最適ではありません。

スキーマ権限の場合

それでは、Oracle 23aiで追加されたスキーマ権限について見ていきましょう。

スキーマ権限ではANY権限を付与する際に対象スキーマを限定できるようになったため、
参照させたいスキーマのテーブルにだけSELECTできるようになりました。

スキーマ権限を付与する場合は以下のSQLを実行します。

GRANT <権限名> ON SCHEMA <権限内で限定したいスキーマ> TO <権限付与するスキーマ/ロール>

例

GRANT SELECT ANY TABLE ON SCHEMA USER1 TO USER3

この例の場合、USER3はUSER1スキーマのすべての表およびビューを参照できるようになります。

スキーマ権限を使ってみる

実際にスキーマ権限を使い、特定のスキーマが所有している表のみ参照できることを確認していきます。
テーブル所有ユーザのUSER1、USER2はそれぞれTABLE1、TABLE2を所有しており、接続用ユーザのUSER3にはUSER1のTABLE1のみ参照できるような権限付与をしていきます。

権限付与されていない状況でSELECTしてみます。

SQL> connect USER3/<password>@FREEPDB1
接続されました。
SQL> 
SQL> SELECT * FROM USER1.TABLE1;
SELECT * FROM USER1.TABLE1
*
行1でエラーが発生しました。:
ORA-00942: 表またはビュー "USER1"."TABLE1"は存在しません ヘルプ:
https://docs.oracle.com/error-help/db/ora-00942/

SQL> 
SQL> SELECT * FROM USER2.TABLE2;
SELECT * FROM USER2.TABLE2
*
行1でエラーが発生しました。:
ORA-00942: 表またはビュー "USER2"."TABLE2"は存在しません ヘルプ:
https://docs.oracle.com/error-help/db/ora-00942/

まだ権限がないため、どちらの表も参照はできていません。

では、USER3にスキーマ権限を付与します。

SQL> GRANT SELECT ANY TABLE ON SCHEMA USER1 TO USER3;

権限付与が成功しました。

権限を確認します。

SQL> SELECT grantee, privilege, schema FROM dba_schema_privs WHERE grantee = 'USER3';

GRANTEE PRIVILEGE SCHEMA
---------- ---------------------------------------- ----------
USER3 SELECT ANY TABLE USER1

スキーマ権限付与後に、表が参照できるか確認します。

SQL> connect USER3/<password>@FREEPDB1
接続されました。
SQL> SELECT * FROM USER1.TABLE1;

COLUM1
----------
111

SQL> SELECT * FROM USER2.TABLE2;
SELECT * FROM USER2.TABLE2
*
行1でエラーが発生しました。:
ORA-00942: 表またはビュー "USER2"."TABLE2"は存在しません ヘルプ:
https://docs.oracle.com/error-help/db/ora-00942/

SELECT ANY TABLE権限を付与しましたが、全ての表ではなくTABLE1のみ参照できています。

スキーマ権限付与後のテーブル作成

次に、USER3がUSER1に対してスキーマ権限を持っている状態でUSER1が新規にTABLE3を作成した場合、どうなるか見ていきます。

まずはUSER1でTABLE3を作成します。

SQL> connect USER1/<password>@FREEPDB1
接続されました。
SQL> create table TABLE3(colum3 varchar(10));

表が作成されました。

SQL> insert into USER1.TABLE3 values (333);

1行が作成されました。

では、既にスキーマ権限を持っているUSER3から参照できるか確認していきます。

SQL> connect USER3/<password>@FREEPDB1
接続されました。
SQL> SELECT * FROM USER1.TABLE3;

COLUM3
----------
333

USER3から、先ほど新しく作成したTABLE3が参照できています。

さいごに

Oracle23aiの新機能であるスキーマ権限を活用することで、スキーマごとに参照権限を管理することが出来ました。
また、スキーマ権限を持っていればオブジェクトが増えても都度権限を追加するといった手間も不要であることが分かりました。

当社では最新機能の検証を常に行っています。
Oracle23ai導入を検討されている企業さまは是非お気軽にお問合せください！

投稿者プロフィール

技術チーム
技術チーム
DBひとりでできるもんを盛り上げるべく、技術チームが立ち上がり早6年。ひとりでできるもんと言いつつ、技術者が読んでプッとなるような、極めてピンポイントでマニアックな技術ネタを執筆しています！

　　アナタにおすすめのこちらの関連記事も読んでみませんか？

Facebook にシェア
LINEで送る

カテゴリー
23ai、Oracle Database 管理