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/invoker trusted or simplified credentials and associate it with READ_NOS.
- 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.
- Replace the function mapping READ_NOS_FM for the function TD_SYSFNLIB.READ_NOS using the authorization object S3Definer_auth:
REPLACE FUNCTION MAPPING READ_NOS_FM FOR TD_SYSFNLIB.READ_NOS EXTERNAL SECURITY DEFINER TRUSTED S3Definer_auth USING BUFFERSIZE , HEADER , ROWFORMAT , MANIFEST , SAMPLE_PERC , RETURNTYPE , LOCATION , ANY IN TABLE;
- Run READ_NOS_FM function mapping to read data from an S3 CSV bucket at the LOCATION specified. This SQLE call returns the data as a dataset with a single column named payload:
SELECT payload FROM READ_NOS_FM ( ON (SELECT cast(NULL as DATASET INLINE LENGTH 64000 STORAGE FORMAT CSV)payload) USING LOCATION('location') ) as d;
location is the bucket location. For example: /s3/s3.amazonaws.com/stsassumerole-testbucket/BILLING_STATMNT_CHARGE.csv
- Run READ_NOS_FM function mapping to read all data from an S3 CSV bucket at the LOCATION specified:
SELECT * FROM READ_NOS_FM ( USING LOCATION('/s3/s3.amazonaws.com/stsassumerole-testbucket/BILLING_STATMNT_CHARGE.csv') ) as d;
- Create the INVOKER authorization object with the name S3Invoker_auth:
CREATE AUTHORIZATION S3Invoker_auth AS INVOKER TRUSTED USING AUTHSERVICETYPE 'ASSUME_ROLE' ROLENAME 'arn:aws:iam::844102931058:role/STSAssumeRole_TestRole' EXTERNALID '3f68bc61-a455-4742-8039-8a03f0600db5' [ DURATION_SECONDS '7200' ];
- Replace the function mapping READ_NOS_FM for the function TD_SYSFNLIB.READ_NOS using the authorization object S3Invoker_auth:
REPLACE FUNCTION MAPPING READ_NOS_FM FOR TD_SYSFNLIB.READ_NOS EXTERNAL SECURITY INVOKER TRUSTED S3Invoker_auth USING BUFFERSIZE , HEADER , ROWFORMAT , MANIFEST , SAMPLE_PERC , RETURNTYPE , LOCATION , ANY IN TABLE;
- Run READ_NOS_FM function mapping to read data from an S3 CSV bucket at the LOCATION specified. This SQLE call returns the data as a dataset with a single column named payload:
SELECT payload FROM READ_NOS_FM ( ON (SELECT cast(NULL as DATASET INLINE LENGTH 64000 STORAGE FORMAT CSV)payload) USING LOCATION('/s3/s3.amazonaws.com/stsassumerole-testbucket/BILLING_STATMNT_CHARGE.csv') ) as d;
- Run READ_NOS_FM function mapping to read all data from an S3 CSV bucket at the LOCATION specified:
SELECT * FROM READ_NOS_FM ( USING LOCATION('/s3/s3.amazonaws.com/stsassumerole-testbucket/BILLING_STATMNT_CHARGE.csv') ) as d;
- Create the authorization object with the name S3_auth:
CREATE AUTHORIZATION S3_auth USING AUTHSERVICETYPE 'ASSUME_ROLE' ROLENAME 'arn:aws:iam::844102931058:role/STSAssumeRole_TestRole' EXTERNALID '3f68bc61-a455-4742-8039-8a03f0600db5' [ DURATION_SECONDS '7200' ];
- Use READ_NOS_FM using the authorization object to read data from an S3 CSV bucket. Note, the READ_NOS_FM command is implicit in the following statement:
SELECT * FROM ( LOCATION = 'location' AUTHORIZATION = S3_auth) as dt;
Where location is the bucket location. For example: /s3/s3.amazonaws.com/testuser1-sts-test/csv_default.csv.