前回の記事では、利用者のID、データ・ロール、実行時の属性に基づいて、データベース側で認可ルールを適用する仕組みを紹介しました。
今回は、そのうちデータベース内で管理するローカル・エンドユーザーを使って、利用者とロールによる参照範囲の違いを実際に確かめてみます。

Oracle Deep Data Security(以下、Deep Sec) では、通常のデータベース・ユーザーとは別に、データを利用する主体としてエンドユーザーを扱います。
本記事では、パスワードを持つローカル・エンドユーザーを2人作成し、SQLクライアントから直接接続します。
そして、同じ従業員表への SELECT 文に対して、管理職には自分と部下の行、一般社員には自分の行だけが返るように構成します。


0. はじめに

本記事で実施する内容は以下のとおりです。

項目内容
実施内容検証用の従業員表の作成、ローカル・エンドユーザーの作成、データ・ロールとデータ・グラントの設定、直接接続と表示結果の比較、後片付け
データベースの前提Oracle AI Database 26ai、Release Update(RU)23.26.2以降
※ ここでは 26ai FREE を使用します。
参照手順の実行環境Oracle AI Database 26ai Enterprise Edition 23.26.2、SQLcl 25.4
接続先の例CDB名 ORCLCDB、PDB名とサービス名 ORCLPDB1
使用するツールSQLclまたはSQL*Plus

ドキュメントにも、外部IAMやアプリケーションを用意せずに試せるローカル直接ログオンのクイック・スタート手順が用意されていますので、こちらも参考にしてみてください。

※ ここで作る HR.EMPLOYEES は5行だけの検証用の表で、これまでの連載で使った標準のHRサンプルスキーマとは列構成が異なりますので、既存のHRスキーマを使用する環境とは分けて扱うようにしてください。

この構成では、表を所有する HR と、データを参照する manderson、ebaker を分けます。
HR に表を作成し、2人のエンドユーザーには、業務上の役割に応じた参照範囲を与えます。

構成の概要は以下のようになります。SQLクライアントから直接接続し、データベース内で利用者を認証して、データ・ロールに応じた参照範囲を適用することで、アクセス制御を実現します。


1. 前提の確認

最初に DBA ユーザーで接続し、対象 PDB へ切り替えます。
Deep Data Security を使用するためには COMPATIBLE が 20.0 以上である必要があります。

SQL> sho parameter compatible
NAME              TYPE    VALUE
----------------- ------- ------
compatible        string  23.6.0
noncdb_compatible boolean FALSE

COMPATIBLEはデータの形式に関する互換性の設定で、データベースのRelease Updateを示す 23.26.2 とは異なります。
https://docs.oracle.com/en/database/oracle/oracle-database/26/refrn/COMPATIBLE.html


2. サンプルスキーマとデータの作成

hr.employees 表を作り、5 行のサンプル従業員データを投入します。

-- USERS表領域があるか確認
SQL> select TABLESPACE_NAME from dba_tablespaces;

TABLESPACE_NAME
__________________
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS

万が一ない場合は、USERS表領域を作成しておきます。

CREATE TABLESPACE users
  DATAFILE '<data_file_path>' SIZE 100M
  AUTOEXTEND ON;

HRユーザー(スキーマ)を作成します。
このユーザーは NO AUTHENTICATION として作成し、スキーマ限定ユーザーとして直接ログインしないようにします。
HR は従業員表を所有するためのユーザーで、後で作成するエンドユーザーのログイン先を表すものではありません。

create user hr no AUTHENTICATION
  default tablespace users
  quota unlimited on users;

上の設定では、表の保存先を USERS とし、quota unlimited でこのスキーマの同表領域に対する使用量を制限しない指定にしています。

テーブル作成とサンプルデータを投入します。

-- テーブル作成
CREATE TABLE hr.employees (
    employee_id   NUMBER PRIMARY KEY,
    first_name    VARCHAR2(50),
    last_name     VARCHAR2(50),
    email         VARCHAR2(128),
    manager       VARCHAR2(128),
    ssn           VARCHAR2(20),
    salary        NUMBER(10,2),
    phone         VARCHAR2(20)
);

