15.10 - DEFINER - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

A keyword that associates an external routine with an operating system platform user to the database that contains the external routine.

If you specify DEFINER, then database_name or user_name must be the containing database or user for the external routine.

You can specify either a DEFINER or an INVOKER, but not both.

DEFAULT
An optional keyword modifier for the DEFINER keyword that associates this authorization with all external routines that do not specify the authorization name in the EXTERNAL SECURITY DEFINER clause of the following statements.
You can assign only one default DEFINER object per database. All others must have specific definer names.
If a default DEFINER already exists for the current or specified database, the system returns an error to the requestor.

Example: Creating a DEFINER Authorization

The following example creates a DEFINER authorization object with the name sales_processing for the user name salesmng and the user password mysecret.

    CREATE AUTHORIZATION sales_processing AS DEFINER
    USER 'salesmng'
    PASSWORD 'mysecret';

Teradata Database uses this OS platform user context to execute all external routines that contain an EXTERNAL SECURITY DEFINER clause when run under the same OS user authorization independent of the user who runs the routine.

Example: Creating a DEFINER Authorization for an External Security Clause

This example creates a specific definer authorization name sales with all external routines that contain the external security clause EXTERNAL SECURITY DEFINER sales. The authorization object name sales is part of the text specified for the external security clause. This specification causes the system to validate the authorization object SYSLIB.sales in the data dictionary and then, once it has been validated, to use the specified user context to run the external routine.

    CREATE AUTHORIZATION SYSLIB.sales AS DEFINER
     USER 'SalesDept'
     PASSWORD 'ikcerednep';

Example: Creating a Default DEFINER Authorization

This example creates a definer authorization with the name accounting in the SYSLIB database for the client user name accdept and user password nesuahkcotsk.

    CREATE AUTHORIZATION SYSLIB.accounting AS DEFINER DEFAULT
      USER 'accdept'
      PASSWORD 'nesuahkcotsk';

The example associates accounting as the DEFAULT authorization in the SYSLIB database with the specified client user name accdept.

Because accdept is the DEFAULT authorization object, the system associates it with all external routines that contain the EXTERNAL SECURITY DEFINER clause. All external routines in database SYSLIB that contain an EXTERNAL SECURITY DEFINER clause are executed using the user context defined by this request.

Recall that there can only be one default definer object in a database. All others must have specific names. If the DEFAULT already exists, the system returns an error to the requestor.