カテゴリー: 技術情報

AIです。（Artificial Intelligence ではありません）

トロントは夏まっさかり！AI も Brewery 巡りや Summerlicious とカナダの夏を楽しんでおります！※ Summerlicious とは何ぞや？という方は是非「カナダde FOOD ADVENTURE ⑨」の記事をご覧ください

カナダde FOOD ADVENTURE ⑨ Summerlicious 2023 編

さて、前回の宣言通り今回は SQL ファイアウォールの違反ログについて見ていきましょう。

■違反ログとは

違反ログは SQL ファイアウォール違反を記録するログです。例えば、SQLファイアウォールを設定したユーザーが許可リストにない SQL を実行した際に実行された SQL や時間、ユーザー情報、その SQL の実行を許可したかどうかが記録されます。また、前回は、ユーザーのキャプチャ・ログに記録された SQL から許可リストを作成しましたがSQL ファイアウォールでは、違反ログから許可リストに SQL を追加することもできます。

SQL ファイアウォールの違反ログの中身は DBA_SQL_FIREWALL_VIOLATIONS から確認することができます。

Oracle Database
データベース・リファレンス
23c
6.416 DBA_SQL_FIREWALL_VIOLATIONS
https://docs.oracle.com/cd/F82042_01/refrn/DBA_SQL_FIREWALL_VIOLATIONS.html#GUID-855FF31F-7F89-4667-B790-4AD157EA00B5

※ DBA_SQL_FIREWALL_VIOLATIONS の SQL_TEXT 列は最大1000文字しか表示されません。完全な SQL 文を表示させたい場合は、DBA_SQL_FIREWALL_SQL_LOGS の SQL_TEXT 列を参照します。

■ブロックと違反ログへの記録

許可リストにない SQL を実行した際、違反ログにその情報が記録されますが違反している SQL を実行するかブロックするかは設定で変えることが可能です。

では、実際に試してみましょう。以下は現在の Goat ユーザーの許可リストです。許可リストの、BLOCK は N(FALSE) となっています。

SQL> SELECT username,allowed_sql_id,sql_text FROM DBA_SQL_FIREWALL_ALLOWED_SQL;

USERNAME   ALLOWED_SQL_ID SQL_TEXT
---------- -------------- ----------------------------------------------------------------------------------------------------
GOAT                    1 SELECT * FROM GOAT.HOUSE WHERE FURNITURE=:"SYS_B_0"
GOAT                    2 SELECT * FROM GOAT.HOUSE WHERE FURNITURE=:"SYS_B_0" OR FURNITURE=:"SYS_B_1"


SQL> SELECT username,status,block FROM DBA_SQL_FIREWALL_ALLOW_LISTS;

USERNAME   STATUS   BLOCK
---------- -------- --------------
GOAT       ENABLED  N ★

この状態で許可リストにない SQL を実行してみます。BLOCK は N でしたので実行自体はできました。

SQL> SELECT * FROM Goat.house WHERE furniture IN ('Wall Clock','Bed');

FURNITURE
---------------
Wall Clock
Bed

ここで違反ログを確認してみると、先ほど実行した SQL が記録されているのがわかります。

SQL> SELECT username,sql_text,cause,firewall_action,occurred_at FROM DBA_SQL_FIREWALL_VIOLATIONS;

USERNAME   SQL_TEXT                                                                                             CAUSE             FIREWAL   OCCURRED_AT                      
---------- ---------------------------------------------------------------------------------------------------- ----------------- -------   ---------------------------------
GOAT       SELECT * FROM GOAT.HOUSE WHERE FURNITURE IN (:"SYS_B_0",:"SYS_B_1")★                                SQL violation     Allowed★ 24-08-14 15:11:13.793419 -04:00
GOAT       SELECT DECODE (USER,:"SYS_B_0",XS_SYS_CONTEXT (:"SYS_B_1",:"SYS_B_2"),USER) FROM SYS.DUAL            SQL violation     Allowed   24-08-14 15:02:41.213732 -04:00

続いて、BLOCK を Y(TRUE) に変更して違う SQL を実行してみます。許可リストのブロックの設定を変更する際は DBMS_SQL_FIREWALL.UPDATE_ALLOW_LIST_ENFORCEMENT を使用します。

