If a stored procedure, trigger, or SQL function accesses tables that are controlled by predicated privileges, Adaptive Server may need to recompile the procedural object for access by different users, or by the same user with one or more roles activated when the active roles differ from those activated when the plan was last compiled.
To prevent constant recompiling for different users executing the same procedure, Adaptive Server attempts to select a cached plan that has the right “profile” for the current user. For example, if a procedure is initially compiled for execution by Joe with role1 and role2 active, the same procedure is likely recompiled for Bob, who has role2 and role3 active, assuming that accesses by the procedure are controlled by separate predicated grants to role1, role2, and role3.
If the procedure cache contains plans compiled for Bob and Joe, Adaptive Server chooses a copy of the plan that is less likely to require compiling.
The initial choice of a plan that uses predicates from the cache depends on matching the protection “profile” of the user with that of the plan, which consists of:
the user ID for whom the plan was compiled. The user ID is saved in the plan's protection profile only if one or more predicated privileges on tables accessed by the procedure are granted directly to the user.
the group ID if one or more predicated privileges are granted to a group.
a list of role IDs if one or more predicated privileges are granted to a role.
the user ID for whom the plan was compiled, and the IDs of roles and groups that authorized the various accesses made by the procedure.
Choosing a plan that matches the user’s profile does not guarantee the plan is up to date with the required predicates for the current user’s accesses. Adaptive Server verifies that each select, update, and delete command in the procedure has been compiled with where clauses that reflect the current user’s predicated grants.
Otherwise, the procedure must be recompiled. Predicated privileges introduces protection checking as a new decision point for plan recompilation during the execution phase.
For efficient use of the procedure cache and the sharing of stored procedure plans, you should use a role-based privacy policy. Predicated access to the objects referenced by a stored procedure should be granted to a small number of roles that can be activated by the end users.
