15.10 - Example: Specifying SQL SECURITY—INVOKER - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

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, Teradata Database uses the privileges on the top of the stack.

Static SQL

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

AT this time … Teradata Database 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. Teradata Database 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.

Teradata Database verifies the following privileges for this example.

AT this time … Teradata Database 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();