15.10 - SQL SECURITY - 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

Optional SQL security privilege set you assign to procedure_name.

See “CREATE PROCEDURE (SQL Form)” in SQL Data Definition Language - Detailed Topics, B035-1184 for descriptions of the privilege options.

The keyword you specify for privilege_option determines the privileges that Teradata Database checks for the underlying objects specified in the request.

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 requests during execution.
CREATOR
Assign the privileges of the creator of the procedure regardless of its containing database or user.
DEFINER
Assign the privileges of the definer of the procedure. This is the default privilege option.
Teradata Database treats procedures as follows when the procedure is defined as a DEFINER whether explicitly or by default, for example, 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, that is, the containing database or user for the procedure, during compilation. The OWNER must have the appropriate privileges for any object that is referenced by a statement in the procedure.
  • 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.
INVOKER
Assign the privileges of the user at the top of the current execution stack.
OWNER
Assign the privileges of the owner of the procedure, which are the privileges possessed by its containing database or user.
You cannot specify the OWNER option for this clause unless you have the explicitly granted CREATE OWNER PROCEDURE privilege to permit you to create an SQL procedure in a database or user other than your default database or user.