17.05 - privilege_option - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1144-175K
Language
English (United States)

The SQL SECURITY clause is supported only for those external procedures where both of the following statements are true:

  • The procedure has one of the following AppCategory codes in DBC.UDFInfo :

    C        (CLIv2)

    J          (Java)

    N        (.NET)

    O        (ODBC)

The only non-valid AppCategory code is S.

See Teradata Vantage™ - Data Dictionary, B035-1092 for details.

The SQL ACCESS clause in the procedure definition specifies one of the following options that supports SQL.

  • CONTAINS SQL
  • MODIFIES SQL DATA
  • READS SQL DATA

The SQL ACCESS option cannot be NO SQL. If you specify NO SQL, Vantage aborts the request and returns an error to the requestor.

SQL DCL and DDL statements for administering row-level security are not allowed in a stored procedure.

The following bullets list the valid privilege options.

  • CREATOR
  • DEFINER
  • INVOKER
  • OWNER

See CREATE PROCEDURE (External Form) in Teradata Vantage™ - SQL Data Definition Language Detailed Topics , B035-1184 for the definitions of the privilege options.

Vantage treats procedures as follows when the procedure is defined as a DEFINER whether explicitly or by default. This is for the case when the CREATOR of the procedure is not its OWNER.

  • Vantage checks the privileges of the CREATOR during compilation of the procedure. The CREATOR must have the appropriate privilege for any object that a statement in the procedure references.
  • Vantage checks the privileges of the OWNER (the containing database or user for the procedure) during compilation, and if the OWNER is missing any privileges for any object that is referenced by a statement in the procedure, Vantage returns a warning message.
  • During execution, Vantage checks the OWNER privileges for any object referenced by any statement in the procedure. Any access failures caused by not having the appropriate privileges return an error to the procedure, which it can handle if it is written to do so.
  • Vantage grants the DROP and EXECUTE privileges to a UDF created in a database or user different from the CREATOR. An OWNER always has the implicit privilege to drop any object it owns. If the OWNER wants to execute the UDF, then it must grant the EXECUTE FUNCTION privilege to that function.

The following rules apply to all procedures:

  • Objects referenced by any statement in the procedure need not have the WITH GRANT OPTION privilege. They require only the GRANT privilege on the referenced object.
  • Vantage check CREATOR and OWNER privileges on DDL statements during execution.

The following paragraphs summarize how Vantage treats privilege violations for procedures during procedure compilation.

For DCL and DDL requests, Vantage checks the CREATOR and OWNER privileges during compilation.

The system returns warning messages to the procedure for any privilege violations that occur.

For DML requests, Vantage checks the CREATOR and OWNER privileges during compilation:

  • For the CREATOR, the system returns error messages to the procedure for any privilege violations that occur.
  • For the OWNER, the system returns warning messages to the procedure for any privilege violations that occur.

For dynamic SQL requests, Vantage does not check any privileges.

The following bullets summarize how Vantage treats privilege violations for procedures during procedure execution.

  • For DCL and DDL requests, Vantage checks the CREATOR and OWNER privileges during execution.

    The system returns error messages to the procedure for any privilege violations that occur.

  • For DML requests, Vantage checks the OWNER privileges during execution.

    The system returns error messages to the procedure for any privilege violations that occur.

  • For dynamic SQL requests, Vantage checks the CREATOR and OWNER privileges during execution.