-- サンプルデータ
INSERT INTO hr.employees VALUES
(100, 'Victoria', 'Williams', 'vwilliams', NULL, '219-09-9999', 13000, '555-0100');
INSERT INTO hr.employees VALUES
(200, 'Marvin', 'Anderson', 'manderson', 'vwilliams', '457-55-5462', 12030, '555-0200');
INSERT INTO hr.employees VALUES
(300, 'Chris', 'Evans', 'cevans', 'vwilliams', '321-12-4567',  6900, '555-0300');
INSERT INTO hr.employees VALUES
(400, 'Emma', 'Baker', 'ebaker', 'manderson', '733-02-9821',  8200, '555-0400');
INSERT INTO hr.employees VALUES
(500, 'Taylor', 'Mills', 'tmills', 'manderson', '558-76-1243', 9000, '555-0500');

COMMIT;

確認します。

SELECT * FROM hr.employees ORDER BY employee_id;
SQL> SELECT * FROM hr.employees ORDER BY employee_id;

   EMPLOYEE_ID FIRST_NAME    LAST_NAME    EMAIL        MANAGER      SSN               SALARY PHONE
______________ _____________ ____________ ____________ ____________ ______________ _________ ___________
           100 Victoria      Williams     vwilliams                 219-09-9999        13000 555-0100
           200 Marvin        Anderson     manderson    vwilliams    457-55-5462        12030 555-0200
           300 Chris         Evans        cevans       vwilliams    321-12-4567         6900 555-0300
           400 Emma          Baker        ebaker       manderson    733-02-9821         8200 555-0400
           500 Taylor        Mills        tmills       manderson    558-76-1243         9000 555-0500

この5行では、EMAIL 列にメールアドレスではなく、利用者を識別する manderson などの値が入っています。
MANAGER 列は上司の識別子になります。400番のEmmaと500番のTaylorは、どちらも MANAGER が manderson なので、管理職の認可を説明する対象になります。

この時点では全5行のSSNに値があることが確認できます。


3. ローカル・エンドユーザーの作成

次に、Deep Data Security 用のローカル・エンドユーザーを2人作ります。
IDENTIFIED BY 以降は、エンドユーザーが直接ログインするためのパスワードを指定します。

コンテクストとして参照する ORA_END_USER_CONTEXT.username と hr.employees.email / manager の値が一致するよう、ユーザー名を manderson と ebaker にすることに注意します。(ここでは小文字に統一します)

CREATE END USER "manderson" IDENTIFIED BY <password>;
CREATE END USER "ebaker" IDENTIFIED BY <password>;

4. Data Role と接続用ロールの作成

ここでは、業務上の役割を表すデータ・ロール(Data Role) と、データベースへの接続権限をまとめる標準ロールを作成します。ローカルエンドユーザーの構成では、Data Role に対して標準ロール経由で CREATE SESSION を与えます。

ここでは employee_role と manager_role を作成し、manderson に両方、ebaker に employee_role のみを付与します。

-- Data Role の作成
CREATE DATA ROLE employee_role;
CREATE DATA ROLE manager_role;

-- 接続用の標準ロール
CREATE ROLE db_role;
GRANT CREATE SESSION TO db_role;

-- ログインに必要な接続権限を付与
GRANT db_role TO employee_role;

-- Data Role を end user に付与
GRANT DATA ROLE manager_role TO "manderson";
GRANT DATA ROLE employee_role TO "manderson";
GRANT DATA ROLE employee_role TO "ebaker";

この付与関係では、2人とも employee_role を通じて db_role の接続権限を利用します。
manager_role は、manderson に管理職としてのデータアクセスを加えるためのロールです。
この段階で与えている標準ロールの権限は CREATE SESSION で、従業員表への参照範囲は次のデータ・グラントで定義します。


5. Data Grant を作成する

