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

When you specify SQL SECURITY CREATOR, Vantage verifies and applies the privileges of the user who created the procedure.

The system uses the name of the creating (definer) user as the default qualifier for implicit qualification of any unqualified object references within the SQL statements in the procedure body.

The following example shows the CREATOR 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.

     .LOGON user_1/user_1
     .COMPILE FILE sp.spl
     /* sp.spl file
     CREATE PROCEDURE SYSLIB.static_dml()
     SQL SECURITY CREATOR
     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:

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