Example: Creating and Using an EXTERNAL SECURITY Clause in a UDF - Analytics Database - Teradata Vantage

SQL Data Definition Language Syntax and Examples

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-10-04
dita:mapPath
jco1628111346878.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
mdr1472255012272
lifecycle
latest
Product Category
Teradata Vantage™

Suppose you have defined the following external authorization object:

     CREATE AUTHORIZATION DEFINER sales
     USER 'salesdept'
     PASSWORD 'secret';

You now create the following C UDF:

     CREATE FUNCTION sales_collect (
       store_number INTEGER, 
       item_no      INTEGER)
     RETURNS INTEGER
     LANGUAGE C
     NO SQL
     EXTERNAL 'cs!salecol!salecollector.c'
     PARAMETER STYLE SQL
     EXTERNAL SECURITY DEFINER sales;

This function collects data associated with sales for a given store for a given item number (item_no) and returns the number of items sold. One possible, albeit contrived, scenario would have the function communicating with the store via a network interface. The function is created using the DEFINER context. This means that when a user logs onto its database account and executes an SQL request that invokes this function, it uses the logon ID associated with the sales authorization object, not the user that is executing the invoking SQL request. In this example, OS user salesdept has access to the external data the function reads to obtain the information it needs.