employee_role には「自分の行だけ」、manager_role には「自分が manager 列で一致する行だけ、ただし SSN を除外」という2つのデータ・グラント(Data Grant) を作成します。
AS SELECT が許可する操作、ON が対象の表、WHERE が許可する行の条件、TO が許可を受けるデータ・ロールを指定しています。

条件式の ORA_END_USER_CONTEXT.username は、現在のエンドユーザー・セキュリティ・コンテキストから利用者名を取得します。
同じ表への問い合わせでも、manderson と ebaker ではこの値が変わるため、条件を満たす行も変わることになります。

-- employee_role へ自分の email と一致する行の全列を SELECT できる
CREATE OR REPLACE DATA GRANT hr.employees_own_record
  AS SELECT
  ON hr.employees
  WHERE email = ORA_END_USER_CONTEXT.username
  TO employee_role;

-- manager_role へ manager 列が自分と一致する行を SELECT(SSN 列は除く)
CREATE OR REPLACE DATA GRANT hr.manager_direct_reports
  AS SELECT (ALL COLUMNS EXCEPT ssn)
  ON hr.employees
  WHERE manager = ORA_END_USER_CONTEXT.username
  TO manager_role;

複数のデータ・グラントによる許可は、和集合として評価されます。
この例の ALL COLUMNS EXCEPT ssn は、部下の行についてSSN以外の列を許可する指定です。
本人の行への別の許可まで取り消す指定ではないため、管理職も自分のSSNは参照できます。

この2つで manderson は自分のレコードでは SSN を見られますが、和集合になっているため部下である Emma と Taylor の SSN は見えず、ebaker は自分のレコードだけを見られるようになります。


6. アクセスを確認する

接続する際は、<host>:<port>/<service_name> を環境に合わせて指定してください。

manderson で接続する

[oracle@vm-db26 ~]$ sql '"manderson"'/<password>@vm-db26.pubsbnt.koinrtdev.oraclevcn.com:1521/orclpdb1;

SQLcl: Release 25.4 Production on Sun May 03 10:58:57 2026

Copyright (c) 1982, 2026, Oracle.  All rights reserved.

Connected to:
Oracle AI Database 26ai Enterprise Edition Release 23.26.2.0.0 - Production
Version 23.26.2.0.0

SQL>

通常 Oracle Database ではオブジェクト名は作成時に小文字だったとしてもすべて大文字に変換されます。
しかし、オブジェクト作成時にダブルクォート " で囲うことにより、小文字を区別して作成することが可能です。

ここでは manderson などのユーザー名を、コンテキストと合わせるように小文字で作成しているため、小文字であることを明示してログインする必要があります。

そのため、上記では sql '"manderson"'/<password> ... とユーザー名をダブルクォートで囲っていることに注意します。
これがないとユーザー名は大文字で認識されるためログインには失敗するはずです。

hr.employees表を参照します。

SQL> select * from hr.employees;

   EMPLOYEE_ID FIRST_NAME    LAST_NAME    EMAIL        MANAGER      SSN               SALARY PHONE
______________ _____________ ____________ ____________ ____________ ______________ _________ ___________
           200 Marvin        Anderson     manderson    vwilliams    457-55-5462        12030 555-0200
           400 Emma          Baker        ebaker       manderson                        8200 555-0400
           500 Taylor        Mills        tmills       manderson                        9000 555-0500

manderson は employee_role(自分の行)と manager_role(部下の行・SSN 除く)の両方を持つため、3行が返されます。
また、Emma と Taylor の SSN 列は空(NULL)になっていることがわかります。
200番の自分の行は employees_own_record、400番と500番の部下の行は manager_direct_reports による許可で返っています。
元データにはSSNの値があるため、部下の空欄は、そのセルの参照が許可されていないことを表します。
通常の NULL 判定だけでは元データの NULL と区別できないので、ここでは第2節の元データと比較して読み取ります。

エンドユーザーで接続した場合、show user は XS$NULL と表示されます。
実際に接続中のエンドユーザーは ORA_END_USER_CONTEXT.username で確認できます。
XS$NULL の表示だけでは、2人のうちどちらとして認可を受けているかは分かりません。
このため、コンテキストに格納された利用者名が、その後の条件判定を理解する手掛かりになります。

