2.10 - Configuring a Foreign Server and Granting Privileges for a Teradata-to-TargetConnector - Teradata QueryGrid

Teradata® QueryGrid™ Installation and User Guide

prodname
Teradata QueryGrid
vrm_release
2.10
created_date
September 2019
category
Administration
Configuration
Installation
User Guide
featnum
B035-5991-099K
Before you begin, the following procedure for a Teradata-to-Teradata connector whose Authentication Mechanism is set to Trusted, verify that a proxy user exists on the target Teradata Database system. The proxy user is the one that will be used for authentication on the target system. See Setting Up the Proxy User for a Teradata-to-Teradata Connector when Authentication Mechanism is Set to Trusted.
Complete the following steps on the initiator system. These apply after initial deployment and when creating remote servers or access, but not after upgrading versions.
For Teradata connectors, External Name Value Pairs (NVP) are set in the Foreign Server definition.
  • The External NVP must be set up so the QueryGrid connector can identify itself with the Teradata QueryGrid Manager to obtain the configuration NVP.
  • External NVP may be set up on Teradata to access specific features like name and role mapping, but should be used with care to not conflict with values supplied by Teradata QueryGrid Manager.
  • The External NVP are called LINK and VERSION. These are configured using SQL commands: ALTER FOREIGN SERVER or CREATE FOREIGN SERVER. For more information, see Teradata Connector and Link Properties.

  1. Log on as an Administrator, such as dbc, to the initiating Teradata Database system, and create an authorization object for the target server:
    CREATE AUTHORIZATION td_server_db.target_server_auth AS DEFINER TRUSTED USER 'proxyuser' PASSWORD 'password';
    An authorization object is created in the td_server_db database. Using the DEFINER clause makes the authorization available globally to all users.
  2. Grant the CREATE SERVER and EXECUTE FUNCTION privileges on the td_server_db database to the Administrator user, for example:
    GRANT CREATE SERVER ON td_server_db TO dbc;
    GRANT EXECUTE FUNCTION ON TD_SYSFNLIB TO dbc;
  3. Create the foreign server:
    CREATE FOREIGN SERVER target_server_name
    EXTERNAL SECURITY DEFINER TRUSTED target_server_auth
    USING
    LINK('linkname')
    VERSION ('version')
    DO IMPORT WITH TD_SYSFNLIB.QGInitiatorImport,
    DO EXPORT WITH TD_SYSFNLIB.QGInitiatorExport;

    For example, where sdll7100 is the initiating Teradata Database system, and sdll7151 is the target Teradata Database system:

    CREATE FOREIGN SERVER sdll7151_fs
    EXTERNAL SECURITY DEFINER TRUSTED target_server_auth
    USING
    LINK('sdll7100_sdll7151')
    VERSION('active')
    DO IMPORT WITH TD_SYSFNLIB.QGInitiatorImport,
    DO EXPORT WITH TD_SYSFNLIB.QGInitiatorExport;
  4. From database td_server_db, grant SELECT and INSERT privileges on the target server to initiating end users.
    GRANT SELECT ON td_server_db.target_server_name to initiating_end_user;
    GRANT INSERT ON td_server_db.target_server_name to initiating_end_user;

    For example:

    GRANT SELECT ON td_server_db.sdll7151_fs to sdll7100_user2;
    GRANT INSERT ON td_server_db.sdll7151_fs to sdll7100_user2;