Rules for SQL Procedure Privileges - Teradata Vantage

Following are the rules for SQL procedure privileges and how they are checked:

Dynamic and static SQL are governed by these rules.
Generally, the term dynamic SQL applies to any SQL statement whose complete text is not known until run time.
The default privilege option for SQL procedures is DEFINER.
When the creator of a procedure is also its immediate owner, all static DML and DCL statements within the procedure are valid.

The following table summarizes the rules for SQL procedure privilege assignment.

Note the following things about the column headings.

For the column Owner/Creator Relationship, the word owner refers to the immediate owner.
For the column Privilege Checking Done at This Time: Compilation, the appropriate CREATE and DROP privileges are also checked during compilation.
For the column Privilege Checking Done at This Time: Execution, the EXECUTE PROCEDURE privilege is also checked at run time.

SQL SECURITY Privilege Option	Dynamic or Static SQL Statement?	Owner/Creator Relationship	Privilege Checking Done at This Time		Default Database or User
SQL SECURITY Privilege Option	Dynamic or Static SQL Statement?	Owner/Creator Relationship	Compilation	Execution	Default Database or User
DEFINER	Dynamic	O is not C	None	O and C	C
	Dynamic	O is C	None	O	C
	Static	O is not C	O and C	O and C	C
	Static	O is C	O	O	C
INVOKER	Dynamic	O is not C	None	I	I
	Dynamic	O is C	None	I	I
	Static	O is not C	C	I	I
	Static	O is C	C	I	I
OWNER	Dynamic	O is not C	COP	O	O
	Dynamic	O is C	None	O	O
	Static	O is not C	COP and O	O	O
	Static	O is C	O	O	O
CREATOR	Dynamic	O is not C	None	C	C
	Dynamic	O is C	None	C	C
	Static	O is not C	C	C	C
	Static	O is C	C	C	C

where:

Symbol or Text String …	User Privileges Required to Submit a CREATE PROCEDURE or REPLACE PROCEDURE Request
None	None.
Error	One or more privileges it did not have at the time the CREATE PROCEDURE or REPLACE PROCEDURE request was compiled. Because the request aborts when this occurs, the cells for Privilege Check at Execution Time and Default Database are empty.
COP	CREATE OWNER PROCEDURE.
C	Privileges of the creator of the procedure.
I	Privileges of the invoker of the procedure.
O	Privileges of the owner of the procedure.
O and C	Privileges of the owner of the procedure and those of its creator.
O or C	Those of the owner of the procedure or those of its creator.
CREATOR	Those of the creator of the procedure regardless of its containing user or database.
DEFINER	Those currently defined. This is the default.
INVOKER	Those at the top of the current stack.
OWNER	Those of the immediate owner of the procedure (the user or database in which the procedure is contained).

This symbol or text string …	Means that the immediate owner and creator of the procedure are …
O is not C	different.
O is C	the same.

Using the first row for the INVOKER SQL SECURITY option as a model, you would interpret the table as follows: For the case when an SQL statement is invoked dynamically and the immediate owner of its containing procedure is not the same user as its creator, no privilege checking is done at the time the procedure is compiled, the privileges of the invoker of the procedure are checked at run time, and the default database or user that is used to implicitly qualify any unqualified object references within the SQL statements in the procedure body is that of the invoker.

The following examples show how the various SQL SECURITY options can be used:

Rules for SQL Procedure Privileges - Teradata Vantage - Analytics Database

SQL Data Definition Language Syntax and Examples