SQL> BEGIN
  2    DBMS_SQL_FIREWALL.UPDATE_ALLOW_LIST_ENFORCEMENT (
  3      username       => 'GOAT',
  4      enforce        => DBMS_SQL_FIREWALL.ENFORCE_SQL,
  5      block          => TRUE ★
  6     );
  7  END;
  8  /

PL/SQLプロシージャが正常に完了しました。


SQL> SELECT username,status,block FROM DBA_SQL_FIREWALL_ALLOW_LISTS;

USERNAME   STATUS   BLOCK
---------- -------- --------------
GOAT       ENABLED  Y ★

BLOCK を Y(TRUE) にすることで、SQL の実行が拒否されました。

SQL> SELECT * FROM Goat.house;
SELECT * FROM Goat.house
                   *
行1でエラーが発生しました。:
ORA-47605: SQLファイアウォール違反 ヘルプ: ★
https://docs.oracle.com/error-help/db/ora-47605/

違反ログの FIREWALL_ACTION 列を見ると Blocked と出力されており、SQL の実行が拒否されたことがわかります。

SQL> SELECT username,sql_text,cause,firewall_action,occurred_at FROM DBA_SQL_FIREWALL_VIOLATIONS;

USERNAME   SQL_TEXT                                                                                             CAUSE             FIREWAL   OCCURRED_AT
---------- ---------------------------------------------------------------------------------------------------- ----------------- -------   ---------------------------------
GOAT       SELECT * FROM GOAT.HOUSE WHERE FURNITURE IN (:"SYS_B_0",:"SYS_B_1")                                  SQL violation     Allowed   24-08-14 15:11:13.793419 -04:00
GOAT       SELECT * FROM GOAT.HOUSE★                                                                           SQL violation     Blocked★ 24-08-14 15:31:34.987688 -04:00
GOAT       SELECT DECODE (USER,:"SYS_B_0",XS_SYS_CONTEXT (:"SYS_B_1",:"SYS_B_2"),USER) FROM SYS.DUAL            SQL violation     Allowed   24-08-14 15:02:41.213732 -04:00

■違反ログから許可リストに SQL を追加

次に違反ログに記録された SQL を許可リストに追加します。許可リストへの追加には APPEND_ALLOW_LISTプロシージャを使用します。今回は違反ログから追加するため、source に DBMS_SQL_FIREWALL.VIOLATION_LOG を指定します。

SQL> BEGIN
  2    DBMS_SQL_FIREWALL.APPEND_ALLOW_LIST (
  3      username       => 'GOAT',
  4      source         => DBMS_SQL_FIREWALL.VIOLATION_LOG
  5     );
  6  END;
  7  /

PL/SQLプロシージャが正常に完了しました。

SQL> SELECT username,allowed_sql_id,sql_text FROM DBA_SQL_FIREWALL_ALLOWED_SQL;

USERNAME   ALLOWED_SQL_ID SQL_TEXT
---------- -------------- ----------------------------------------------------------------------------------------------------
GOAT                    1 SELECT * FROM GOAT.HOUSE WHERE FURNITURE=:"SYS_B_0"
GOAT                    2 SELECT * FROM GOAT.HOUSE WHERE FURNITURE=:"SYS_B_0" OR FURNITURE=:"SYS_B_1"
GOAT                    3 SELECT DECODE (USER,:"SYS_B_0",XS_SYS_CONTEXT (:"SYS_B_1",:"SYS_B_2"),USER) FROM SYS.DUAL
GOAT                    4 SELECT * FROM GOAT.HOUSE WHERE FURNITURE IN (:"SYS_B_0",:"SYS_B_1")
GOAT                    5 SELECT * FROM GOAT.HOUSE

違反として記録された SQL が許可リストに追加されました！とはいえ、すべての違反 SQL を追加したいわけではない、というケースもありますよね。許可リストに入れたくない SQL は DBMS_SQL_FIREWALL.DELETE_ALLOWED_SQL を使って削除しましょう。

