Example: Using AWS Assume Role Authorization with WRITE_NOS - Analytics Database - Teradata Vantage

Assume Role gives existing AWS IAM users and service accounts temporary access to AWS resources in other accounts.

The following example shows how you can create an authorization with definer trusted or simplified credentials and associate it with WRITE_NOS.

1. Create the DEFINER authorization object with the name S3Definer_auth:

   CREATE AUTHORIZATION S3Definer_auth
   AS DEFINER TRUSTED
   USING
   AUTHSERVICETYPE 'ASSUME_ROLE'
   ROLENAME 'role_name'
   EXTERNALID 'external_id'
   [ DURATION_SECONDS 'duration_in_seconds' ];

   Where:

   role_name is the Amazon Resource Name (ARN) of the role to assume. For example: arn:aws:iam::844102931058:role/STSAssumeRole_TestRole

   external_id is is the external ID which is assuming the role. For example: 3f68bc61-a455-4742-8039-8a03f0600db5

   [OPTIONAL] duration_in_seconds is the duration in seconds of the role session. For example: 7200.

2. Replace the function mapping WRITE_NOS_FM for the function WRITE_NOS using the authorization object S3Definer_auth:

   REPLACE FUNCTION MAPPING WRITE_NOS_FM
   FOR WRITE_NOS EXTERNAL SECURITY DEFINER TRUSTED S3Definer_auth
   USING
   STOREDAS,
   LOCATION,
   MANIFESTFILE,
   MANIFESTONLY,
   COMPRESSION,
   OVERWRITE,
   NAMING,
   INCLUDE_ORDERING,
   INCLUDE_HASHBY,
   MAXOBJECTSIZE,
   ANY IN TABLE;

3. Create a small sample table:

   CREATE TABLE t11 (
   c1 SMALLINT, 
   c2 INT, 
   c3 VARCHAR(10) CHARACTER SET LATIN CASESPECIFIC, 
   c4 VARCHAR(10) CHARACTER SET UNICODE CASESPECIFIC, 
   c5 DATE, 
   c6 DATE
   );

4. Add data to the table:

   INSERT INTO t11(1, 11, 'store1', 'paper', DATE '2010-06-01', DATE '2010-07-01');
   INSERT INTO t11(2, 12, 'store1', 'pen', DATE '2010-06-01', DATE '2010-07-01');
   INSERT INTO t11(3, 13, 'store1', 'ipad', DATE '2010-06-01', DATE '2010-07-01');

5. Create the authorization object with the name AUTH_S3:

   CREATE AUTHORIZATION AUTH_S3
   USING
   AUTHSERVICETYPE 'ASSUME_ROLE'
   ROLENAME 'arn:aws:iam::844102931058:role/STSAssumeRole_TestRole'
   EXTERNALID '3f68bc61-a455-4742-8039-8a03f0600db5'
   [ DURATION_SECONDS '7200' ];

6. Run WRITE_NOS using the authorization object AUTH_S3. The following example selects data from the Teradata table named t11 and writes it to external object storage at the LOCATION specified. Finally, a count is done to verify the correct amount of data stored:

   SELECT count(*) FROM WRITE_NOS
   (ON (SELECT * FROM t11)
     USING
     LOCATION('/s3/s3.amazonaws.com/stsassumerole-testbucket/wr_test/')
     AUTHORIZATION(AUTH_S3)
     STOREDAS('PARQUET') ) as dt;