Nesting and stored procedures

You can nest EXECUTE AS and REVERT commands to switch the current user. In the following example, John masquerades as Hank, then masquerades as Tom, and reverts:

BIZ.SCM(JOHN) = > EXECUTE AS HANK;
EXECUTE AS
BIZ.SCM(JOHN) = > SELECT session_user, current_user;
 SESSION_USER | CURRENT_USER
--------------+--------------
 JOHN         | HANK
(1 row)
BIZ.SCM(JOHN) = > EXECUTE AS TOM;
EXECUTE AS
BIZ.SCM(JOHN) = > SELECT session_user, current_user;
 SESSION_USER | CURRENT_USER
--------------+--------------
 JOHN         | TOM
(1 row)
BIZ.SCM(JOHN)=> REVERT
REVERT
BIZ.SCM(JOHN) => SELECT session_user, current_user;
SESSION_USER | CURRENT_USER
--------------+--------------
 JOHN         | HANK
(1 row)
BIZ.SCM(JOHN)=> REVERT
REVERT
BIZ.SCM(JOHN) => SELECT session_user, current_user;
SESSION_USER | CURRENT_USER
--------------+--------------
 JOHN         | JOHN
(1 row)

BIZ.SCM(ADMIN)=> CREATE USER john PASSWORD 'john';
CREATE USER
BIZ.SCM(ADMIN)=> CREATE USER hank PASSWORD 'hank';
CREATE USER
BIZ.SCM(ADMIN)=> CREATE USER tom PASSWORD 'tom123';
CREATE USER
BIZ.SCM(ADMIN)=> GRANT EXECUTE AS ON tom to hank;
GRANT
BIZ.SCM(ADMIN)=> GRANT LIST ON john,hank,tom to john, hank, tom;
GRANT
BIZ.SCM(ADMIN)=> GRANT CONNECT ON dev to john, hank, tom;
GRANT
BIZ.SCM(ADMIN)=> GRANT CREATE PROCEDURE to hank;
GRANT

Log in as Hank and create a stored procedure. Depending on the inputs, the procedure runs as HANK (proc_as 1) or as TOM (proc_as 6).

DEV.SCM(ADMIN)=> \c dev hank hank
You are now connected to database dev as user hank.
DEV.SCM(HANK)=> CREATE OR REPLACE PROCEDURE proc_as(INTEGER) RETURNS 
INTEGER LANGUAGE NZPLSQL AS BEGIN_PROC DECLARE inval ALIAS FOR $1; 
cuser VARCHAR(128); suser VARCHAR(128); BEGIN IF (inval  > 5) THEN 
EXECUTE IMMEDIATE 'EXECUTE AS TOM'; END IF; SELECT CURRENT_USER INTO 
cuser; SELECT SESSION_USER INTO suser; RAISE NOTICE 'Current User <% > 
Session User <% > ', cuser, suser; RETURN inval; END; END_PROC;
CREATE PROCEDURE
DEV.SCM(HANK)=> CALL proc_as(1);
NOTICE:  Current User <HANK> Session User <HANK>
PROC_AS
---------
       1
(1 row)
DEV.SCM(HANK)=> CALL proc_as(6);
NOTICE:  Current User <TOM> Session User <HANK>
PROC_AS
---------
          6
(1 row)

HANK grants JOHN the EXECUTE permission on the procedure, and runs the procedure as JOHN. The procedure does not include the REVERT command, because this is done automatically on a return from a stored procedure. This means that you exit a stored procedure with the same current user and session user you had when calling the procedure.

DEV.SCM(HANK)=> GRANT EXECUTE on proc_as(integer) to john;
GRANT
DEV.SCM(HANK)=> \c dev john john
You are now connected to database dev as user john.
DEV.SCM(JOHN)=> CALL proc_as(1);
NOTICE:  Current User <HANK> Session User <JOHN>
PROC_AS
---------
       1
(1 row)
DEV.SCM(JOHN)=> CALL proc_as(6);
NOTICE:  Current User <TOM> Session User <JOHN>
PROC_AS
---------
       6
(1 row)