続けて、上記で作成したサンプルのリモートMCPサーバーsampleserverを、Microsoft Entra IDをIdPとしてOpen ID Connect認証できるようにします。Entra IDのアプリとしてORDS MCPおよびORDS MCP Clientを作成します。
- employee_in_same_department: サインインしたユーザーと同じ部門にアクセスを限定する
- employee_is_manager: サインインしたユーザーがマネージャーである従業員の列SALARYとCOMMISSION_PCTにアクセスを限定する
管理ユーザーVPDADMINの作成と各種権限の付与
create user vpdadmin identified by <パスワード>;
alter user vpdadmin quota 25m on data;
grant create session to vpdadmin;
grant create table to vpdadmin;
オンプレミスではユーザーVPDADMINのデフォルト表領域はDATAではない（主にUSERS）場合が多いため、クオータの割り当て先は変更する必要があります。
SQL> create user vpdadmin identified by *********;
User VPDADMINは作成されました。
SQL> alter user vpdadmin quota 25m on data;
User VPDADMINが変更されました。
SQL> grant create session to vpdadmin;
Grantが正常に実行されました。
SQL> grant create table to vpdadmin;
Grantが正常に実行されました。
SQL>
VPDADMINに、仮想プラベート・データベースを構成するために必要な権限を与えます。
grant create any context to vpdadmin;
grant execute on dbms_rls to vpdadmin;
grant execute on dbms_session to vpdadmin;
grant create procedure to vpdadmin;
SQL> grant create any context to vpdadmin;
Grantが正常に実行されました。
SQL> grant execute on dbms_rls to vpdadmin;
Grantが正常に実行されました。
SQL> grant execute on dbms_session to vpdadmin;
Grantが正常に実行されました。
SQL> grant create procedure to vpdadmin;
Grantが正常に実行されました。
SQL>
26aiの場合は、権限ADMINISTER ROW LEVEL SECURITY POLICYも必要です。
grant administer row level security policy to vpdadmin;
リモートMCPサーバーを実装しているユーザーWKSP_APEXDEVに、サンプル・スキーマhuman_resourcesの読み取り権限を追加します。
create role hr_role;
grant select on hr.departments to hr_role;
grant select on hr.employees to hr_role;
grant select on hr.jobs to hr_role;
grant select on hr.job_history to hr_role;
grant select on hr.locations to hr_role;
grant select on hr.regions to hr_role;
grant select on hr.countries to hr_role;
grant select on hr.emp_details_view to hr_role;
grant hr_role to wksp_apexdev;
alter user wksp_apexdev default role hr_role;SQL> create role hr_role;
Role HR_ROLEは作成されました。
SQL> grant select on hr.departments to hr_role;
Grantが正常に実行されました。
SQL> grant select on hr.employees to hr_role;
Grantが正常に実行されました。
SQL> grant select on hr.jobs to hr_role;
Grantが正常に実行されました。
SQL> grant select on hr.job_history to hr_role;
Grantが正常に実行されました。
SQL> grant select on hr.locations to hr_role;
Grantが正常に実行されました。
SQL> grant select on hr.regions to hr_role;
Grantが正常に実行されました。
SQL> grant select on hr.countries to hr_role;
Grantが正常に実行されました。
SQL> grant select on hr.emp_details_view to hr_role;
Grantが正常に実行されました。
SQL> grant hr_role to wksp_apexdev;
Grantが正常に実行されました。
SQL> alter user wksp_apexdev default role hr_role;
User WKSP_APEXDEVが変更されました。
SQL>
@mcp-app/vpd-setup/auth_users.sql
SQL> @mcp-app/vpd-setup/auth_users.sql
SQL> create table auth_users(
2 employee_id number not null,
3 department_id number not null,
4 email varchar2(25),
5 authenticated_identity varchar2(128) not null
6 );
Table AUTH_USERSは作成されました。
SQL>
@mcp-app/vpd-setup/create-vpd-prot.sql wksp_apexdev
SQL> @mcp-app/vpd-setup/create-vpd-prot.sql wksp_apexdev
SQL> set serveroutput on
SQL> set echo on
SQL>
SQL> define SCHEMA = &1
SQL>
SQL> /*
SQL> * create context emp_dept_ctx
SQL> */
SQL> create or replace context emp_dept_ctx using oj_mcp_vpd_config;
Context EMP_DEPT_CTXは作成されました。
SQL> -- create a package to manage context.
SQL> @@../src/vpd/oj_mcp_vpd_config.pks
SQL> create or replace package oj_mcp_vpd_config
2 as
3
4 procedure init(
5 p_current_user in varchar2
6 );
7
8 end;
9 /
Package OJ_MCP_VPD_CONFIGがコンパイルされました
SQL> @@../src/vpd/oj_mcp_vpd_config.pkb
SQL> create or replace package body oj_mcp_vpd_config
2 as
3
4 G_NAMESPACE constant varchar2(80) := 'emp_dept_ctx';
5
6 procedure init(
7 p_current_user in varchar2
8 )
9 as
10 l_employee_id auth_users.employee_id%type;
11 l_department_id auth_users.department_id%type;
12 begin
13 select employee_id, department_id into l_employee_id, l_department_id
14 from auth_users
15 where authenticated_identity = p_current_user;
16 /*
17 * Set employee_id and department_id to the application context.
18 */
19 dbms_session.set_context(G_NAMESPACE,'employee_id', l_employee_id);
20 dbms_session.set_context(G_NAMESPACE,'department_id',l_department_id);
21 exception
22 when no_data_found then
23 -- context initialized with null
24 dbms_session.set_context(G_NAMESPACE,'employee_id', null);
25 dbms_session.set_context(G_NAMESPACE,'department_id',null);
26 when others then
27 -- empty app context
28 raise;
29 end init;
30
31 end oj_mcp_vpd_config;
32 /
Package Body OJ_MCP_VPD_CONFIGがコンパイルされました
SQL> --
SQL> grant execute on oj_mcp_vpd_config to &SCHEMA;
旧:grant execute on oj_mcp_vpd_config to &SCHEMA
新:grant execute on oj_mcp_vpd_config to wksp_apexdev
Grantが正常に実行されました。
SQL>
SQL> /*
SQL> * Create policy functions.
SQL> */
SQL> @@../src/vpd/pred_employee_in_same_department.pls
SQL> CREATE OR REPLACE FUNCTION pred_employee_in_same_department
2 (
3 schema_p IN VARCHAR2,
4 table_p IN VARCHAR2
5 )
6 RETURN VARCHAR2
7 AS
8 pred VARCHAR2(80);
9 BEGIN
10 pred := q'~department_id = SYS_CONTEXT('emp_dept_ctx','department_id')~';
11 RETURN pred;
12 END;
13 /
Function PRED_EMPLOYEE_IN_SAME_DEPARTMENTがコンパイルされました
SQL> @@../src/vpd/pred_employee_is_manager.pls
SQL> CREATE OR REPLACE FUNCTION pred_employee_is_manager
2 (
3 schema_p IN VARCHAR2,
4 table_p IN VARCHAR2
5 )
6 RETURN VARCHAR2
7 AS
8 pred VARCHAR2(80);
9 BEGIN
10 pred := q'~manager_id = SYS_CONTEXT('emp_dept_ctx', 'employee_id')~';
11 RETURN pred;
12 END;
13 /
Function PRED_EMPLOYEE_IS_MANAGERがコンパイルされました
SQL>
SQL> /*
SQL> * Create and apply VPD policy.
SQL> */
SQL> begin
2 dbms_rls.add_policy(
3 object_schema => 'hr'
4 , object_name => 'employees'
5 , policy_name => 'employee_is_manager'
6 , function_schema => 'vpdadmin'
7 , policy_function => 'pred_employee_is_manager'
8 , statement_types => 'select'
9 , policy_type => DBMS_RLS.CONTEXT_SENSITIVE
10 , sec_relevant_cols => 'salary,commission_pct'
11 , sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS
12 , namespace => 'emp_dept_ctx'
13 , attribute => 'employee_id'
14 );
15 end;
16 /
PL/SQLプロシージャが正常に完了しました。
SQL>
SQL> begin
2 dbms_rls.add_policy(
3 object_schema => 'hr'
4 , object_name => 'employees'
5 , policy_name => 'employee_in_same_department'
6 , function_schema => 'vpdadmin'
7 , policy_function => 'pred_employee_in_same_department'
8 , statement_types => 'select'
9 , policy_type => DBMS_RLS.CONTEXT_SENSITIVE
10 , namespace => 'emp_dept_ctx'
11 , attribute => 'department_id'
12 );
13 end;
14 /
PL/SQLプロシージャが正常に完了しました。
SQL>
表AUTH_USERSにユーザーの対応を作成します。
insert into wksp_apexdev.auth_users(employee_id, department_id, email, authenticated_identity) values(103,60,'AJAMES','Entra IDのユーザーのID');
commit;
SQL> insert into auth_users(employee_id, department_id, email, authenticated_identity) values(103,60,'AJAMES','****************************');
1行挿入しました。
SQL> commit;
コミットが完了しました。
SQL>
スキーマWKSP_APEXDEVの構成
リモートMCPサーバーのリクエストを受け付けるORDSのPOSTハンドラにて、実際の処理を始める前に仮想プライベート・データベースで参照するアプリケーション・コンテキストを初期化するようにします。
create or replace procedure oj_mcp_vpd_post_handler
(
p_body in blob,
p_current_user in varchar2,
p_status_code out number
)
as
l_scope logger_logs.scope%type := 'oj_mcp_vpd_post_handler';
begin
logger.log_info('Enter VPD POST Handler', l_scope);
-- Suppress compile-time errors.
execute immediate 'begin vpdadmin.oj_mcp_vpd_config.init(:1); end;' using p_current_user;
-- After initializing the context for VPD, perform the standard POST processing.
oj_mcp_post_handler(p_body, p_current_user, p_status_code);
logger.log_info('Leave VPD POST Handler');
end oj_mcp_vpd_post_handler;
/begin
ords.define_handler(
p_module_name => 'sampleserver',
p_pattern => 'mcp',
p_method => 'POST',
p_source_type => 'plsql/block',
p_source => 'begin oj_mcp_vpd_post_handler(:body,:current_user,:status_code); end;'
);
end;
/SQL> begin
2 ords.define_handler(
3 p_module_name => 'sampleserver',
4 p_pattern => 'mcp',
5 p_method => 'POST',
6 p_source_type => 'plsql/block',
7 p_source => 'begin oj_mcp_vpd_post_handler(:body,:current_user,:status_code); end;'
8 );
9 end;
10* /
PL/SQLプロシージャが正常に完了しました。
SQL>
動作確認
begin
ords.define_handler(
p_module_name => 'sampleserver',
p_pattern => 'mcp',
p_method => 'POST',
p_source_type => 'plsql/block',
p_source => 'begin oj_mcp_post_handler(:body,:current_user,:status_code); end;'
);
end;
/@mcp-app/vpd-setup/delete-vpd-prot.sql
SQL> @mcp-app/vpd-setup/delete-vpd-prot.sql
SQL>
SQL> begin
2 dbms_rls.drop_policy(
3 object_schema => 'hr'
4 ,object_name => 'employees'
5 ,policy_name => 'employee_in_same_department'
6 );
7 dbms_rls.drop_policy(
8 object_schema => 'hr'
9 ,object_name => 'employees'
10 ,policy_name => 'employee_is_manager'
11 );
12 end;
13 /
PL/SQLプロシージャが正常に完了しました。
SQL>
SQL> drop function pred_employee_in_same_department;
Function PRED_EMPLOYEE_IN_SAME_DEPARTMENTが削除されました。
SQL> drop function pred_employee_is_manager;
Function PRED_EMPLOYEE_IS_MANAGERが削除されました。
SQL>
SQL> drop package oj_mcp_vpd_config;
Package OJ_MCP_VPD_CONFIGが削除されました。
SQL> -- drop context emp_dept_ctx;
SQL>
@mcp-app/vpd-setup/create-vpd-prot.sql wksp_apexdev
begin
ords.define_handler(
p_module_name => 'sampleserver',
p_pattern => 'mcp',
p_method => 'POST',
p_source_type => 'plsql/block',
p_source => 'begin oj_mcp_vpd_post_handler(:body,:current_user,:status_code); end;'
);
end;
/SQL> begin
2 ords.define_handler(
3 p_module_name => 'sampleserver',
4 p_pattern => 'mcp',
5 p_method => 'POST',
6 p_source_type => 'plsql/block',
7 p_source => 'begin oj_mcp_vpd_post_handler(:body,:current_user,:status_code); end;'
8 );
9 end;
10* /
PL/SQLプロシージャが正常に完了しました。
SQL> exit
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.31.0.1.0から切断されました
mcp-salesadb %
[
{
"EMPLOYEE_ID": 103,
"FIRST_NAME": "Alexander",
"LAST_NAME": "James",
"EMAIL": "AJAMES",
"PHONE_NUMBER": "1.590.555.0103",
"HIRE_DATE": "2016-01-03T00:00:00",
"JOB_ID": "IT_PROG",
"SALARY": null,
"COMMISSION_PCT": null,
"MANAGER_ID": 102,
"DEPARTMENT_ID": 60
},
{
"EMPLOYEE_ID": 104,
"FIRST_NAME": "Bruce",
"LAST_NAME": "Miller",
"EMAIL": "BMILLER",
"PHONE_NUMBER": "1.590.555.0104",
"HIRE_DATE": "2017-05-21T00:00:00",
"JOB_ID": "IT_PROG",
"SALARY": 6000,
"COMMISSION_PCT": null,
"MANAGER_ID": 103,
"DEPARTMENT_ID": 60
},
{
"EMPLOYEE_ID": 105,
"FIRST_NAME": "David",
"LAST_NAME": "Williams",
"EMAIL": "DWILLIAMS",
"PHONE_NUMBER": "1.590.555.0105",
"HIRE_DATE": "2015-06-25T00:00:00",
"JOB_ID": "IT_PROG",
"SALARY": 4800,
"COMMISSION_PCT": null,
"MANAGER_ID": 103,
"DEPARTMENT_ID": 60
},
{
"EMPLOYEE_ID": 106,
"FIRST_NAME": "Valli",
"LAST_NAME": "Jackson",
"EMAIL": "VJACKSON",
"PHONE_NUMBER": "1.590.555.0106",
"HIRE_DATE": "2016-02-05T00:00:00",
"JOB_ID": "IT_PROG",
"SALARY": 4800,
"COMMISSION_PCT": null,
"MANAGER_ID": 103,
"DEPARTMENT_ID": 60
},
{
"EMPLOYEE_ID": 107,
"FIRST_NAME": "Diana",
"LAST_NAME": "Nguyen",
"EMAIL": "DNGUYEN",
"PHONE_NUMBER": "1.590.555.0107",
"HIRE_DATE": "2017-02-07T00:00:00",
"JOB_ID": "IT_PROG",
"SALARY": 4200,
"COMMISSION_PCT": null,
"MANAGER_ID": 103,
"DEPARTMENT_ID": 60
}
]