DEFINER and INVOKER Authorizations | CREATE or REPLACE AUTHORIZATION | Vantage - DEFINER and INVOKER Authorizations - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

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
2023-07-11
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1184
lifecycle
latest
Product Category
Teradata Vantage™

An external routine with an EXTERNAL SECURITY clause DEFINER authorization always uses the OS user authorization that is associated with the creator of the authorization object.

An external routine with an EXTERNAL SECURITY clause INVOKER authorization uses the OS user authorization that is associated with the database user who invokes it.

Both routines require external authorization. The difference is in which OS user authorization the routine uses when it runs:
  • All use the same OS user authorization, that of the user who created the authorization, when the routine is defined with a DEFINER authorization.
  • All use a different OS user authorization, that of the user who invokes the routine, when the routine is defined with an INVOKER authorization.

The following table summarizes these differences:

WHEN an external routine is to be run with the … THEN you should specify this type of authorization …
same OS user authorization independent of the user who runs the routine DEFINER.
OS user authorization as specified by the user who runs the routine INVOKER.

Note that when you define an INVOKER authorization, the database creates a second authorization named INVOKER_AUTH under the same database (see the report on the next page).

There can only be one INVOKER authorization per database, so when one is defined, the database creates one entry using the specified name and another, duplicate, entry using the name INVOKER_DEFAULT, both of which are stored in DBC.TVM. Apart from their names, the two INVOKER default entries are identical.

When you specify an EXTERNAL SECURITY INVOKER clause for a UDF, no authorization name exists. the database looks up the INVOKER_DEFAULT name in DBC.TVM for the current database, where it finds a correct entry because there can only be one per database.

If you DROP AUTHORIZATION specifying the name you created for it, the database drops both entries. See Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.

SELECT *
FROM DBC.authorizationsV
ORDER BY databasename;

Results for using DROP in AUTHORIZATION