17.05 - Example: Specifying a SQL SECURITY—DEFINER - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

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

The DEFINER option is the SQL SECURITY clause default.

The following rules apply to the DEFINER option.
  • Vantage checks the privileges of both the creator and the owner of the procedure at compilation and execution times.
  • Static DDL and DML statements are valid in a procedure definition even if the creator and owner are not the same.

    Vantage uses the name of the creating (definer) user as the default for implicit qualification of any unqualified object references within the procedure body.

The following example shows the DEFINER case for static 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.

user_1 has the INSERT privilege on user_1 on the database object user_1.t1.

The SYSLIB database has the INSERT privilege on the database object user_1 .t1.

execution user_2 has the EXECUTE PROCEDURE privilege on the SQL procedure SYSLIB.dyn_dml.

user_1 has the INSERT privilege on the database object user_1.t1.

The SYSLIB database has the INSERT privilege on the database object user_1.t1.

     .LOGON user_1,user_1
     .COMPILE FILE sp.spl
     /* sp.spl file
     CREATE PROCEDURE SYSLIB.static_dml()
     SQL SECURITY DEFINER
     BEGIN
       INSERT INTO t1
       SELECT 1,1;
     END;
     /*
     .LOGON user_2,user_2
     CALL SYSLIB.static_dml();

For the dynamic SQL case, Vantage verifies the following privileges.

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_1 has the INSERT privilege on the database object user_1.t1.

The SYSLIB database has the INSERT privilege on the database object user_1.t1.

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