SQL> BEGIN
  2    DBMS_SQL_FIREWALL.DELETE_ALLOWED_SQL (
  3      username         => 'GOAT',
  4      allowed_sql_id   => 5
  5     );
  6  END;
  7  /

PL/SQLプロシージャが正常に完了しました。

SQL> SELECT username,allowed_sql_id,sql_text FROM DBA_SQL_FIREWALL_ALLOWED_SQL;

USERNAME   ALLOWED_SQL_ID SQL_TEXT
---------- -------------- ----------------------------------------------------------------------------------------------------
GOAT                    1 SELECT * FROM GOAT.HOUSE WHERE FURNITURE=:"SYS_B_0"
GOAT                    2 SELECT * FROM GOAT.HOUSE WHERE FURNITURE=:"SYS_B_0" OR FURNITURE=:"SYS_B_1"
GOAT                    3 SELECT DECODE (USER,:"SYS_B_0",XS_SYS_CONTEXT (:"SYS_B_1",:"SYS_B_2"),USER) FROM SYS.DUAL
GOAT                    4 SELECT * FROM GOAT.HOUSE WHERE FURNITURE IN (:"SYS_B_0",:"SYS_B_1")

■違反ログのレコード削除

違反ログがたまってきたら、DBMS_SQL_FIREWALL.PURGE_LOG でログを削除しましょう。DBMS_SQL_FIREWALL.PURGE_LOG で指定したログの指定した時間より前のレコードを削除することができます。

SQL> BEGIN
  2    DBMS_SQL_FIREWALL.PURGE_LOG (
  3      username    => 'GOAT',
  4      purge_time  => TO_TIMESTAMP('2024-08-14 15:30:00', 'YYYY-MM-DD HH24:MI:SS'),
  5      log_type    => DBMS_SQL_FIREWALL.VIOLATION_LOG
  6     );
  7  END;
  8  /

PL/SQLプロシージャが正常に完了しました。


SQL> SELECT username,sql_text,cause,firewall_action,occurred_at FROM DBA_SQL_FIREWALL_VIOLATIONS;

USERNAME   SQL_TEXT                                                                                             CAUSE             FIREWAL OCCURRED_AT
---------- ---------------------------------------------------------------------------------------------------- ----------------- ------- ---------------------------------
GOAT       SELECT * FROM GOAT.HOUSE                                                                             SQL violation     Blocked 24-08-14 15:31:34.987688 -04:00

■まとめ

今回は、SQL ファイアウォールの違反ログについて見てきましたがいかがでしたでしょうか。

SQL ファイアウォールの違反ログは、実行タイミングや SQL 文、OS ユーザー、IP アドレスなども記録されますので仮に不正な SQL が実行されてしまったとしても、違反ログからタイミングや実行 SQL を特定することが可能です。不正 SQL の実行を防止するだけでなく、検知・後追いもできるところが SQL ファイアウォールの強みになりそうですね。

今後も Oracle Database 23ai の新機能について掘り下げていく予定ですので、次回の記事をお楽しみに！

AIです。（Artificial Intelligence ではありません）
トロントは初夏にさしかかり、半袖で過ごせる日も多くなってきました。晴れているとインドア派な AI でさえ、でかけたくなるくらいのお散歩日和となります。実はトロントでは人だけではなく、カナダガン（カナダグース）も街を散歩していたりします。道端で遭遇するとびっくりするくらいには大きいですよ。カナダガン。

さて、前回に引き続き、Oracle Database 23ai の新機能を触っていきたいと思います。今回フィーチャーする機能は「SQL ファイアウォール」です！SQL ファイアウォールは、事前にユーザーごとの SQL の許可リストを作成し、許可リストにない SQL の実行をブロックしたりロギングしたりすることができる機能です。

■設定

今回は goat ユーザーに対して SQL ファイアウォールを設定します。設定は以下の流れで行います。

1. SQL ファイアウォールの有効化
2. キャプチャの作成・有効化
3. SQL のキャプチャ
4. キャプチャの無効化
5. 許可リストの生成
6. 許可リストの有効化

1.SQL ファイアウォールの有効化

現在の SQL ファイアウォールの状態は以下の SQL で確認できます。現在は無効となっていますね。

