When you specify SQL SECURITY CREATOR, the system 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.
The system verifies the following privileges for this example.
| AT this time … | The system 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 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. |
.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, the system verifies the following privileges:
| AT this time … | The system 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. |
.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();