Pleasanter Technical Manual

テーマ

拡張 SQL の実行ユーザと外部 DB 接続 ​

第2版作成 最終更新 （日本時間）
確認バージョン1.5.8.1

拡張 SQL はプリザンターのデータベースに接続して実行されるため、DBMS の機能（SQL Server の Linked Server、PostgreSQL の Foreign Data Wrapper、MySQL の FEDERATED エンジン）を使えば、外部のデータベースも参照できます。このページでは、拡張 SQL がどの DB ユーザで実行されるか、そのユーザにどんな権限があるか、外部 DB に接続するために DBA 側で何を設定するかをまとめます。拡張 SQL そのものの設定項目は 拡張 SQL の活用 を参照してください。

INFO

実装の根拠は、確認時のソースへの固定リンクで示しています。外部 DB 接続の設定（Linked Server・FDW・FEDERATED）は各 DBMS の機能で、プリザンターのソースでは確かめていません。

拡張 SQL を実行する DB ユーザ ​

Rds.json には 3 種類の接続文字列があります。

接続文字列使われる場面
SaConnectionStringCodeDefiner の実行時（データベースやユーザの作成）
OwnerConnectionString削除済みレコードの復元（IssueUtilities.cs#L6097-L6100）など、本体の一部の処理
UserConnectionString画面・API の通常の読み書き。拡張 SQL も既定ではこれ

拡張 SQL の DbUser に "Owner" を書くと OwnerConnectionString で実行されます。ただし DbUser を見ているのは API から呼び出す拡張 SQL（/api/extended/sql）の実行処理だけ です（ExtensionUtilities.cs#L89-L126）。確認したソースで DbUser を参照している箇所はここだけで、OnCreated などのイベントで動く拡張 SQL や Html: true の拡張 SQL は、DbUser を書いても UserConnectionString で実行されます。

csharp
switch (extendedSql.DbUser)
{
    case "Owner":
        connectionString = Parameters.Rds.OwnerConnectionString;
        break;
    default:
        connectionString = null; // 既定の接続（UserConnectionString）
        break;
}

CodeDefiner が作る DB ユーザの権限 ​

CodeDefiner はユーザ名が _Owner で終わるものをオーナー、それ以外を通常ユーザとして作ります（UsersConfigurator.cs#L60-L67）。付与される権限は DBMS ごとの SQL 定義（App_Data/Definitions/Sqls/<DBMS>/）で決まります。

DBMSオーナー通常ユーザ
SQL Serverプリザンターの DB で db_owner（CreateLoginAdmin.sql）db_datareader と db_datawriter（GrantPrivilegeUser.sql）
PostgreSQLユーザ作成の SQL は空で、事前に作っておくオーナーが所有するテーブルへの select, insert, update, delete（GrantPrivilegeUser.sql）
MySQLプリザンターの DB への create, alter, index, drop と DML・ルーチン作成（with grant option）プリザンターの DB への DML とルーチン作成（GrantPrivilegeUser.sql）

SQL Server での違いを整理すると次のとおりです。

操作オーナー（db_owner）通常ユーザ（db_datareader / db_datawriter）
SELECT / INSERT / UPDATE / DELETE○○
DDL（CREATE / ALTER / DROP）○×
ストアドプロシージャの EXECUTE○個別に付与が必要

CodeDefiner の権限はプリザンターの DB の中だけ

どの DBMS でも、CodeDefiner が付与するのはプリザンターのデータベース内の権限だけです。Linked Server のログインマッピング、FDW の USAGE 権限やユーザマッピング、FEDERATED テーブルの作成権限は含まれません。拡張 SQL から外部 DB を読むには、DBA が次の設定を別途行う必要があります。

外部データベースへの接続 ​

項目SQL Server（Linked Server）PostgreSQL（FDW）MySQL（FEDERATED）
設定の単位サーバーサーバー＋テーブルテーブル
接続できる相手OLE DB で接続できる DBMSFDW 拡張次第（postgres_fdw、mysql_fdw、tds_fdw など）MySQL のみ
クエリの書き方4 部構成名か OPENQUERY通常の SELECT通常の SELECT
トランザクション分散トランザクション（MSDTC）対応非対応（リモートの変更は即時確定）

どの方式でも、拡張 SQL の接続に使う DB ユーザ（通常は UserConnectionString のユーザ、API 経由で DbUser: "Owner" ならオーナー）に対して、外部接続を使う権限を与えます。

SQL Server：Linked Server ​

Linked Server の作成・管理には sysadmin か setupadmin のサーバーロールが要ります。使う側に特定のロールは要らず、ログインマッピングで許可します。db_owner はデータベース内の権限なので、db_owner だけでは Linked Server は使えません。

sql
-- リンクサーバーを作る
EXEC sp_addlinkedserver
    @server = N'RemoteServer',
    @srvproduct = N'',
    @provider = N'SQLNCLI11',
    @datasrc = N'remote-server-name';

