Checking Privileges for Stored Procedures | Teradata Vantage - Checking Privileges for Stored Procedures - Teradata Vantage - Analytics Database

SQL Stored Procedures and Embedded SQL
Deployment
VantageCloud
VantageCore
Edition
VMware
Enterprise
IntelliFlex
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
ft:locale
en-US
ft:lastEdition
2023-10-30
dita:mapPath
frc1628111662093.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
rjx1472253414573
lifecycle
latest
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. Vantage 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, Vantage 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, Vantage 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 Vantage 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, Vantage 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 Information

  • SQL SECURITY clause, see the information about CREATE 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.