Restrict Access to External Storage | Native Object Store | Teradata Vantage - 17.05 - Controlling Foreign Table Access with an AUTHORIZATION Object - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Native Object Store Getting Started Guide

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1214-175K
Language
English (United States)
An authorization object is used to control who can access an external object store.

Before creating the authorization object Advanced SQL Engine must have permission from the external object store to access the data. For example, to access an Amazon S3 bucket an Access Key or an AWS Identity and Access Management (IAM) user credential is required. To access a Google Cloud Storage bucket an Access Key is required. To access Azure storage an Access Key or Shared Access Signature (SAS) token is needed. The credentials are configured on the object store that you want to access.

Once your external storage allows Advanced SQL Engine to access it, set up an authorization object using the appropriate credentials.

An authorization object can be shared among several people if Definer is used in the authorization object definition. If Invoker is used, only the user who created the object can use it.

Description Example
Authorization object used by one user
CREATE AUTHORIZATION authorization_object
AS INVOKER TRUSTED
USER 'YOUR-ACCESS-KEY-ID'
PASSWORD 'YOUR-SECRET-ACCESS-KEY';
Authorization object shared by a group of users
CREATE AUTHORIZATION authorization_object
AS DEFINER TRUSTED
USER 'YOUR-ACCESS-KEY-ID'
PASSWORD 'YOUR-SECRET-ACCESS-KEY';
Public buckets or containers in external object stores do not require credentials for access. To access a public bucket or container, put an empty string between the straight quotes for USER and PASSWORD:
CREATE AUTHORIZATION authorization_object
AS DEFINER TRUSTED
USER ''
PASSWORD '';

    Prerequisites

  1. If not already done, log on to Advanced SQL Engine as an administrative user who can grant others privileges.
  2. Grant the appropriate privileges to the user.
    To create an authorization object, the user needs the following privileges:
    • CREATE AUTHORIZATION
  3. Log off as the administrative user.
  4. Create the Authorization Object

  5. To run NOS-related commands, log on to the database as a user with the required privileges.
  6. Create an authorization object in Advanced SQL Engine with the credentials to your external object store.
    Create the authorization object in the same database as the foreign table that will use it.
    CREATE AUTHORIZATION DefAuth
    AS DEFINER TRUSTED
    USER 'YOUR-ACCESS-KEY-ID'
    PASSWORD 'YOUR-SECRET-ACCESS-KEY';

    Or, to access a public bucket create the authorization object like this:

    CREATE AUTHORIZATION DefAuth
    AS DEFINER TRUSTED
    USER ''
    PASSWORD '';
PostrequisiteIn later examples, the authorization object is linked to a foreign table and in other examples used in a function mapping.