These rules apply to dynamic and static SQL. Dynamic applies to any SQL statement whose complete text is unknown 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.
- Owner/Creator Relationship: "Owner" is the immediate owner.
- Privilege Checking Done at This Time:
- Compilation: Appropriate CREATE and DROP privileges are also checked during compilation.
- Execution: 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 when CREATE PROCEDURE or REPLACE PROCEDURE request was compiled. Request stops, so 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 | Privileges of the owner of the procedure or those of its creator. |
| CREATOR | Privileges of the creator of the procedure regardless of its containing user or database. |
| DEFINER [Default] | Privileges defined. |
| INVOKER | Privileges at the top of the current stack. |
| OWNER | Privileges of the immediate owner of the procedure (the user or database in which the procedure is contained). |
| Symbol or Text String | Meaning |
|---|---|
| O is not C | Immediate owner and creator of procedure are different. |
| O is C | Immediate owner and creator of procedure are the same. |
Using the first row for the INVOKER SQL SECURITY option as a model, you 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.