In versions of Adaptive Server 15.7 ESD #2, you can create a procedure using execute as owner or execute as caller, which checks runtime permissions, executes DDL, and resolves objects names on behalf of the owner or caller respectively. If you create a procedure using execute as caller, Adaptive Server performs these operations as the procedure caller. If you create a procedure using execute as owner, these operations are performed on behalf of the procedure owner. When the execute as clause is omitted, the behavior is the same as in versions earlier than Adaptive Server 15.7 ESD #2.Creating procedures for execution as the procedure owner is useful for applications that require all actions in a procedure to be checked against the privileges of the procedure owner. Implicit grant checking due to ownership chains does not apply to procedures created with execute as owner. The application end user requires no privilege in the database other than execute permission on the stored procedure. Additionally, any DDL executed by the procedure is conducted on behalf of the procedure owner, and any objects created in the procedure are owned by the procedure owner. This relieves the administrator of the requirement of having to grant privilege on DDL commands to the application user.Creating the procedures for execution as the session user or caller is necessary if permissions must be checked on behalf of the individual user. For example, use execute as caller if a table accessed by the procedure is subject to fine-grained access control through predicated privileges, such that one user is entitled to see one set of rows and another user another set of rows. Implicit grant checking due to ownership chains does not apply for procedures which are created with execute as caller; if predicated privileges are present on tables referenced in the procedure, they will be applied.If the execute as clause is omitted:
Object names are resolved on behalf of the procedure owner.
DDL commands and cross-database access are on behalf of the procedure caller.
Permission checks for DML, execute, transfer table, truncate table, delete statistics, and update statistics are made on behalf of the caller unless there exists an ownership chain between the referenced object and the procedure, in which case permission checks are bypassed.
If the execute as owner clause is specified, the procedure behavior conforms to the expected behavior following an implicit set session authorization to the owner at the beginning of execution. This behavior includes:
Object names are resolved on behalf of the procedure owner. If the procedure references a table or other object without qualifying the name with an owner name, Adaptive Server will look up a table of that name belonging to the procedure owner. If no such table exists, Adaptive Server will look for a table of that name owned by the Database Owner.
DDL commands and cross-database access are on behalf of the procedure owner.
No implicit granting of permissions through ownership chains occurs.
All access control checks are based on the procedure owner's permission, his group, his system roles, his default user-defined roles, and those roles granted to the owner that are activated in the procedure body. Roles granted to the owner's login profile are also considered. Only those roles activated by the procedure owner during execution of the procedure are considered.
Procedures defined with execute as owner, execute as caller, or with no execute as clause, can be nested inside procedures defined with execute as owner. Similarly procedures defined with execute as owner can be nested inside procedures defined without the execute as clause.
Procedures called from an execute as owner procedure are executed as the owner of the calling procedure unless the nested procedure is defined as execute as owner.
Dynamic SQL statements inside a procedure are executed with permissions of procedure owner regardless of the 'Dynamic Ownership Chain' setting of sp_procxmode.
Because temporary tables are owned by the session, temporary tables created outside the procedure by the caller are available inside the procedure to the procedure owner. This behavior reflects temporary table availability after a set session authorization command is executed in a session.
Audit records of statements executed within the procedure show the procedure owner's name and the option execute as owner.
set session authorization statement is not allowed inside the procedure created with execute as owner even if the statement is in a nested procedure which is not defined as execute as owner.
If the execute as caller clause is specified,
Objects are resolved on behalf of caller. If the procedure references a table or other object without qualifying the name with an owner name, Adaptive Server will look up a table of that name belonging to the user who called the procedure. If no such table exists, Adaptive Server will look for a table of that name owned by the Database Owner.
DDL commands and cross-database access are on behalf of the caller.
No implicit granting of permissions through ownership chains occurs.
Permissions are checked on behalf of caller, caller's group, active roles and system roles.
Procedures defined with execute as owner, execute as caller, or with no execute as clause, can be nested inside procedures defined with execute as caller. Similarly procedures defined with execute as caller can be nested inside procedures defined without the execute as clause.
Procedures called from an execute as caller procedure are executed on behalf of the caller of the parent procedure unless the nested procedure is defined as execute as owner.
Dynamic SQL executes as caller regardless of the 'Dynamic Ownership Chain' setting on sp_procxmode.
Temporary tables created outside the procedure are available inside the procedure.
Object references by the procedure are not entered into sysdepends as the objects are resolved according to each caller of the procedure.
select * is not expanded in syscomments.
Plans in the procedure cache for the same procedure are not shared across users as the objects in the procedure must be resolved to the user executing the procedure. Because of this, procedure cache usage may increase if many users are executing the procedure.The plan for a particular user is reused when the user executes the procedure again.
Audit records of statements executed within the procedure show the procedure caller's name and execute as caller clause.
In the following example, the procedure created by user Jane has no execute as clause. The procedure selects from jane.employee into an intermediate table named emp_interim.:
create procedure p_emp
select * into emp_interim from jane.employee
grant execute on p_emp to bill
Bill executes the procedure:
exec jane.p_emp
Bill is not required to have select permission on jane.employee because Jane owns p_emp and employee. By granting execute permission on p_emp to Bill, Jane has implicitly granted him select permission on employee.
Bill must have been granted create table permission. The emp_interim table will be owned by Bill.
In the following example, Jane creates a procedure with an identical body using the execute as owner clause and Bill executes the procedure:
create procedure p_emp
with execute as owner as
select * into emp_interim from jane.employee
grant execute on p_emp to bill
Bill requires only execute permission to run the procedure successfully.
emp_interim table is created on behalf of Jane, meaning Jane is the owner. If Jane does not have create table permission, the procedure will fail.
In the following example, Jane creates the same procedure with the execute as caller clause:
create procedure p_emp
with execute as caller as
select * into emp_interim from jane.employee
grant execute on p_emp to bill
Bill must have select permission on jane.employee. No implicit grant checking is done though jane owns both p_emp and employee. If jane.employee has predicated privileges granted to Bill, the predicates will be added to the query. See “Granting Predicated Privileges” for more information.
Bill must have create table permission. emp_interim is created on behalf of Bill, meaning Bill is the owner.
