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.
| Time | Privileges Vantage Verifies (in Order) |
|---|---|
| Compilation |
|
| Run |
|
.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.
| Time | Privileges Vantage Verifies (in Order) |
|---|---|
| Compilation | user_1 has CREATE PROCEDURE privilege on SYSLIB database. |
| Run |
|
.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();