SQL> show user
USER is "XS$NULL"

SQL> SELECT ORA_END_USER_CONTEXT.username FROM dual;

USERNAME
--------------------------------------------------------------------------------
"manderson"

SQL> select ora_end_user_context;

ORA_END_USER_CONTEXT
--------------------------------------------------------------------------------
{"SERVER_HOST":"vm-db26","NLS_DATE_FORMAT":"DD-MON-RR","IS_DG_ROLLING_UPGRADE":"

※ SQLcl(JDBC)では ORA_END_USER_CONTEXT の型 119 をフェッチできずエラーになる場合があります。
失敗した場合は JSON_VALUE で個別フィールドを取得してください。
確認するだけであれば代わりに SQL*Plus を使用しても構いません。

SELECT JSON_VALUE(ORA_END_USER_CONTEXT, '$.USERNAME' RETURNING VARCHAR2(128)) AS username FROM dual;
SQL> SELECT JSON_VALUE(
  2           ORA_END_USER_CONTEXT,
  3           '$.USERNAME'
  4           RETURNING VARCHAR2(128)
  5         ) AS username
  6* FROM dual;

USERNAME
____________
manderson

ebaker で接続する

[oracle@vm-db26 ~]$ sql '"ebaker"'/<password>@vm-db26.pubsbnt.koinrtdev.oraclevcn.com:1521/orclpdb1;

SQLcl: Release 25.4 Production on Sun May 03 11:02:46 2026

Copyright (c) 1982, 2026, Oracle.  All rights reserved.

Connected to:
Oracle AI Database 26ai Enterprise Edition Release 23.26.2.0.0 - Production
Version 23.26.2.0.0

SQL>

ebaker は employee_role のみのため、自分の行 1件だけが返されます。
SSN も自分のレコードなので表示されます。
同じEmmaの400番の行でも、上司の manderson が参照したときにはSSNが空欄になり、本人の ebaker では値が表示されます。
行を参照できるかに加え、その行のどの列を参照できるかも、利用者ごとに評価されていることが分かります。

SQL> SELECT * FROM hr.employees;

   EMPLOYEE_ID FIRST_NAME    LAST_NAME    EMAIL     MANAGER      SSN               SALARY PHONE
______________ _____________ ____________ _________ ____________ ______________ _________ ___________
           400 Emma          Baker        ebaker    manderson    733-02-9821         8200 555-0400

manderson と同様にユーザー名は ORA_END_USER_CONTEXT.username から取得することができます。

SQL> SELECT ORA_END_USER_CONTEXT.username FROM dual;

USERNAME
--------------------------------------------------------------------------------
"ebaker"

ORA_END_USER_CONTEXT の全体を確認する

先ほどは SELECT ORA_END_USER_CONTEXT.username からユーザー名のみを取り出しましたが、参考までに最後にその他に何が取り出せるかの全体を確認します。

JSON ドット記法でアイテムメソッド(.string() / .number() など)を付けない場合、戻り値は SQL の VARCHAR2 ではなく JSON 値になります。
ここでは JSON_SERIALIZE を使うと全体を整形して表示します。

SELECT JSON_SERIALIZE(
         ORA_END_USER_CONTEXT
         RETURNING VARCHAR2(4000)
         PRETTY
       ) AS ctx
FROM dual;
SQL> SELECT JSON_SERIALIZE(
  2           ORA_END_USER_CONTEXT
  3           RETURNING VARCHAR2(4000)
  4           PRETTY
  5         ) AS ctx
  6* FROM dual;

CTX
_________________________________________________________
{
  "SERVER_HOST" : "vm-db26",
  "NLS_DATE_FORMAT" : "DD-MON-RR",
  "IS_DG_ROLLING_UPGRADE" : "FALSE",
  "CURRENT_USER" : "XS$NULL",
  "HOST" : "vm-db26",
  "NLS_CURRENCY" : "$",
  "INSTANCE_NAME" : "ORCLCDB",
  "NLS_DATE_LANGUAGE" : "AMERICAN",
  "PID" : "97",
  "NLS_TERRITORY" : "AMERICA",
  "SCHEDULER_JOB" : "N",
  "AUTHENTICATION_METHOD" : "PASSWORD",
  "DB_NAME" : "ORCLPDB1",
  "UNIFIED_AUDIT_SESSIONID" : "439269382221329887",
  "LANG" : "US",
  "ISDBA" : "FALSE",
  "OS_USER" : "oracle",
  "AUTHENTICATED_IDENTITY" : "ebaker",
  "MODULE" : "java@vm-db26 (TNS V1-V3)",
  "ORACLE_HOME" : "/opt/oracle/product/26ai/dbhome_2",
  "CURRENT_SCHEMA" : "XS$NULL",
  "CURRENT_USERID" : "2147483638",
  "NETWORK_PROTOCOL" : "tcp",
  "IP_ADDRESS" : "10.0.0.104",
  "CLIENT_PROGRAM_NAME" : "java@vm-db26 (TNS V1-V3)",
  "SID" : "300",
  "LOGON_END_USER" : "ebaker",
  "SESSION_EDITION_ID" : "140",
  "NLS_SORT" : "BINARY",
  "CURRENT_END_USER" : "ebaker",
  "SERVICE_NAME" : "orclpdb1",
  "CDB_NAME" : "ORCLCDB",
  "CON_ID" : "3",
  "INSTANCE" : "1",
  "SESSION_USERID" : "2147483638",
  "SESSION_USER" : "XS$NULL",
  "CLOUD_MIGRATION_MODE" : "OFF",
  "CURRENT_EDITION_NAME" : "ORA$BASE",
  "TERMINAL" : "pts/3",
  "USERNAME" : "ebaker",
  "DATABASE_ROLE" : "PRIMARY",
  "LANGUAGE" : "AMERICAN_AMERICA.AL32UTF8",
  "SESSION_EDITION_NAME" : "ORA$BASE",
  "NLS_CALENDAR" : "GREGORIAN",
  "IDENTIFICATION_TYPE" : "XS",
  "DB_UNIQUE_NAME" : "ORCLCDB",
  "CON_NAME" : "ORCLPDB1",
  "PLATFORM_SLASH" : "/",
  "IS_APPLY_SERVER" : "FALSE",
  "SESSIONID" : "80096",
  "DRAIN_STATUS" : "NONE",
  "CURRENT_SCHEMAID" : "2147483638",
  "GLOBAL_CONTEXT_MEMORY" : "0",
  "RESET_STATE" : "NONE",
  "CURRENT_EDITION_ID" : "140",
  "FG_JOB_ID" : "0"
}

-- 個別のコンテクストを取り出すこともできる
SQL> select ora_end_user_context.lang;

LANG
--------------------------------------------------------------------------------
"US"

全体の出力でも、USERNAME と CURRENT_END_USER が ebaker、AUTHENTICATION_METHOD が PASSWORD と表示されています。
今回のデータ・グラントが認可条件で参照しているのは、この利用者名になっています。

https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/ora_end_user_context.html


7. クリーンアップ

最後に作成したエンドユーザー・スキーマ・Data Role・ロールを削除します。
以下の DROP USER hr CASCADE は、hr.employees の表とデータを含めてHRスキーマを削除するようになっています。

DROP END USER "manderson";
DROP END USER "ebaker";
DROP USER hr CASCADE;
DROP DATA ROLE employee_role;
DROP DATA ROLE manager_role;
DROP ROLE db_role;

8. まとめ

今回は、ローカル・エンドユーザーを使ってDeep Secへ直接接続する手順を紹介しました。
管理職には3行、一般社員には1行を返し、同じ従業員のSSNも参照する利用者によって表示が変わる構成です。

本記事で扱ったように、外部の認証基盤を用意する前に、従来と似たような利用感でデータ・ロールとデータ・グラントで必要な認可制御を試してみることも可能です。

続く次の記事では、この認可ルールを使い、利用者とグループを外部の認証基盤で管理する場合の接続を構成をしてみます。