17.05 - UDT-Related Privileges - Teradata Database

Teradata Vantageā„¢ - SQL Data Control Language

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
17.05
created_date
June 2020
category
Programming Reference
featnum
B035-1149-170K

Initially, only user DBC has UDT privileges on SYSUDTLIB, which user DBC holds implicitly. Any other user or role must be explicitly granted UDT privileges on SYSUDTLIB to execute SQL statements that involve UDTs.

There are three UDT-related privileges:
  • UDTMETHOD (see UDTMETHOD Privilege).

    UDTMETHOD allows a user to use, create, drop, or modify any UDT and its methods without restriction.

    You can only grant this privilege at the database level, specifically on the SYSUDTLIB database: there are no UDT object-level privileges for UDTMETHOD.

  • UDTTYPE (see UDTTYPE Privilege).

    UDTTYPE allows a user to create, alter and drop UDTs in addition to the privileges UDTUSAGE permits.

    You can only grant this privilege at the database level: there are no UDT object-level privileges for UDTTYPE.

  • UDTUSAGE (see UDTUSAGE Privilege).
    UDTUSAGE allows a user to execute all SQL statements that reference existing UDTs and their existing methods. This means that a user with the UDTUSAGE privilege on a particular UDT can perform the following operations:
    • Create a new table that is defined with a column having that UDT type.
    • Alter an existing table that is defined with a column having that UDT type.
    • Reference that UDT in a query, UDF, or procedures.
    • Execute all methods that are associated wholly with the UDT type.

      Methods that involve additional UDTs require you to have the UDTUSAGE privilege on any such UDTs as well.

    UDTUSAGE does not permit a user to perform any of the following operations:
    • Create new UDTs.
    • Drop existing UDTs.
    • Alter the ordering, casting, or transform behavior of existing UDTs.
    • Create new methods.
    • Drop or replace existing methods.

Unlike UDTMETHOD and UDTTYPE, you can grant UDTUSAGE on either a specific UDT (as TYPE UDT_name) or on the entire SYSUDTLIB database.