Basic Concepts for using Authorization Objects with Teradata NOS and S3

An introduction to Vantage Native Object Store (NOS) has been covered in a previous post, where the following code sample was used to query csv data in an S3 store (by using the READ_NOS operator).

SELECT TOP 5 *
FROM
    READ_NOS ( 
USING 
    LOCATION ('/s3/my-private-surface-water-data-store.s3.us-west-1.amazonaws.com/CSVDATA/09380000/2018/06/27.csv') 
      AUTHORIZATION ('{"Access_ID":"AKIATZSC8AVA4PNC6VLC"                   ,"Access_Key":"qpGYpDMFiKgPczxEJgKgxFO9cUhiiuqJFESl61Gw"}')
    RETURNTYPE ('NOSREAD_RECORD') 
) AS D

An obvious concern with this approach is that the IAM Access key is clearly visible:

...
AUTHORIZATION ('{"Access_ID":"AKIATZSC8AVA4PNC6VLC"                   ,"Access_Key":"qpGYpDMFiKgPczxEJgKgxFO9cUhiiuqJFESl61Gw"}')
...

Authorization objects provide a secure approach to storing these hard-coded values. For example, if the above credentials were stored using an authorization with name sampledb.auth_s3, the updated code can be changed to reference the authorization:

...
AUTHORIZATION (sampledb.auth_s3)
...

Topics in this post will cover the creation of authorizations for S3 stores, with examples showing usage with READ_NOS, foreign tables and function mappings.

As a result of several enhancements to NOS between versions of Teradata Vantage, it’s important to note that from 17.10 onwards, the new system-wide authorization model was introduced.

All sample code has been tested using Teradata Vantage Express 17.10.

Authorization Elements

Syntax for creating an authorization is repeated below for convenience.

{ CREATE | REPLACE } AUTHORIZATION [ database_name. | user_name. ] authorization_name
  [ AS [ DEFINER | INVOKER ] TRUSTED ]
  USER 'user_name' 
  PASSWORD 'password'
  ;

Summary of elements:

• if INVOKER TRUSTED specified -> only the user who created the object can use it.
• if DEFINER TRUSTED specified -> other users can access the authorization (provided they have been assigned correct permissions)
• if neither of the above are specified -> system-wide authorization
• user_name: for S3 external stores, this is the value of AWS Access_ID
• password: for S3 stores this is the value of AWS Access_Key
• for public stores, user_name & password are passed as empty values

Creating an Authorization Object

• The following grant would be required to allow user, nos_user permissions to create authorization objects in database sampledb:

GRANT AUTHORIZATION ON sampledb to nos_user;

• Using the credentials (taken from prior READ_NOS code sample),

...
AUTHORIZATION ('{"Access_ID":"AKIATZSC8AVA4PNC6VLC"                   ,"Access_Key":"qpGYpDMFiKgPczxEJgKgxFO9cUhiiuqJFESl61Gw"}')
...

nos_user would create a system-wide authorization object with name sampledb.auth_S3 by executing:

CREATE AUTHORIZATION sampledb.auth_s3
USER 'AKIATZSC8AVA4PNC6VLC'
PASSWORD 'qpGYpDMFiKgPczxEJgKgxFO9cUhiiuqJFESl61Gw';
;
​

• To grant other database users (for example, nos_user2)access to use the authorization, nos_user (or a database admin) would need to grant the following:

.logon nos_user;
...
GRANT EXECUTE ON sampledb.auth_s3 to nos_user2;

Using Authorizations with READ_NOS Table Operator

To use the new authorization sampledb.auth_s3 with the initial code sample, the hard-coded IAM Access Key is replaced with the authorization name:

SELECT TOP 5 *
FROM
    READ_NOS ( 
USING 
    LOCATION ('/s3/my-private-surface-water-data-store.s3.us-west-1.amazonaws.com/CSVDATA/09380000/2018/06/27.csv') 
      AUTHORIZATION (sampledb.auth_s3)
    RETURNTYPE ('NOSREAD_RECORD') 
) AS D

Using Authorizations with Foreign Tables

The following example shows the authorization sampledb.auth_s3 being used in foreign table definition sampledb.sample_data.

CREATE FOREIGN TABLE sampledb.sample_data
 ,EXTERNAL SECURITY sampledb.auth_s3
 USING (LOCATION ('/s3/my-private-surface-water-data-store.s3.us-west-1.amazonaws.com/CSVDATA/09380000/2018/06/27.csv') );

Contents of the file/key 27.csv should be auto-discovered, provided there are no record format errors.

Creating a Function Mapping Wrapper for READ_NOS Operator

Function mappings can be used as code “wrappers” for NOS operators (such as READ_NOS). They reduce the need for repeating common parameters.

For example, take the READ_NOS syntax element ROWFORMAT, which describes the format of an external row in an object store. For a store containing csv data, READ_NOS select statement would include the following.

ROWFORMAT('{"field_delimiter":",", "record_delimiter":"\n"}')

If there were many external stores containing data with the same row format, repetition could be avoided by including the above in a function mapping.

Requirements for Authorizations when used with Function Mapping

What would happen if the authorization created in previous sections, sampledb.auth_s3 is used when defining a function mapping for READ_NOS?

Function mappings used as READ_NOS wrappers, must contain the clause [INVOKER | DEFINER] TRUSTED] .

https://docs.teradata.com/r/Teradata-VantageTM-SQL-Operators-and-User-Defined-Functions/July-2021/Table-Operators/READ_NOS/READ_NOS-Syntax-Elements).

This would imply that an authorization object would also need to include this clause, otherwise the function mapping would be invalid.

Create Authorization for use with Function Mapping

Run the following to create a shared (DEFINER) authorization named auth_s3_func_map in database sampledb:

DATABASE sampledb;
​
REPLACE AUTHORIZATION auth_s3_func_map
DEFINER TRUSTED 
USER 'AKIATZSC8AVA4PNC6VLC' 
PASSWORD 'qpGYpDMFiKgPczxEJgKgxFO9cUhiiuqJFESl61Gw';
;

Create the function mapping, using this authorization. Note that option DEFINER is used.

DATABASE sampledb;
​
REPLACE FUNCTION MAPPING sampledb.read_nos_func_map
FOR READ_NOS EXTERNAL SECURITY DEFINER TRUSTED auth_s3_func_map
USING
/* include common parameters to avoid repeating in other code*/
ROWFORMAT('{"record_delimiter":"\n", "character_set":"LATIN"}'),
RETURNTYPE('NOSREAD_RECORD'), 
LOCATION, 
ANY IN TABLE;

To use the function mapping:

SELECT TOP 5 * 
FROM sampledb.read_nos_func_map (
USING
  LOCATION ('/s3/my-private-surface-water-data-store.s3.us-west-1.amazonaws.com/CSVDATA/09380000/2018/06/27.csv')
) AS dt;

The function can also be used with other S3 stores with similar attributes:

SELECT TOP 5 * 
FROM sampledb.read_nos_func_map (
USING
  LOCATION ('/s3/some-other-bucket.s3.us-west-1.amazonaws.com/dataset.csv')
) AS dt;

Conclusion

• Authorizations provide a secure mechanism for working with external store credentials
• Starting from Vantage 17.10, the new system-wide authorization model was introduced
• Function mappings used as NOS operator wrappers, need to include [INVOKER | DEFINER] TRUSTED]