17.10 - Example: Specifying SQL SECURITY—INVOKER - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1144-171K
Language
English (United States)

The INVOKER logic uses the current top of the authorization stack to check for privileges, and the authorization identifier sitting on the top of the stack is used as the default qualifier for implicit qualification of any unqualified object references within the SQL statements in the procedure. When you specify SQL SECURITY INVOKER, Vantage uses the privileges on the top of the stack.

Static SQL

For static SQL, Vantage checks the privileges of the invoker during the creation of the procedure.

AT this time … Vantage verifies the following privileges in the order indicated …
compilation Creator has the CREATE PROCEDURE privilege on the SYSLIB database.

Required privileges for creator on referenced objects but generates only warnings and allows stored procedure creation to succeed.

execution

EXECUTE PROCEDURE privilege for invoker.

Required privileges for invoker on referenced objects and fails execution if privileges are missing.

For example, the DBC user creates the databases, tables, and user as follows:

     CREATE DATABASE db1 AS PERM = 1e6;
     CREATE DATABASE db2 AS PERM = 1e6;

     CREATE TABLE db1.tab1(a INT, b INT);
     CREATE TABLE db2.tab1(a INT, b INT);

     CREATE DATABASE testdb1 AS PERM = 1e6;

     CREATE USER User1 AS PERM = 1e6 PASSWORD = User1;

     GRANT CREATE PROCEDURE ON testdb1 TO User1;
     GRANT EXECUTE PROCEDURE ON testdb1 TO User1;
Use a text editor to create the stored procedure as follows:
     sp1.spl
     -------
     REPLACE PROCEDURE testdb1.sp1() 
     SQL SECURITY INVOKER 
     BEGIN 
     delete from db1.tab1; 
     insert into db1.tab1 select * from  db2.tab1;
     END;

Then, User1 compiles the stored procedure. The stored procedure creation is successful with warnings:

     .compile file = sp1.spl

     *** Procedure has been created. 2 Errors/Warnings. 
     *** Warning: 5527 Stored Procedure Created with Warnings.
     *** Total elapsed time was 1 second.

     Warnings reported during compilation
     ------------------------------------
     SPL5000:W(L4), E(3523):The user does not have DELETE access to db1.tab1.
     SPL5000:W(L5), E(3523):The user does not have SELECT access to db2.tab1.

Use the SHOW PROCEDURE statement to verify the creation of the procedure:

SHOW PROCEDURE testdb1.sp1;

 *** Text of DDL statement returned. 
 *** Total elapsed time was 1 second.

----------------------------------------------
REPLACE PROCEDURE testdb1.sp1() 
SQL SECURITY INVOKER 
BEGIN 
delete from db1.tab1; 
insert into db1.tab1 select * from  db2.tab1;
END;

The privileges of the INVOKER are checked during execution and fails without the required privileges.

Call testdb1.sp1();
 *** Failure 3523 SP1:The user does not have DELETE access to db1.tab1.
 *** Total elapsed time was 1 second.

Dynamic SQL

For dynamic SQL, there are no privileges that can be checked during the creation of the procedure. Vantage generates a warning message only if the object does not exist. The system checks all privileges during the execution of the procedure.

The following example shows the INVOKER case for dynamic SQL. In this example, user_1 creates procedure dyn_dml in the SYSLIB database, and user_2 calls SYSLIB.dyn_dml.

Vantage verifies the following privileges for this example.

AT this time … Vantage verifies the following privileges in the order indicated …
compilation user_1 has the CREATE PROCEDURE privilege on the SYSLIB database.
execution user_2 has the EXECUTE PROCEDURE privilege on the SQL procedure SYSLIB.dyn_dml.

user_2 has the INSERT privilege on table user_2.t1.

     .LOGON user_1,user_1
     .COMPILE FILE sp.spl
     /* sp.spl file
     CREATE PROCEDURE SYSLIB.dyn_dml()
     SQL SECURITY INVOKER
     BEGIN
       CALL dbc."sysexecsql" ('INSERT INTO t1 (1,1);');
     END;
     /*
     .LOGON user_2,user_2
     CALL SYSLIB.dyn_dml();