例：動的なディレクトリアクセスサーバ (SQL の場合)

この例では、管理者は CREATE SERVER 文とともにディレクトリアクセスサーバのルートとサブディレクトリのレベルを表す変数を使用して、動的なディレクトリアクセスサーバを作成できます。

前提条件

SERVER OPERATOR システム権限が必要です。

内容と備考

あなたは DBA であり、あるときは server1 という名前のデータベースサーバが起動されるコンピュータ A のデータベース、またはあるときは server2 という名前のデータベースサーバが起動されるコンピュータ B のデータベースを利用しているとします。ここで、コンピュータ A のローカルドライブ c:\temp を参照するディレクトリアクセスサーバと、コンピュータ B のネットワークサーバドライブ d:\temp をセットアップしたいとします。さらに、プロキシテーブルをセットアップして、すべてのユーザがそこから自分のプライベートディレクトリのリストを取得できるようにしたいとします。次の手順に従って、CREATE SERVER 文の USING 句と CREATE EXISTING TABLE 文の AT 句の変数を使用することによって、単一のディレクトリアクセスサーバと単一のプロキシテーブルを作成すれば、そのような要求を満たすことができます。

♦ タスク

この例では、接続しているサーバの名前が server1 であるとして、次のディレクトリが存在するとします。

c:\temp\dba
c:\temp\updater
c:\temp\browser

ディレクトリアクセスサーバのルートとサブディレクトリのレベルを表す変数を使用して、ディレクトリアクセスサーバを作成します。

CREATE SERVER dir 
CLASS 'DIRECTORY' 
USING 'root={@directory};subdirs={@subdirs}';

ディレクトリアクセスサーバの使用を許可するそれぞれのユーザに明示的な外部ログインを作成します。

CREATE EXTERNLOGIN "DBA" TO dir;
CREATE EXTERNLOGIN "UPDATER" TO dir;
CREATE EXTERNLOGIN "BROWSER" TO dir;

ディレクトリアクセスサーバとその関連するプロキシテーブルを動的に設定するための変数を作成します。

CREATE VARIABLE @directory LONG VARCHAR;
SET @directory = 'c:\\temp';

CREATE VARIABLE @subdirs VARCHAR(10);
SET @subdirs = '7';

CREATE VARIABLE @curuser VARCHAR(128);
SET @curuser = 'updater';

CREATE VARIABLE @server VARCHAR(128);
SET @server = 'dir';

ディレクトリアクセスサーバ @server の @directory\@curuser を参照するプロキシテーブルを作成します。

CREATE EXISTING TABLE dbo.userdir AT '{@server};;;{@curuser}';

変数はもう必要ではないため、次の文を実行して削除します。

DROP VARIABLE @server;
DROP VARIABLE @curuser;
DROP VARIABLE @subdirs;
DROP VARIABLE @directory;

ユーザが自分のユーザディレクトリの内容を表示するためのプロシージャを作成します。

CREATE OR REPLACE PROCEDURE dbo.listmydir()
SQL SECURITY INVOKER
BEGIN
    DECLARE @directory LONG VARCHAR;
    DECLARE @subdirs VARCHAR(10);
    DECLARE @server VARCHAR(128);
    DECLARE @curuser VARCHAR(128);

    -- for this example we always use the "dir" remote directory access server
    SET @server = 'dir';

    -- the root directory is based on the name of the server the user is connected to
    SET @directory = if property('name') = 'server1' then 'c:\\temp' 
                     else 'd:\\temp' endif;

    -- the subdir limit is based on the connected user
    SET @curuser = user_name();

    -- all users get a subdir limit of 7 except "browser" who gets a limit of 1
    SET @subdirs = convert( varchar(10), if @curuser = 'browser' then 1 else 7 endif);

    -- with all the variables set above, the proxy table dbo.userdir 
    -- now points to @directory\@curuser and has a subdir limit of @subdirs
    SELECT * FROM dbo.userdir;

    DROP REMOTE CONNECTION TO dir CLOSE CURRENT;
END;

この手順の最後のステップでは、リモート接続を終了して、ユーザが (sp_remote_tables システムプロシージャなどを使用して) ディレクトリアクセスサーバのリモートテーブルをリストできないようにします。

ストアドプロシージャの一般的な用途に必要な権限を設定します。

GRANT SELECT ON dbo.userdir TO PUBLIC;
GRANT EXECUTE ON dbo.listmydir TO PUBLIC;

データベースサーバから切断して、ユーザ UPDATER (パスワード 'update') またはユーザ BROWSER (パスワード 'browse') として再接続します。次のクエリを実行します。
CALL dbo.listmydir()

結果

動的なディレクトリアクセスサーバが作成され、設定されます。

参照

DocCommentXchange で意見交換できます