Teradata Database SQL Data Definition Language Detailed Topics

Teradata Database
Programming Reference


Providing Security for User‑Written External Routines

Authorization definitions permit users to issue operating system I/O calls from within an external routine (see “CREATE FUNCTION (External Form)/ REPLACE FUNCTION (External Form)” on page 240, “CREATE METHOD” on page 403, and “CREATE PROCEDURE (External Form)/ REPLACE PROCEDURE (External Form)” on page 422). The ANSI SQL:2011 specification collectively refers to user‑written non‑SQL modules as external routines.

Teradata Database requires any external routine that is designed to perform operating system I/O to run in protected mode as a separate process than runs under an explicitly specified user ID (see “Protected and Unprotected Execution Modes” on page 246).

The system enforces this restriction for the following reasons:

  • Operating system I/O calls need a context to be able to access data and to determine which data they can access. The only way to do this is to run the external routine as a separate process under the authorization of a specific user.
  • It is not appropriate to permit an external routine to execute in the context of either of the following.
  • An AMP Worker Task.
  • The Parser component of the parsing engine.
  • These are not appropriate because an AWT thread can be aborted by means of a simple transaction abort, which in turn can cause memory leaks and hanging file handles.

    None of this behavior is appropriate for a user‑written routine. Authorization objects provide a flexible, yet robust, scheme for providing the authorizations required by these external routines without exposing the system to the these potential problems.

    The principal difference between an external routine running in protected mode (see “ALTER FUNCTION (External Form)” on page 15, “ALTER METHOD” on page 20, or “ALTER PROCEDURE (External Form)” on page 24) or in secure mode is that when an external routine runs in protected mode, it always runs as the OS user tdatuser, while an external routine that runs in secure mode can run as any OS user you want to associate with an external authorization. While tdatuser has no special privileges, an OS user associated with an external authorization can have any privileges on OS files you want to assign to it. All that is required is that the OS user with special privileges be specified in the EXTERNAL SECURITY clause of the SQL definition for the external routine associated with it (see “CREATE FUNCTION (External Form)/ REPLACE FUNCTION (External Form)” on page 240, “ALTER METHOD” on page 20, or “CREATE PROCEDURE (External Form)/ REPLACE PROCEDURE (External Form)” on page 422 for details).