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.