SQL> SELECT * FROM DBA_SQL_FIREWALL_STATUS;

STATUS   STATUS_UPDATED_ON                                                           EXCLUDE_JOBS
-------- --------------------------------------------------------------------------- ------------
DISABLED 24-05-21 11:35:40.636036 -04:00                                             Y

以下のコマンドで SQL ファイアウォールを有効化します。

SQL> EXEC DBMS_SQL_FIREWALL.ENABLE;

PL/SQLプロシージャが正常に完了しました。

SQL> SELECT * FROM DBA_SQL_FIREWALL_STATUS;

STATUS   STATUS_UPDATED_ON                                                           EXCLUDE_JOBS
-------- --------------------------------------------------------------------------- ------------
ENABLED  24-05-22 11:54:26.253441 -04:00                                             Y

2.キャプチャの作成・有効化

続いて、goat ユーザーのキャプチャを作成します。

SQL> BEGIN
  DBMS_SQL_FIREWALL.CREATE_CAPTURE (
    username         => 'GOAT',
    top_level_only   => TRUE,
    start_capture    => TRUE
  );
END;
/  2    3    4    5    6    7    8

PL/SQLプロシージャが正常に完了しました。

SQL> SELECT username,status FROM dba_sql_firewall_captures;

USERNAME   STATUS
---------- --------
GOAT       ENABLED

3.SQL のキャプチャ

キャプチャ・ログに SQL を登録するため、goat ユーザーで以下の SQL を実行します。

SQL> SELECT * FROM Goat.house;

FURNITURE
--------------------------------------------------------------------------------
Wall Clock
Table
Bed

SQL> SELECT * FROM Goat.house WHERE furniture = 'Wall Clock';

FURNITURE
--------------------------------------------------------------------------------
Wall Clock

SQL がキャプチャされたかをキャプチャ・ログから確認。先ほど実行した SQL がキャプチャ・ログに登録されていますね。登録された SQL の WHERE 句の条件の値はバインド変数に置換されて登録されているのがわかります。

SQL> SELECT SQL_TEXT FROM DBA_SQL_FIREWALL_CAPTURE_LOGS WHERE USERNAME = 'GOAT';

SQL_TEXT
---------------------------------------------------------------------------------------------
SELECT DECODE (USER,:"SYS_B_0",XS_SYS_CONTEXT (:"SYS_B_1",:"SYS_B_2"),USER) FROM SYS.DUAL
SELECT * FROM GOAT.HOUSE
SELECT * FROM GOAT.HOUSE WHERE FURNITURE=:"SYS_B_0"

4.キャプチャの無効化

きちんとキャプチャがとれていることが確認できたので、キャプチャを無効化します。

SQL> EXEC DBMS_SQL_FIREWALL.STOP_CAPTURE ('GOAT');

PL/SQLプロシージャが正常に完了しました。

SQL> col username for a10
SQL> SELECT username,status FROM dba_sql_firewall_captures;

USERNAME   STATUS
---------- --------
GOAT       DISABLED

5.許可リストの生成

以下のプロシージャを実行して、既存のキャプチャ・ログから許可リストを生成します。

SQL> EXEC DBMS_SQL_FIREWALL.GENERATE_ALLOW_LIST ('GOAT');

PL/SQLプロシージャが正常に完了しました。

生成された許可リストの一覧や状態は DBA_SQL_FIREWALL_ALLOW_LISTS から確認できます。

SQL> SELECT username,status,block FROM DBA_SQL_FIREWALL_ALLOW_LISTS
  2  WHERE username='GOAT';

USERNAME   STATUS   BLOCK
---------- -------- --------------
GOAT       DISABLED N

許可リストに登録された SQL は DBA_SQL_FIREWALL_ALLOWED_SQL から確認します。

SQL> SELECT username,allowed_sql_id,sql_text FROM DBA_SQL_FIREWALL_ALLOWED_SQL
  2  WHERE username='GOAT';

USERNAME   ALLOWED_SQL_ID SQL_TEXT
---------- -------------- ----------------------------------------------------------------------------------------------------
GOAT                    1 SELECT DECODE (USER,:"SYS_B_0",XS_SYS_CONTEXT (:"SYS_B_1",:"SYS_B_2"),USER) FROM SYS.DUAL
GOAT                    2 SELECT * FROM GOAT.HOUSE
GOAT                    3 SELECT * FROM GOAT.HOUSE WHERE FURNITURE=:"SYS_B_0"

