Beispiel: Dynamische Verzeichniszugriffsserver (SQL)

In diesem Beispiel können Administratoren dynamische Verzeichniszugriffsserver mit der CREATE SERVER-Anweisung mit Variablen für den Verzeichniszugriffsserver und die Unterverzeichnisebene erstellen.

Voraussetzungen

Sie müssen das SERVER OPERATOR-Systemprivileg haben.

Kontext und Bemerkungen

Nehmen wir an, Sie sind DBA und verfügen über eine Datenbank, die manchmal auf Computer A gestartet wird, mit dem Datenbankserver namens server1, und zu anderen Zeiten auf Computer B gestartet wird, mit dem Server namens server2. Angenommen, Sie wollen einen Verzeichniszugriffsserver einrichten, der sowohl auf das lokale Laufwerk c:\temp auf Computer A als auch auf das Netzwerkserver-Laufwerk d:\temp auf Computer B zeigt. Außerdem wollen Sie eine Gruppe von Proxy-Tabellen einrichten, aus der alle Benutzer die Liste ihrer eigenen privaten Verzeichnisse abrufen können. Durch die Verwendung von Variablen in der USING-Klausel einer CREATE SERVER-Anweisung und in der AT-Klausel einer CREATE EXISTING TABLE-Anweisung können Sie diesen Anforderungen gerecht werden, indem Sie einen einzigen Verzeichniszugriffsserver und eine einzige Proxy-Tabelle erstellen, und zwar folgendermaßen:

Aufgabe

In diesem Beispiel wird der Name des Servers, mit dem die Verbindung hergestellt werden soll, mit server1 angenommen und es wird vorausgesetzt, dass die folgenden Verzeichnisse bereits existieren.

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

Erstellen Sie den Verzeichniszugriffsserver mit Variablen für das Stammverzeichnis des Verzeichniszugriffsservers und die Unterverzeichnisebene.

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

Erstellen Sie explizite externe Logins für jeden Benutzer, der berechtigt ist, den Verzeichniszugriffsserver zu verwenden.

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

Erstellen Sie Variablen, die verwendet werden, um den Verzeichniszugriffsservers und die damit verbundenen Proxy-Tabellen dynamisch zu konfigurieren.

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

Erstellen Sie eine Proxy-Tabelle, die auf @directory\@curuser auf dem Verzeichniszugriffsserver @server verweist.

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

Die Variablen werden nicht mehr benötigt und können daher mithilfe der folgenden Anweisungen gelöscht werden:

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

Erstellen Sie die -Prozedur, die die Benutzer verwenden, um den Inhalt ihrer jeweiligen Benutzerverzeichnisse zu sehen.

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;

Der letzte Schritt in der Prozedur schließt die Verbindung zum Fremdserver, sodass der Benutzer die entfernten Tabellen auf dem Verzeichniszugriffsserver nicht auflisten kann (z.B. mithilfe der sp_remote_tables-Systemprozedur).

Legen Sie die erforderlichen Berechtigungen für die allgemeine Verwendung der gespeicherten Prozedur fest.

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

Trennen Sie die Verbindung zum Datenbankserver und stellen Sie eine neue Verbindung als UPDATER (Kennwort "update") oder BROWSER (Kennwort "browse") her. Führen Sie die folgende Abfrage aus.
CALL dbo.listmydir()

Ergebnisse

Der dynamische Verzeichniszugriffsserver wird erstellt und konfiguriert.

Siehe auch

Kommentieren Sie diese Seite in DocCommentXchange.