You can create a procedure using execute as owner or execute as caller, which checks runtime permissions, executes DDL, and resolves objects names.
If you create a procedure using execute as caller, SAP ASE performs these operations as the procedure caller. If you on create a procedure using execute as owner, these operations are performed on the behalf of the procedure owner. 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. 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 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. If the execute as 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, truncate table and update stats 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.
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, SAP ASE looks up a table of that name belonging to the procedure owner. If no such table exists, SAP ASE looks for a table of that name owned by the database owner.
DDL commands and cross-database access are on behalf of the procedure owner.
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.
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 on 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 proxy command is executed in a session.
Audit records of statements executed within the procedure show the procedure owner's suid.
Objects are resolved on behalf of caller. If the procedure references a table or other object without qualifying the name with an owner name, SAP ASE looks up a table of that name belonging to the user who called the procedure. If no such table exists, SAP ASE looks for a table of that name owned by the database owner.
No implicit granting of permissions through ownership chains occurs.
DDL commands and cross-database access are on behalf of the caller.
Permissions are checked on behalf of caller, caller's group, active roles and system roles.
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.
Unqualified object references by the procedure are not entered into sysdepends.
select * is not expanded in syscomments .
Plans in the procedure cache for the same procedure are not shared across users.
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 jan.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 on employee.
Bill must have been granted create table permission. The emp_interim table will be owned by Bill.
In following example, Jane creates a procedure with an identical body using the execute as owner clause and Bill execute 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 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.
Bill must have create table permission. emp_interim is created on behalf of Bill, meaning Bill is the owner.