AIです。（Artificial Intelligence ではありません）
つい先日、オンプレミス環境向けに Oracle Database 23ai の Free版がリリースされましたね…！元々「23c」と打ち出しておりましたが、生成AI に関連した機能に焦点を当て名称が「23ai」になったとのことです。
さて、せっかくですので、Oracle Database 23ai Free を使って新機能の検証をしてみたいと思います！今回フィーチャーする機能は、スキーマ権限です。従来は他のスキーマのオブジェクトを操作する場合の選択肢はオブジェクト権限かシステム権限のいずれかを付与していました。
- オブジェクト権限：オブジェクト単位で付与、対象が沢山あると管理が煩雑に
- システム権限：ANY権限でDB内のすべてのオブジェクトに対して権限付与、セキュリティ的にToo Muchになりがち
今回 23ai でスキーマ権限が実装されたことで、あるスキーマの持つオブジェクト全てに対する権限を付与することができるようになりました。では具体的にどのようなことができるか、実際に検証してみます！
■検証前の状態
まずは今回使用するユーザーや権限、テーブルの確認をします。
--# ユーザー一覧
SQL> SELECT username FROM dba_users WHERE username IN ('BRICK','STICK');
USERNAME
----------
STICK
BRICK
--# ユーザーに付与されているシステム権限
SELECT grantee,privilege FROM dba_sys_privs
SQL> 2 WHERE grantee IN ('BRICK','STICK');
GRANTEE PRIVILEGE
---------- ----------------------------------------
BRICK UNLIMITED TABLESPACE
STICK UNLIMITED TABLESPACE
--# ユーザーに付与されているロール
SQL> SELECT grantee,granted_role FROM dba_role_privs
2 WHERE grantee IN ('BRICK','STICK');
GRANTEE GRANTED_RO
---------- ----------
BRICK GENERAL
STICK GENERAL
--# GENERAL ロールの権限
SQL> SELECT grantee,privilege FROM dba_sys_privs
2 WHERE grantee IN ('GENERAL');
GRANTEE PRIVILEGE
------- ----------------------------------------
GENERAL CREATE TABLE
GENERAL CREATE SESSION
--# ユーザーが持つテーブルの一覧
SQL> SELECT owner,table_name FROM dba_tables
2 WHERE owner IN ('BRICK','STICK');
OWNER TABLE_NAME
---------- --------------------
BRICK INVENTORY
STICK INVENTORY
BRICK BOOKSHELF
STICK BOOKSHELF
--# ユーザーやロールが持つオブジェクト権限
SQL> SELECT grantee,owner,table_name,privilege FROM dba_tab_privs
2 WHERE grantee IN ('BRICK','STICK');
レコードが選択されませんでした。
SQL> SELECT grantee,privilege FROM dba_tab_privs
2 WHERE grantee IN ('GENERAL');
レコードが選択されませんでした。この状態から、検証スタートです！
■オブジェクト権限とスキーマ権限の違い
まずは、Stick ユーザーで Brick スキーマのオブジェクトを SELECT してみます。ただし、今はオブジェクト権限や SELECT ANY TABLE 権限がないので、テーブルにアクセスすることができませんでした。
SQL> show user
ユーザーは"STICK"です。
SQL> SELECT * FROM Stick.inventory; -- 自分のスキーマにあるテーブルにはもちろんアクセスできます。
FOOD STOCK
------------------------------ ----------
banana 1
SQL> SELECT * FROM Brick.inventory;
SELECT * FROM Brick.inventory
*
行1でエラーが発生しました。:
ORA-00942: 表またはビュー "BRICK"."INVENTORY"は存在しません ヘルプ:
https://docs.oracle.com/error-help/db/ora-00942/
SQL> SELECT * FROM Brick.bookshelf;
SELECT * FROM Brick.bookshelf
*
行1でエラーが発生しました。:
ORA-00942: 表またはビュー "BRICK"."BOOKSHELF"は存在しません ヘルプ:
https://docs.oracle.com/error-help/db/ora-00942/では従来通り、オブジェクト権限を付与します。
SQL> GRANT SELECT ON Brick.inventory TO Stick;
権限付与が成功しました。
SQL> show user
ユーザーは"STICK"です。
SQL> SELECT * FROM Brick.inventory;
FOOD STOCK
------------------------------ ----------
apple 2
SQL> SELECT * FROM Brick.bookshelf;
SELECT * FROM Brick.bookshelf
*
行1でエラーが発生しました。:
ORA-00942: 表またはビュー "BRICK"."BOOKSHELF"は存在しません ヘルプ:
https://docs.oracle.com/error-help/db/ora-00942/オブジェクト権限を付与したテーブル inventory には SELECT できるようになりましたが、Brick ユーザーの他のテーブル bookshlef に対しては SELECT ができません。 従来は Brick.bookshelf のテーブルに SELECT したい場合、別途 Brick.bookshelf に対する SELECT 権限を付与するか、SELECT ANY TABLE 権限を付与する必要がありました。
では、ここで新機能のスキーマ権限を付与してみましょう。スキーマ権限を付与するには以下のコマンドを実行します。
SQL> GRANT <権限> ON SCHEMA <対象スキーマ> TO <権限を付与されるユーザー>;
SQL> GRANT SELECT ANY TABLE ON SCHEMA Brick TO Stick;
権限付与が成功しました。スキーマ権限については、DBA_SCHEMA_PRIVS ビューで確認ができます。
SQL> SELECT * FROM DBA_SCHEMA_PRIVS;
GRANTEE PRIVILEGE SCHEMA ADM COM INH
---------- -------------------- ---------- --- --- ---
STICK SELECT ANY TABLE BRICK NO NO NOスキーマ権限を付与することで、Brick.bookshelf に対しても SELECT ができるようになりました！
SQL> show user
ユーザーは"STICK"です。
SQL> SELECT * FROM Brick.bookshelf;
TITLE
------------------------------
The Three Little Pigsオブジェクト権限ではなく、スキーマ権限の方が便利なポイントとして、新しく作成したオブジェクトに対して、別途オブジェクト権限を付与しなくても SELECT ができるという点があります。早速、Brick スキーマに新しいテーブルを作成してみましょう。
SQL> CREATE TABLE Brick.toolbox (tool VARCHAR2(30));
表が作成されました。
SQL> SELECT owner,table_name FROM dba_tables
2 WHERE owner IN ('BRICK','STICK');
OWNER TABLE_NAME
---------- --------------------
BRICK INVENTORY
STICK INVENTORY
BRICK BOOKSHELF
STICK BOOKSHELF
BRICK TOOLBOX --# 新しいテーブル
SQL> INSERT INTO Brick.toolbox VALUES('scissors');
1行が作成されました。
SQL> commit;
コミットが完了しました。
SQL> show user
ユーザーは"STICK"です。
SQL> SELECT * FROM Brick.toolbox;
TOOL
------------------------------
scissors
オブジェクト権限を付与しなくても Brick スキーマで新しく作成されたテーブルに対して SELECT することができました…！
■CREATE ANY TABLE 権限
もう一つちなみになお話ですが、スキーマ権限によって、「他のスキーマに新たなテーブルを作成する」ということが現実的に可能になったのではないかと思います。
19c までは他のスキーマにテーブルを作成する場合、CREATE ANY TABLE システム権限が必要でしたがこれは全てのスキーマに対してテーブルを作成できてしまう権限でした。そこまでの権限をユーザーに付与するのは躊躇してしまいますよね。
スキーマ権限の実装によって、特定のスキーマだけテーブルを作成できる状態は待ち望んでいた方もいらっしゃるのでは。
さて、ではこちらも試しに検証してみましょう。ここまでの検証では権限付与は sys ユーザーで行っていたのですが、今回は Brick ユーザーでスキーマ権限を付与してみましょう。
SQL> show user
ユーザーは"BRICK"です。
SQL> GRANT CREATE ANY TABLE ON SCHEMA Brick TO Stick;
GRANT CREATE ANY TABLE ON SCHEMA Brick TO Stick
*
行1でエラーが発生しました。:
ORA-01031: 権限が不足しています ヘルプ:
https://docs.oracle.com/error-help/db/ora-01031/おや、権限が足りていないと怒られてしまいます。
実は 23ai の GRANT コマンドのマニュアルを読むと、CREATE ANY TABLE の権限受領者がスキーマ所有者である場合、権限受領者にはCREATE TABLE権限が付与されている必要があると記載されています。
表18-3 スキーマ権限(認可される操作ごとに編成)
CREATE ANY TABLE
SYS、AUDSYSを除く任意のスキーマ内での表の作成。なお、表が設定されるスキーマの所有者は、表領域内にその表を定義するための割当て制限が必要です。権限受領者がスキーマ所有者である場合、権限受領者にはCREATE TABLE権限が付与されている必要があります
https://docs.oracle.com/cd/F82042_01/sqlrf/GRANT.html#GUID-20B4E2C0-A7F8-4BC8-A5E8-BE61BDC41AC3
冒頭で確認した通り、Brick ユーザーには General ロールで CREATE TABLE 権限を付与していたのですがこれではダメみたいですね…
改めて、直接 Brick ユーザーに CREATE TABLE 権限を付与してみましょう。
SQL> GRANT CREATE TABLE TO Brick;
権限付与が成功しました。
SQL> show user
ユーザーは"BRICK"です。
SQL> GRANT CREATE ANY TABLE ON SCHEMA Brick TO Stick;
権限付与が成功しました。これで権限付与することができました。あくまで今リリースされている Free 版(Version 23.4.0.24.05)での話にはなりますがCREATE TABLE 権限をロールで持っていたとしても、CREATE ANY TABLE のスキーマ権限を付与できない、というのは意外とひっかかりそうな気がします。
さて、無事 Stick ユーザーに CREATE ANY TABLE 権限が付与されましたので、Brick スキーマにテーブルを作成してみましょう。
SQL> show user
ユーザーは"STICK"です。
SQL>
SQL> CREATE TABLE Brick.gift (Content VARCHAR2(30));
表が作成されました。無事テーブルを作成することができました！
■まとめ
スキーマ単位で権限を付与できるようになったことで、従来よりも細やかに権限を設定したり、オブジェクトを作成する都度権限を付与しなくてもよくなりました。権限の管理はセキュリティ上重要ですので、管理性が向上したのは朗報なのではないかと思います。
今後も他の新機能で検証してみる予定ですので、次回の更新をお楽しみに…！