Oracle 23ai 新機能 – スキーマ権限

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));

表が作成されました。

無事テーブルを作成することができました!

■まとめ

スキーマ単位で権限を付与できるようになったことで、従来よりも細やかに権限を設定したり、オブジェクトを作成する都度権限を付与しなくてもよくなりました。権限の管理はセキュリティ上重要ですので、管理性が向上したのは朗報なのではないかと思います。

今後も他の新機能で検証してみる予定ですので、次回の更新をお楽しみに…!

コーソルカナダのメンバー紹介 (MOさん)

こんにちは!
毎度おなじみGENYAです。

今回も前回に引き続きメンバー紹介をしていきます!

という事で MO さんよろしくお願いします♪

MOさん> よろしくお願いします!

最初に軽い自己紹介からお願いします。

2017年4月にコーソルに入社して、日本では東京と福岡でOracle Databaseのサポートを対応していました。
好物はお酒(特にビールとワイン)です。

カナダにはいつ来ましたか?

2022年12月にカナダに来たので1年とちょっと経過しました。

カナダに来ると決めたきっかけはありますか?

これといったきっかけはないんですが、住む場所には特にこだわりはなかったので
海外で暮らすのも楽しそうだなと思って来ることにしました。

軽いノリで海外に出れるのすごい!

カナダに来てカルチャーショックはありましたか?

話には聞いていたのですが、外食が高くてビックリしました。
なるべく自炊をして食費を抑えるようにしています。
お昼もお弁当を作って持ってくることが多いです。

カナダでのレストランの支払いは確かに高くつきますよね。。

オススメの go-to place はありますか?

LCBO(Liquor Control Board of Ontario)という、お酒が買えるお店です。
※カナダでは日本よりお酒のルールが厳しく、決められたお店でしかお酒が買えないのです。

会社近くのLCBOの入り口

ビールの種類が豊富で価格も比較的リーズナブルなので、飲み比べをして楽しんでいます。

色々な国のビールやオンタリオ州のクラフトビールが置いてあるのでビール好きの人には凄く楽しめる所ですよね!

冬は何をして過ごしていますか?

休みの日にはよくアイススケートをしています。
カナダにいる間に上手くなりたい!

僕も上手くなりたいと思いながらも練習が全然出来ていないので頑張ります💦

カナダでやりたい事はありますか?

せっかくの機会なので英語力を向上させたいと思っていますがまだできていません。
今年こそは…!

英語話すのハードル高いですよね。。すごく分かります。
フォローが必要ならいつでも遠慮なく言ってくださいね。

最後にカナダでの Good Shot(写真)をください。

満面の笑みのMOさん

以上です♪
MOさんご協力いただきありがとうございました!

次回もメンバー紹介なのでお楽しみに!

それではまた次回の更新でお会いしましょう★
Take it easy 🙂