Supported & Unsupported DCL Statements in SQL Procedures | VantageCloud Lake - DCL Statements in SQL Procedures - Teradata Vantage

Teradata® VantageCloud Lake
Deployment
VantageCloud
Edition
Lake
Product
Teradata Vantage
Published
January 2023
ft:locale
en-US
ft:lastEdition
2024-12-11
dita:mapPath
phg1621910019905.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
phg1621910019905

See a list of supported and unsupported DCL statements for SQL procedures when using CREATE PROCEDURE, and the session mode impact on DCL statements in SQL procedures.

Supported DCL Statements

You can specify the following SQL DCL statements in an SQL procedure when the creator is also the immediate owner of the procedure. That is, an SQL procedure can contain DCL statements only if it is created in the creating in the database of the user.

  • GIVE
  • GRANT (all forms)
  • GRANT CONNECT THROUGH
  • GRANT LOGON
  • REVOKE (all forms)
  • REVOKE CONNECT THROUGH
  • REVOKE LOGON

Unsupported DCL Statements

You cannot create an SQL procedure that contains any DCL request that administers row-level security privileges.

Session Mode Impact on DCL Statements in SQL Procedures

The runtime behavior of DCL statements specified in SQL procedures depends on the session mode of the Teradata session in which the procedure is created.
  • A DCL statement specified within an explicit (user-defined) transaction in an SQL procedure in Teradata session mode must be the last statement in that transaction. Otherwise, the system raises a runtime exception.
  • When performing a procedure in ANSI session mode, each DCL statement specified in the procedure body must be followed by a COMMIT WORK request. Otherwise, the database aborts the request and raises a run time exception.