16.20 - UDTUSAGE Privilege - Teradata Vantage NewSQL Engine

Teradata Vantageā„¢ SQL Data Control Language

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
created_date
March 2019
category
Programming Reference
featnum
B035-1149-162K

The UDTUSAGE privilege allows a user to use a UDT in a table or view and to execute all methods associated wholly with that UDT. They cannot create or delete UDTs, create, alter, or delete methods, or alter the behavior of a UDT with respect to ordering, casting, or transform functionality.

You can grant UDTUSAGE at either the database or UDT object level.

Level of the Grant Where UDTUSAGE Must Be Granted
Database SYSUDTLIB database only.
UDT object Name of the UDT.
A user with the UDTUSAGE privilege on a specific UDT can perform the following statements and operations:
  • CREATE TABLE or ALTER TABLE for tables that contain columns that use that UDT.
  • Reference that UDT in queries, UDFs, and procedures that reference the UDT.
  • Execute all methods associated wholly with the UDT.

    Methods that involve other UDTs also require the UDTUSAGE privilege on those UDTs.

With regard to nested structured UDTs, be aware that privileges are not automatically inherited from their parents. For example, if you have been granted UDTUSAGE on a top-level structured UDT attribute, you can then specify a column with that type in a create table operation. However, that privilege does not grant you the ability to use observer or mutator methods on any of the structured attributes in the lower layers of that UDT, nor can you invoke any of the methods defined for those lower layered attributes.

To enable that sort of access, you must grant UDTUSAGE explicitly on each individual UDT component of the nested UDT type or grant UDTUSAGE explicitly on the SYSUDTLIB database.

UDTUSAGE is not an automatically granted privilege. A user must either be granted this privilege explicitly or acquire it through a role. Users who are granted UDTUSAGE WITH GRANT option can then grant others the UDTUSAGE privilege, optionally with the WITH GRANT option privilege.

UDTUSAGE is represented by the code UU in the AccessRight column of the DBC.AccessRights table.

User DBC has implicit UDT privileges on the SYSUDTLIB database.

Any other user or role that needs to access the UDT-related objects in SYSUDTLIB must first be granted the appropriate UDT privileges on SYSUDTLIB or a specific UDT in the SYSUDTLIB database explicitly.

The following GRANT request grants the following respective privileges:
  • UDTUSAGE on the SYSUDTLIB database to the user named tester1.
  • UDTUSAGE on the UDT named circle to the user named tester3.
  • UDTUSAGE on the UDT named square in database SYSUDTLIB to the user named tester4.
    GRANT UDTUSAGE
    ON SYSUDTLIB
    TO tester1;
    
    GRANT UDTUSAGE
    ON TYPE circle
    TO tester3;
    
    GRANT UDTUSAGE
    ON TYPE SYSUDTLIB.square
    TO tester4;