To create or execute a stored procedure, you must have the appropriate privileges to execute the SQL statements in the procedure and privileges to the database objects referenced in the stored procedure body. Teradata Database also checks for the appropriate CREATE and DROP privileges during the creation of the procedure, and the EXECUTE privilege during the execution of the procedure.
- CREATOR
- DEFINER
- INVOKER
- OWNER You must have the CREATE OWNER PROCEDURE privilege to specify the OWNER option if the creator is different from the immediate owner of the stored procedure.
- the privileges of the user that created the stored procedure (no matter where the stored procedure resides)
- the privileges of the current user that invoked the stored procedure
- the privileges of the immediate owner of the stored procedure (the user or database space where the stored procedure resides)
- the privileges of the creator and the owner of the stored procedure
The SQL SECURITY option also determines the default database used to implicitly qualify any unqualified object references within the SQL statements in the procedure body.
The SQL SECURITY clause is optional. If you do not include the clause, Teradata Database uses the SQL SECURITY DEFINER option as the default.
IF the SQL SECURITY option is... | THEN... |
---|---|
CREATOR |
|
DEFINER | for stored procedures with dynamic SQL, the following privileges are checked:
For stored procedures with static SQL, the following privileges are checked:
The default database used to implicitly qualify any unqualified object references within the SQL statements in the procedure body is that of the creator. |
INVOKER |
|
OWNER |
You must have the CREATE OWNER PROCEDURE privilege to specify this option if the creator is different from the immediate owner of the procedure.
|
Not specified | the DEFINER SQL SECURITY option is used as the default. |
If you are accessing Teradata Database through a proxy connection, privilege checking for stored procedures is still done based on the SQL SECURITY clause. The privileges of the proxy user are used for checking the required privileges of referenced SQL statements and objects only if the SQL SECURITY INVOKER option is specified.
Because stored procedures use the SQL SECURITY option to determine the default database, the default database is not updated if a proxy user is set in a stored procedure. If the query band with the proxy user remains when exiting the stored procedure, the session is set to the default database for the proxy user.
If the stored procedure includes a SET QUERY_BAND statement that sets a proxy user, Teradata Database validates the CONNECT THROUGH privilege of the trusted user when the stored procedure is executed.
If the CONNECT THROUGH privilege of the trusted user includes the WITH TRUST-ONLY option, then all SET QUERY_BAND statements that set, change, or remove a proxy user or proxy role must be performed from a trusted request. A SET QUERY_BAND statement in an
Related Topics
- SQL SECURITY clause, see “CREATE/REPLACE PROCEDURE” in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
- Granting the CONNECT THROUGH privilege to a trusted user, see Teradata Vantage™ - SQL Data Control Language, B035-1149.
- Setting a proxy user with SET QUERY_BAND, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.