Executing an Oracle Stored Proc as Another User...

Question

I'm mostly an oracle novice, so forgive me if this is a stupid question...

I have a schema called 'CODE' with a stored proc that executes arbitrary SQL (for now, please ignore the potential security issues associated with that). The SQL that is passed in will select data; but all of the data resides in either schema A, B, or C - but the SQL will only ever select from ONE schema at a time.

For example: User of type A creates a string 'SELECT * FROM A.USERTABLE' - while user of type B creates a string 'SELECT * FROM B.USERTABLE'.

What I'm trying to do is allow the user to not explicitly specify their schema. In the front-end .net application; I already know if they are type A, B, or C. I want all three to simply enter 'SELECT * FROM USERTABLE'.

The problem I'm having is that I don't know how to do that. My app can only execute proc in the 'CODE' schema - so I can't just duplicate the code and let user A call 'A.ExecuteSQL'.

I've tried a few things; but nothing has worked thus far. I want the ExecuteSQL proc to stay in the CODE schema; but when 'USERTABLE' gets passed in, I need it to know that sometimes that means A.USERNAME and sometimes B.USERNAME.

Any suggestions?

Answer 1

Use:

ALTER SESSION SET CURRENT_SCHEMA = schema

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

Answer 2

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_USER AS 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).