15.00 - DEFINER and INVOKER Authorizations - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1184-015K
Language
English (United States)

DEFINER and INVOKER Authorizations

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, Teradata 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, Teradata 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.

    The reason for this is that when you specify an EXTERNAL SECURITY INVOKER clause for a UDF, no authorization name exists, so there is no way for the system to look it up. To work around this, Teradata 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 the AUTHORIZATION specifying the name you created for it, Teradata Database drops both entries (see “DROP AUTHORIZATION” on page 792 and SQL Data Definition Language).

    SELECT *
    FROM DBC.authorizationsV
    ORDER BY databasename;
     
    DatabaseName  AuthorizationName AuthorizationId  TableKind  Version  AuthorizationType AuthorizationSubType OSDomainName OSUserName
    fisjo04       INVOKER_DEFAULT   0000E3060000     X          1        I                 D                                 fisjo04
    fisjo04       JohnAuth          0000E2060000     X          1        I                 D                                 fisjo04
    sinan04       AndreiAuth        0000E4060000     X          1        D                 N                                 sinan04
    venba01       INVOKER_DEFAULT   0000E8060000     X          1        I                 D                                 venba01
    venba01       VenkatAuth        0000E7060000     X          1        I                 D                                 venba01
    vuolo01       INVOKER_DEFAULT   0000E6060000     X          1        I                 D                                 vuolo01
    vuolo01       LouAuth           0000E5060000     X          1        I                 D                                 vuolo01
    vuolo01       TestAuth          0000DC060000     X          1        D                 N                                 vuolo01