Using Function Mapping to Prevent Exposed Credentials in Queries - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Native Object Store Getting Started Guide

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
jjn1567647976698.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1214
lifecycle
previous
Product Category
Software
Teradata Vantage
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