17.00 - Using Function Mapping to Prevent Exposed Credentials in Queries - Teradata Database

Teradata Vantage™ - Native Object Store Getting Started Guide

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
created_date
September 2020
category
Programming Reference
featnum
B035-1214-170K
You can use function mapping to hide credentials in an authorization object. After you create the function mapping, you can use the mapped authorization object in queries and avoid exposing your credentials.

For more information about function mapping, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144 and Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.

For example, the user needs these privileges to run the following commands:
  • CREATE AUTHORIZATION
  • CREATE FUNCTION
  1. Log in as a user with the privileges needed to create an authorization object and function mapping.
  2. If not already done, create an authorization object called DefAuth:
    CREATE AUTHORIZATION DefAuth
    AS DEFINER TRUSTED
    USER 'YOUR-ACCESS-KEY-ID'
    PASSWORD 'YOUR-SECRET-ACCESS-KEY';
  3. Create the Function Mapping

  4. Create the function mapping:
    CREATE FUNCTION MAPPING READ_NOS_FM
    FOR READ_NOS EXTERNAL SECURITY DEFINER TRUSTED DefAuth
    USING
    BUFFERSIZE,
    SAMPLE_PERC,
    ROWFORMAT,
    RETURNTYPE,
    HEADER,
    MANIFEST,
    LOCATION,
    STOREDAS,
    FULLSCAN,
    ANY IN TABLE;
  5. Verify the Function Mapping

  6. Use the function mapping READ_NOS_FM in a SELECT query:
    SELECT TOP 2 LOCATION FROM READ_NOS_FM (
    ON (SELECT cast (NULL as JSON))
    USING
    LOCATION('YOUR-STORAGE-ACCOUNT')
    RETURNTYPE('NOSREAD_RECORD')
    ) AS D;

    Where YOUR-STORAGE-ACCOUNT is the location of your external storage; for example, /s3/td-usgs.s3.amazonaws.com/JSONDATA/.

    Your result will be similar to the following:

    Location
    --------------------------------------------------------------
    /S3/td-usgs.s3.amazonaws.com/JSONDATA/09380000/2018/06/28.json
    /S3/td-usgs.s3.amazonaws.com/JSONDATA/09380000/2018/06/29.json