Executing an Oracle Stored Proc as Another User

Use:

ALTER SESSION SET CURRENT_SCHEMA = schema

That is the equivalent to SQL Server's EXECUTE AS syntax.

Another option would be using the AUTHID CURRENT_USER pragma.

If you add these two keywords immediately after your package, procedure, function or type name, it will execute with the privileges of the executing user, rather than the CODE schema. This overrides the default behaviour which is AUTHID DEFINER (the privileges of the schema/user that compiled the code)

i.e.

CREATE FUNCTION examplefunc    (pSqlStatement IN VARCHAR2)RETURN INTEGER  AUTHID CURRENT_USERAS    lResult INTEGER;BEGIN    EXECUTE IMMEDIATE pSqlStatement INTO lResult;    RETURN lResult;END examplefunc;

Note that for functions and procedures insider a package, the pragma can only be applied at the package level. You cannot set the rights on a per function basis.

This should cause any SQL inside the function, package, etc, to execute with the users privileges.

I've used that to manage a similar 'run any old bit of SQL dynamically' routine - at the very least you will have stopped a 'normal' user from being able to use your stored procedure to drop a table or install additional code in the CODE schema.

(It may also be worth - if you haven't already - adding some validation to throw out certain keywords - i.e. must start with SELECT, must not contain embedded pl/sql blocks - whatever you can get away with without breaking existing code).