Execute a Procedure with execute as owner or execute as caller

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:

If the execute as owner is specified, the procedure behavior conforms to the expected behavior following an implicit set proxy to the owner at the beginning of execution. This behavior includes:
If the execute as caller is specified:

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

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

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