-- プリザンターの接続ユーザをリモートのログインに対応付ける
EXEC sp_addlinkedsrvlogin
    @rmtsrvname = N'RemoteServer',
    @useself = N'False',
    @locallogin = N'pleasanter_user',
    @rmtuser = N'remote_user',
    @rmtpassword = N'********';

@locallogin = NULL にすると、個別の対応付けがないすべてのログインに適用されます。全ログインがリモートに接続できるようになるので、プリザンターの接続ユーザだけを個別に対応付けるほうが安全です。

json
{
    "Name": "GetRemoteData",
    "Api": true,
    "CommandText": "SELECT * FROM OPENQUERY([RemoteServer], 'SELECT Code, Name FROM [RemoteDB].[dbo].[MasterTable]')"
}

4 部構成名（[RemoteServer].[RemoteDB].[dbo].[TableName]）でも書けますが、WHERE 句での絞り込みをリモート側で行わせたいときは OPENQUERY を使います。

確認用の SQL とよくあるエラー
sql
-- リンクサーバーの一覧
SELECT name, provider, data_source FROM sys.servers WHERE is_linked = 1;

-- ログインマッピングの一覧
SELECT s.name AS LinkedServerName, sp.name AS LocalLogin, ll.uses_self_credential, ll.remote_name
FROM sys.linked_logins ll
JOIN sys.servers s ON ll.server_id = s.server_id
LEFT JOIN sys.server_principals sp ON ll.local_principal_id = sp.principal_id
WHERE s.is_linked = 1;

-- 接続テスト
EXEC sp_testlinkedserver N'RemoteServer';

-- RPC を使う場合
EXEC sp_serveroption 'RemoteServer', 'rpc', 'true';
EXEC sp_serveroption 'RemoteServer', 'rpc out', 'true';
エラー原因対処
サーバーへのアクセスが拒否された、OLE DB プロバイダーでエラーログインマッピングがないsp_addlinkedsrvlogin で対応付ける
リモート サーバーでログインに失敗したリモート側の認証エラーリモートのログイン名・パスワードを確認する
オブジェクトに対する SELECT 権限が拒否されたリモート DB 側の権限不足リモート側で権限を付与する
RPC 要求が無効RPC が無効sp_serveroption で有効にする

書き込みは MSDTC が要ることがある

Linked Server への単純な SELECT や OPENQUERY の SELECT では MSDTC は要りません。一方、ローカルのトランザクションの中で Linked Server に書き込むと分散トランザクションになり、MSDTC（ネットワーク DTC アクセスの許可など）の設定が必要になります。OnCreated や OnUpdated の拡張 SQL から外部 DB へ書き込む場合は注意してください。リモートで完結させるなら EXEC ('...') AT [RemoteServer] を使う方法もあります。

PostgreSQL：Foreign Data Wrapper ​

sql
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

CREATE SERVER remote_server
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'remote-host', port '5432', dbname 'remote_db');

-- プリザンターの接続ユーザ用のユーザマッピング
CREATE USER MAPPING FOR pleasanter_user
    SERVER remote_server
    OPTIONS (user 'remote_user', password '********');

GRANT USAGE ON FOREIGN SERVER remote_server TO pleasanter_user;

-- 外部テーブル（またはスキーマごと IMPORT FOREIGN SCHEMA）
CREATE FOREIGN TABLE remote_table (
    id integer,
    name text
) SERVER remote_server
OPTIONS (schema_name 'public', table_name 'source_table');

GRANT SELECT ON remote_table TO pleasanter_user;
操作必要な権限
CREATE EXTENSION / CREATE SERVERデータベースの CREATE 権限またはスーパーユーザ
ユーザマッピングの作成外部サーバーの USAGE（他ユーザ用はスーパーユーザ）
外部テーブルの作成スキーマの CREATE
外部テーブルの参照外部テーブルへの SELECT など

CodeDefiner の GrantPrivilegeUser.sql はオーナーが所有する通常テーブルにしか権限を付けないため、外部テーブルへの SELECT は別途付与します。権限は has_server_privilege('pleasanter_user', 'remote_server', 'USAGE') で確認できます。

MySQL：FEDERATED エンジン ​

FEDERATED エンジンが無効な場合は、my.cnf の [mysqld] に federated を書いて再起動します（SHOW ENGINES で確認）。

sql
CREATE TABLE remote_table (
    id INT NOT NULL,
    name VARCHAR(100),
    PRIMARY KEY (id)
) ENGINE=FEDERATED
CONNECTION='mysql://remote_user:********@remote-host:3306/remote_db/source_table';

CREATE SERVER でサーバー定義を作る場合は SUPER 権限（MySQL 8.0.3 以降は FEDERATED_ADMIN でも可）が要ります。FEDERATED はトランザクションに対応せず、ローカルのインデックスも使われません。CONNECTION に書いたパスワードは平文で保存されます。

関連ページ ​

変更履歴

第2版記事の確認版を繰り返す表現を整理する
第1版サイト設定の変更履歴・拡張 SQL の外部 DB 接続・サイト名の解決・API ラッパー・ApiVersion の解説と、関連する改修・設計メモを追加