- Dynamic and static SQL are governed by these rules.
Generally, the term dynamic SQL applies to any SQL statement whose complete text is not known until run time.
- The default privilege option for SQL procedures is DEFINER.
- When the creator of a procedure is also its immediate owner, all static DML and DCL statements within the procedure are valid.
The following table summarizes the rules for SQL procedure privilege assignment.
- For the column Owner/Creator Relationship, the word owner refers to the immediate owner.
- For the column Privilege Checking Done at This Time: Compilation, the appropriate CREATE and DROP privileges are also checked during compilation.
- For the column Privilege Checking Done at This Time: Execution, the EXECUTE PROCEDURE privilege is also checked at run time.
| SQL SECURITY Privilege Option | Dynamic or Static SQL Statement? | Owner/Creator Relationship | Privilege Checking Done at This Time | Default Database or User | |
|---|---|---|---|---|---|
| Compilation | Execution | ||||
| DEFINER | Dynamic | O is not C | None | O and C | C |
| O is C | None | O | C | ||
| Static | O is not C | O and C | O and C | C | |
| O is C | O | O | C | ||
| INVOKER | Dynamic | O is not C | None | I | I |
| O is C | None | I | I | ||
| Static | O is not C | C | I | I | |
| O is C | C | I | I | ||
| OWNER | Dynamic | O is not C | COP | O | O |
| O is C | None | O | O | ||
| Static | O is not C | COP and O | O | O | |
| O is C | O | O | O | ||
| CREATOR | Dynamic | O is not C | None | C | C |
| O is C | None | C | C | ||
| Static | O is not C | C | C | C | |
| O is C | C | C | C | ||
where:
| Symbol or Text String … | User Privileges Required to Submit a CREATE PROCEDURE or REPLACE PROCEDURE Request |
|---|---|
| None | None. |
| Error | One or more privileges it did not have at the time the CREATE PROCEDURE or REPLACE PROCEDURE request was compiled. Because the request aborts when this occurs, the cells for Privilege Check at Execution Time and Default Database are empty. |
| COP | CREATE OWNER PROCEDURE. |
| C | Privileges of the creator of the procedure. |
| I | Privileges of the invoker of the procedure. |
| O | Privileges of the owner of the procedure. |
| O and C | Privileges of the owner of the procedure and those of its creator. |
| O or C | Those of the owner of the procedure or those of its creator. |
| CREATOR | Those of the creator of the procedure regardless of its containing user or database. |
| DEFINER | Those currently defined. This is the default. |
| INVOKER | Those at the top of the current stack. |
| OWNER | Those of the immediate owner of the procedure (the user or database in which the procedure is contained). |
| This symbol or text string … | Means that the immediate owner and creator of the procedure are … |
|---|---|
| O is not C | different. |
| O is C | the same. |
Using the first row for the INVOKER SQL SECURITY option as a model, you would interpret the table as follows: For the case when an SQL statement is invoked dynamically and the immediate owner of its containing procedure is not the same user as its creator, no privilege checking is done at the time the procedure is compiled, the privileges of the invoker of the procedure are checked at run time, and the default database or user that is used to implicitly qualify any unqualified object references within the SQL statements in the procedure body is that of the invoker.