UDTUSAGE Privilege - Analytics Database - Teradata Vantage

SQL Data Control Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
sgu1628111251052.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
dvv1472243528022
lifecycle
latest
Product Category
Teradata Vantageā„¢

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;