You can use function mapping to hide credentials in an authorization object. After you create the function mapping, you can use the mapped authorization object in queries and avoid exposing your credentials.
For more information about function mapping, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144 and Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.
For example, the user needs these privileges to run the following commands:
- CREATE AUTHORIZATION
- CREATE FUNCTION
- Log in as a user with the privileges needed to create an authorization object and function mapping.
-
If not already done, create an authorization object called DefAuth:
CREATE AUTHORIZATION DefAuth AS DEFINER TRUSTED USER 'YOUR-ACCESS-KEY-ID' PASSWORD 'YOUR-SECRET-ACCESS-KEY';
-
Create the function mapping:
CREATE FUNCTION MAPPING READ_NOS_FM FOR READ_NOS EXTERNAL SECURITY DEFINER TRUSTED DefAuth USING BUFFERSIZE, SAMPLE_PERC, ROWFORMAT, RETURNTYPE, HEADER, MANIFEST, LOCATION, STOREDAS, FULLSCAN, ANY IN TABLE;
-
Use the function mapping READ_NOS_FM in a SELECT query:
SELECT TOP 2 LOCATION FROM READ_NOS_FM ( ON (SELECT cast (NULL as JSON)) USING LOCATION('YOUR-STORAGE-ACCOUNT') RETURNTYPE('NOSREAD_RECORD') ) AS D;
Where YOUR-STORAGE-ACCOUNT is the location of your external storage; for example, /s3/td-usgs.s3.amazonaws.com/JSONDATA/.
Your result will be similar to the following:
Location -------------------------------------------------------------- /S3/td-usgs.s3.amazonaws.com/JSONDATA/09380000/2018/06/28.json /S3/td-usgs.s3.amazonaws.com/JSONDATA/09380000/2018/06/29.json
Create the Function Mapping
Verify the Function Mapping