How to execute query as specific user in Oracle

Question

Is there a way to execute a query like

select count(*) from some_table;

as a specific user when logged in as a database "super user" like sys or sysadmin?

How can this be accomplished?

UPDATE

we have where clauses like this:

where column_user_name=user

So the DB needs to think the current user as specified by user is the user we want to pretend to be.

Answer 1

If I understand your question correctly, it should be possible with

alter session set current_schema = <username>;

and then running your statement.

Update Your new requirement (where x = USER) does not work with this approach (as you have already figured out). However, you can change user to sys_context('userenv', 'current_schema') which will return the name of altered schema.

Answer 2

You can, but you'd need to use an undocumented package to do so. The dbms_sys_sql package lets you run SQL as another user. That's something that Application Express (APEX) uses internally. But it's undocumented so you'd use it at your own risk.

In general, you ought not be connecting to a database as SYS during normal day-to-day operations. If you're a DBA (using your own account, not SYS), you ought to be able to query from any user's table because you'd have the SELECT ANY TABLE privilege. So you ought to be able to run

SELECT *
  FROM some_user.some_table_name