Oracle Databaseの最新ロングタームリリースであるOracle Database 23ai(今まではOracle Database 23cと呼称)の提供開始が発表されました。
ということで(?)、今回は最近触れる機会があった、Oracle Database 23aiの新機能の一つである、スキーマ権限について調べてみました。
1. スキーマ権限とは
スキーマ権限とは、システム権限の対象を特定スキーマに限定できる、Oracle Database 23aiで登場した新機能です。
例えば、今まではシステム権限であるSELECT ANY TABLE権限を付与する際に、権限の対象スキーマを指定できないため、本来は参照させたくないスキーマのテーブルにもSELECTできてしまいましたが、スキーマ権限では特定スキーマのみを対象としたSELECT ANY TABLE権限が付与できるようになったので、参照させたいスキーマのテーブルにだけSELECTできるような制御をできるようになりました。
※Database Vaultを導入している場合は、レルムを定義することで、SELECT ANY TABLE権限が付与されていても、参照させたくないスキーマのテーブルへのアクセスを制限することができます。
2. スキーマ権限の付与方法
スキーマ権限は以下の様なGRANT文によって、ユーザやロールに付与できます。ポイントはON SCHEMA句で権限対象のスキーマを指定する点です。
GRANT <スキーマ権限名> ON SCHEMA <権限対象のスキーマ(ユーザ)> TO <権限付与対象ユーザ/ロール>;
ON SCHEMA句に続けて指定できるスキーマは1つだけなので、1回のGRANT文で複数のスキーマに対するスキーマ権限の付与はできないため、付与したい場合は複数回GRANT文を実行する必要がある点に注意が必要です。
※TOの後は複数のユーザ/ロールを指定できます。
3. 設定例
以下の様なユーザ(スキーマ)構成の環境下で、スキーマ権限の設定例を紹介いたします。
- データベースへの接続用ユーザとしてUSR_AとUSR_Bが存在
- テーブル所有ユーザとしてUSR_CとUSR_Dが存在
- USR_Cが所有するテーブルはUSR_AとUSR_Bにアクセスさせたいが、USR_Dが所有するテーブルにはアクセスさせたくない
- USR_CとUSR_Dが所有するテーブル数は多く且つ頻繁に増減するので、SELECT ANY TABLE権限で対応したい
上記の図に合わせて、USR_Aにシステム権限のSELECT ANY TABLE権限を付与し、USR_BにUSR_Cの所有テーブルにだけSELECTできるSELECT ANY TABLEのスキーマ権限を付与して、両ユーザのテーブルアクセス状況を比較してみます。
今回の実機確認では、OCIのOracle Base Database Serviceで構築した、Oracle Database 23ai(23.4.0)を使用しました。
まず、USR_AとUSR_Bに権限を付与します。システム権限はdba_sys_privsディクショナリを用いて権限の付与状況を確認できますが、スキーマ権限の場合はdba_schema_privsディクショナリを使用します。
-- USR_Aへのシステム権限付与 SQL> GRANT SELECT ANY TABLE TO USR_A; Grant succeeded. -- dba_sys_privsディクショナリで権限の付与状況を確認 SQL> SELECT grantee, privilege FROM dba_sys_privs WHERE grantee = 'USR_A' ORDER BY 1,2; GRANTEE PRIVILEGE ------------------------------ ---------------------------------------- USR_A SELECT ANY TABLE -- USR_Bへのスキーマ権限付与 SQL> GRANT SELECT ANY TABLE ON SCHEMA USR_C TO USR_B; Grant succeeded. -- dba_schema_privsディクショナリで権限の付与状況を確認 SQL> SELECT grantee, privilege, schema FROM dba_schema_privs WHERE grantee LIKE 'USR%' ORDER BY 1,2,3; GRANTEE PRIVILEGE SCHEMA ------------------------------ ---------------------------------------- ------------------------------ USR_B SELECT ANY TABLE USR_C
まず、従来から存在しているシステム権限のSELECT ANY TABLE権限を付与した、USR_Aでのテーブルアクセス状況を確認してみます。
SQL> show user ユーザーは"USR_A"です。 SQL> SELECT * FROM USR_C.TAB001; COL1 COL2 ---------- ----- 1 AAAAA SQL> SELECT * FROM USR_D.TAB501; COL1 COL2 ---------- ----- 1 CCCCC
USR_Cが所有するTAB001テーブルにアクセスできるのはよいですが、本来はアクセスさせたくない、USR_Dが所有するTAB501テーブルにもアクセスできてしまっています。
続いて、USR_Cを対象としたスキーマ権限のSELECT ANY TABLE権限を付与した、USR_Bでのテーブルアクセス状況を確認してみます。
SQL> show user ユーザーは"USR_B"です。 SQL> SELECT * FROM USR_C.TAB001; COL1 COL2 ---------- ----- 1 AAAAA SQL> SELECT * FROM USR_D.TAB501; SELECT * FROM USR_D.TAB501 * ERROR at line 1: ORA-00942: table or view "USR_D"."TAB501" does not exist Help: https://docs.oracle.com/error-help/db/ora-00942/
USR_Cが所有するTAB001テーブルにアクセスでき、アクセスさせたくないUSR_Dが所有するTAB501テーブルに対するSELECT文はORA-00942エラーが発生し、アクセスできないことが確認できました。
4. さいごに
スキーマ権限を利用することで、範囲を限定した権限付与が行えることがわかりました。今までは望まないながらも、広範囲にアクセスできてしまうシステム権限を付与していた方は、Oracle Database 23aiからは、スキーマ権限の利用を検討してみるとよいかと思います。
