Basic Concepts for using Authorization Objects with Teradata NOS and S3

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
...
AUTHORIZATION ('{"Access_ID":"AKIATZSC8AVA4PNC6VLC" ,"Access_Key":"qpGYpDMFiKgPczxEJgKgxFO9cUhiiuqJFESl61Gw"}')
...
...
AUTHORIZATION (sampledb.auth_s3)
...

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'
;
  • 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"}')
...
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') );

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.

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

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?

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';
;
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;
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;
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]

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Tony Tannous

Tony Tannous

62 Followers

Learner. Interests include Cloud and Devops technologies.