% podman exec -it apex-db bash
bash-4.4$
bash-4.4$ sqlplus / as sysdba
SQL*Plus: Release 23.0.0.0.0 - Production on Tue Dec 3 07:38:35 2024
Version 23.5.0.24.07
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.5.0.24.07
SQL>
alter session set container = freepdb1;
SQL> alter session set container = freepdb1;
Session altered.
SQL>
以下のスクリプトを実行し、ユーザーWKSP_MCPを作成します。パスワードは適当な文字列に変えます。この後に作成するAPEXワークスペースの名前をmcpとするため、ORDS_ADMIN.ENABLE_SCHEMAの引数p_url_mapping_patternにmcpを与えています。
-- USER SQL
CREATE USER WKSP_MCP IDENTIFIED BY "パスワード";
-- ADD ROLES
GRANT CONNECT TO WKSP_MCP;
GRANT RESOURCE TO WKSP_MCP;
-- REST ENABLE
BEGIN
ORDS_ADMIN.ENABLE_SCHEMA(
p_enabled => TRUE,
p_schema => 'WKSP_MCP',
p_url_mapping_type => 'BASE_PATH',
p_url_mapping_pattern => 'mcp',
p_auto_rest_auth=> TRUE
);
commit;
END;
/
-- QUOTA
ALTER USER WKSP_MCP QUOTA UNLIMITED ON USERS;SQL> -- USER SQL
CREATE USER WKSP_MCP IDENTIFIED BY "パスワード";
-- ADD ROLES
GRANT CONNECT TO WKSP_MCP;
GRANT RESOURCE TO WKSP_MCP;
-- REST ENABLE
BEGIN
ORDS_ADMIN.ENABLE_SCHEMA(
p_enabled => TRUE,
p_schema => 'WKSP_MCP',
p_url_mapping_type => 'BASE_PATH',
p_url_mapping_pattern => 'mcp',
p_auto_rest_auth=> TRUE
);
commit;
END;
/
-- QUOTA
ALTER USER WKSP_MCP QUOTA UNLIMITED ON USERS;SQL>
User created.
SQL> SQL> SQL>
Grant succeeded.
SQL>
Grant succeeded.
SQL> SQL> SQL> 2 3 4 5 6 7 8 9 10 11
PL/SQL procedure successfully completed.
SQL> SQL> SQL>
User altered.
SQL>
データベースへのユーザー作成は以上で完了です。
% podman exec -it apex-ords bash
[oracle@apex ords]$
cd /etc/ords/config
[oracle@apex ords]$ cd /etc/ords/config
[oracle@apex config]$
REST対応SQLを有効にします。--configの指定がなければORDSの構成ファイルを保存するディレクトリとしてカレント・ディレクトリが選択されますが、念のため--configオプションも付けておきます。
[oracle@apex config]$ ords --config /etc/ords/config config set restEnabledSql.active true
ORDS: Release 24.3 Production on Tue Dec 03 08:22:12 2024
Copyright (c) 2010, 2024, Oracle.
Configuration:
/etc/ords/config
The setting named: restEnabledSql.active was set to: true in configuration: default
[oracle@apex config]$
REST対応SQLの認証に使用できるORDSのユーザーを、ords_devとして作成しておきます。ロールとしてSQL Developerを与えます。作成時にパスワードを入力します。
ords --config /etc/ords/config config user add ords_dev "SQL Developer"
[oracle@apex config]$ ords --config /etc/ords/config config user add ords_dev "SQL Developer"
ORDS: Release 24.3 Production on Tue Dec 03 08:26:35 2024
Copyright (c) 2010, 2024, Oracle.
Configuration:
/etc/ords/config
Enter the password for ords_dev: **********
Confirm password: **********
Created user ords_dev in file /etc/ords/config/global/credentials
[oracle@apex config]$
スキーマWKSP_MCPとそのパスワードを指定しても基本認証は可能です。スキーマWKSP_MCPで認証するとアクセスできる範囲はスキーマWKSP_MCPに限定されますが、SQL Developerロールを持つORDSユーザーords_devで認証すると、すべてのスキーマにアクセスできます。
% podman restart apex-ords
apex-ords
%
curl -i -X POST -u wksp_mcp:[パスワード] http://localhost:8181/ords/mcp/_/sql -H "Content-Type: application/sql" --data-binary 'select * from eba_country_regions'
% curl -i -X POST -u wksp_mcp:********** http://localhost:8181/ords/mcp/_/sql -H "Content-Type: application/sql" --data-binary 'select * from eba_country_regions'
HTTP/1.1 200 OK
Content-Type: application/json
X-Frame-Options: SAMEORIGIN
Transfer-Encoding: chunked
{"env":{"defaultTimeZone":"UTC"},"items":[{"statementId":1,"statementType":"query","statementPos":{"startLine":1,"endLine":2},"statementText":"select * from eba_country_regions","resultSet":{"metadata":[{"columnName":"ID","jsonColumnName":"id","columnTypeName":"NUMBER","columnClassName":"java.math.BigDecimal","precision":0,"scale":-127,"isNullable":0},{"columnName":"NAME","jsonColumnName":"name","columnTypeName":"VARCHAR2","columnClassName":"java.lang.String","precision":255,"scale":0,"isNullable":0}],"items":[{"id":10,"name":"America"},{"id":20,"name":"Europe"},{"id":30,"name":"Asia"},{"id":40,"name":"Oceania"},{"id":50,"name":"Africa"}],"hasMore":false,"limit":10000,"offset":0,"count":5},"response":[],"result":0}]}% %
続いてORDSユーザーords_devで認証してみます。
% curl -i -X POST -u ords_dev:********* http://localhost:8181/ords/mcp/_/sql -H "Content-Type: application/sql" --data-binary 'select * from eba_country_regions'
HTTP/1.1 200 OK
Content-Type: application/json
X-Frame-Options: SAMEORIGIN
Transfer-Encoding: chunked
{"env":{"defaultTimeZone":"UTC"},"items":[{"statementId":1,"statementType":"query","statementPos":{"startLine":1,"endLine":2},"statementText":"select * from eba_country_regions","resultSet":{"metadata":[{"columnName":"ID","jsonColumnName":"id","columnTypeName":"NUMBER","columnClassName":"java.math.BigDecimal","precision":0,"scale":-127,"isNullable":0},{"columnName":"NAME","jsonColumnName":"name","columnTypeName":"VARCHAR2","columnClassName":"java.lang.String","precision":255,"scale":0,"isNullable":0}],"items":[{"id":10,"name":"America"},{"id":20,"name":"Europe"},{"id":30,"name":"Asia"},{"id":40,"name":"Oceania"},{"id":50,"name":"Africa"}],"hasMore":false,"limit":10000,"offset":0,"count":5},"response":[],"result":0}]}% %
{
"mcpServers": {
"oracle": {
"command": "node",
"args": [
"/Users/username/Documents/oracle-server/build/index.js",
"http://localhost:8181/ords/mcp/_/sql",
"wksp_mcp",
"**********"
]
}
}
}今回の記事は以上になります。
