示例:动态目录访问服务器 (SQL)

在此示例中，管理员可通过目录访问服务器的根和子目录级别变量，使用 CREATE SERVER 语句创建动态目录访问服务器。

前提条件

您必须具有 SERVER OPERATOR 系统特权。

上下文和注释

假设您是 DBA 用户并有一个数据库，而该数据库有时在计算机 A 上启动（具有名为 server1 的数据库服务器），有时在计算机 B 上启动（具有名为 server2 的服务器）。假设您想设置一个指向计算机 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讨论此页。