Example: Dynamic directory access servers (SQL)

In this example, administrators can create dynamic directory access servers using the CREATE SERVER statement with variables for the root of the directory access server and the subdirectory level.

Prerequisites

You must have the SERVER OPERATOR system privilege.

Context and remarks

Assume you are a DBA and have a database that is sometimes started on computer A, with the database server named server1, and at other times is started on computer B, with the server named server2. Suppose you want to set up a directory access server that points to the local drive c:\temp on computer A as well as the network server drive d:\temp on computer B. Additionally, you want to set up a proxy table from which all users can get the listing of their own private directory. By using variables in the USING clause of a CREATE SERVER statement and in the AT clause of a CREATE EXISTING TABLE statement, you can fulfill your needs by creating a single directory access server and a single proxy table, as follows:

Task

For this example, the name of the server that you are connecting to is assumed to be server1 and the following directories are assumed to exist.

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

Create the directory access server using variables for the root of the directory access server and the subdirectory level.

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

Create explicit external logins for each user who is allowed to use the directory access server.

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

Create variables that will be used to dynamically configure the directory access server and related proxy table.

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';

Create a proxy table that points to @directory\@curuser on the directory access server @server.

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

The variables are no longer needed, so drop them by executing the following statements:

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

Create the procedure that users will use to view the contents of their individual user directories.

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;

The final step in the procedure closes the remote connection so that the user cannot list the remote tables on the directory access server (for example, by using the sp_remote_tables system procedure).

Set the permissions required for general use of the stored procedure.

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

Disconnect from the database server and reconnect as the user UPDATER (password 'update') or the user BROWSER (password 'browse'). Run the following query.
CALL dbo.listmydir()

Results

The dynamic directory access server is created and configured.

Example: Dynamic directory access servers (SQL)

Task

See also