15.10 - SQL SECURITY privilege_option - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

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 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, Teradata Database 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 SQL Data Definition Language - Detailed Topics, B035-1184 for the definitions of the privilege options.

Teradata Database 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.

  • Teradata Database 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.
  • Teradata Database 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, Teradata Database returns a warning message.
  • During execution, Teradata Database 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.
  • Teradata Database 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.
  • Teradata Database check CREATOR and OWNER privileges on DDL statements during execution.

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

For DCL and DDL requests, Teradata Database 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, Teradata Database 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, Teradata Database does not check any privileges.

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

  • For DCL and DDL requests, Teradata Database 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, Teradata Database checks the OWNER privileges during execution.

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

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