UDT-Related Privileges - Advanced SQL Engine - Teradata Database

SQL Data Control Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
lmb1556233084626.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1149
lifecycle
previous
Product Category
Teradata® Vantage™ NewSQLEngine

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.