Checking Privileges for Stored Procedures | Teradata Vantage - Checking Privileges for Stored Procedures - Advanced SQL Engine - Teradata Database

SQL Stored Procedures and Embedded SQL

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
xqq1557098602407.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1148
lifecycle
previous
Product Category
Teradata Vantage™

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.

You can specify how privilege checking is handled by defining the SQL SECURITY clause in the CREATE/REPLACE PROCEDURE statement. When the stored procedure is compiled or executed, Teradata Database checks for the required privileges based on the following options of the SQL SECURITY clause:
  • 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 SQL SECURITY option determines which of the following privileges are checked when the stored procedure is compiled or executed:
  • 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
  • the privileges of the user that created the stored procedure are checked, no matter where the stored procedure resides.
  • the default database used to implicitly qualify any unqualified object references within the SQL statements in the procedure body is that of the creator.
DEFINER for stored procedures with dynamic SQL, the following privileges are checked:
  • If the creator is different from the immediate owner, then both the creator and owner privileges are checked upon execution of the procedure.
  • If the creator is the same as the immediate owner, then either the creator or the owner privileges are checked upon execution of the procedure.
For stored procedures with static SQL, the following privileges are checked:
  • If the creator is different from the immediate owner, then both the creator and owner privileges are checked upon compilation and execution of the procedure.
  • If the creator is the same as the immediate owner, then either the creator or the owner privileges are checked upon compilation and execution of the procedure.

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
  • the privileges of the user creating the stored procedure are checked when the procedure is created.
  • the privileges of the user that called the stored procedure are checked upon execution of the procedure.
  • the default database used to implicitly qualify any unqualified object references within the SQL statements in the procedure body is that of the current user.
OWNER
  • the privileges of the immediate owner of the stored procedure 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 immediate 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

For more information about:
  • 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.