個人的に気を付けたいポイントとして、ALLOWED_SQL_ID が AWR レポートや v$session で SQL の特定に使用する SQL_ID とは異なり、ユーザーごとに一意の ID となっているところですね。項目だけ見ると一瞬いつもの SQL_ID かなと思ってしまいそうなので…

6.許可リストの有効化

最後に許可リストを有効化して、設定は完了です！

SQL> BEGIN
  DBMS_SQL_FIREWALL.ENABLE_ALLOW_LIST (
    username       => 'GOAT',
    enforce        => DBMS_SQL_FIREWALL.ENFORCE_SQL,
    block          => TRUE
   );
END;
/  2    3    4    5    6    7    8

PL/SQLプロシージャが正常に完了しました。

SQL> SELECT username,status,block FROM DBA_SQL_FIREWALL_ALLOW_LISTS
  2  WHERE username='GOAT';

USERNAME   STATUS   BLOCK
---------- -------- --------------
GOAT       ENABLED  Y

■許可された SQL の実行 / 許可されていない SQL の実行

では早速、GOAT ユーザーで色々な SQL を実行してみましょう。まずは、許可リストにある SQL から。これは実行できますね。

SQL> SELECT * FROM Goat.house;

FURNITURE
--------------------------------------------------------------------------------
Wall Clock
Table
Bed

SQL> SELECT * FROM Goat.house WHERE furniture = 'Wall Clock';

FURNITURE
--------------------------------------------------------------------------------
Wall Clock

続いて、許可リストにない SQL を実行してみましょう。WHERE 句で指定する条件の数を変えたり、IN 条件に変えたりすると ORA-47605 が発生して実行できませんでした。

SQL> SELECT * FROM Goat.house WHERE furniture = 'Wall Clock' OR furniture='Table';
SELECT * FROM Goat.house WHERE furniture = 'Wall Clock' OR furniture='Table'
                   *
行1でエラーが発生しました。:
ORA-47605: SQLファイアウォール違反 ヘルプ:
https://docs.oracle.com/error-help/db/ora-47605/


SQL> SELECT * FROM Goat.house WHERE furniture IN ('Wall Clock','Bed');
SELECT * FROM Goat.house WHERE furniture IN ('Wall Clock','Bed')
                   *
行1でエラーが発生しました。:
ORA-47605: SQLファイアウォール違反 ヘルプ:
https://docs.oracle.com/error-help/db/ora-47605/

一方で、条件の指定の仕方は同じで値だけ異なる場合には実行できます。許可リストの検索条件の値はバインド変数で登録されていましたしね。

SQL> SELECT * FROM Goat.house WHERE furniture = 'Table';

FURNITURE
--------------------------------------------------------------------------------
Table

つまり、[OR ‘1’=’1′] と余計な条件をつけた SQL インジェクション的な SQL は当然ブロックされるというわけですね…！

SQL> SELECT * FROM Goat.house WHERE furniture = 'Table' OR '1'='1';
SELECT * FROM Goat.house WHERE furniture = 'Table' OR '1'='1'
                   *
行1でエラーが発生しました。:
ORA-47605: SQLファイアウォール違反 ヘルプ:
https://docs.oracle.com/error-help/db/ora-47605/

■まとめ

SQL ファイアウォールの設定・ブロック編、いかがでしたでしょうか。設定の際には実際に SQL を実行する必要がありますが、一度設定してしまえば許可した SQL 以外は実行できない、というのはセキュリティのリスク軽減に大きく貢献すると思います。

今回は SQL の許可リストを設定しましたが IP アドレスやプログラム名など実行ユーザーに紐づいた情報に対しても許可リストを作ることができます。

また SQL ファイアウォールは SQL の実行をブロックするだけでなく、違反した SQL をログから確認する機能もあります。ログの確認については次回の記事で触れる予定ですのでお楽しみに！

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

表が作成されました。

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

■まとめ

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

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