如何在 Oracle 中以特定用户身份执行查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22079432/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to execute query as specific user in Oracle
提问by Jason
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?
以数据库“超级用户”（如 sys 或 sysadmin）身份登录时作为特定用户？
How can this be accomplished?
如何做到这一点？
UPDATE
更新
we have where clauses like this:
我们有这样的 where 子句：
where column_user_name=user
So the DB needs to think the current user as specified by useris the user we want to pretend to be.
因此，DB 需要将指定的当前用户视为user我们想要伪装的用户。
回答by Justin Cave
You can, but you'd need to use an undocumented package to do so. The dbms_sys_sqlpackage 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.
你可以，但你需要使用一个未记录的包来做到这一点。该dbms_sys_sql程序包允许您以另一个用户身份运行 SQL。这是 Application Express (APEX) 在内部使用的东西。但它没有记录，所以你要自担风险使用它。
In general, you ought not be connecting to a database as SYSduring 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 TABLEprivilege. So you ought to be able to run
通常，您不应该像SYS在正常的日常操作中那样连接到数据库。如果您是 DBA（使用您自己的帐户，而不是SYS），您应该能够从任何用户的表中进行查询，因为您拥有SELECT ANY TABLE特权。所以你应该能够跑
SELECT *
FROM some_user.some_table_name
回答by René Nyffenegger
If I understand your question correctly, it should be possible with
如果我正确理解您的问题，应该可以使用
alter session set current_schema = <username>;
and then running your statement.
然后运行你的语句。
UpdateYour new requirement (where x = USER) does not work with this approach (as you have already figured out). However, you can change userto sys_context('userenv', 'current_schema')which will return the name of altered schema.
更新您的新要求 ( where x = USER) 不适用于这种方法（正如您已经发现的那样）。但是，您可以更改user为sys_context('userenv', 'current_schema')which 将返回已更改架构的名称。
回答by troy
you can use the proxy: alter user order_request grant connect through system; connect system [order_request]/cidw_pre
您可以使用代理：alter user order_request grant connect through system；连接系统 [order_request]/cidw_pre
回答by durette
You can use Proxy User Authentication:
您可以使用代理用户身份验证：
SQL> CONNECT/@mytnsname AS SYSDBA
Connected.
SQL> CREATE USER user_impersonating IDENTIFIED BY password1;
User created.
SQL> CREATE USER user_being_impersonated IDENTIFIED BY password2;
User created.
SQL> GRANT CONNECT TO user_impersonating;
Grant succeeded.
SQL> GRANT CONNECT TO user_being_impersonated;
Grant succeeded.
SQL> ALTER USER user_being_impersonated GRANT CONNECT THROUGH user_impersonating;
User altered.
SQL> DISCONNECT;
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
SQL> CONNECT user_impersonating[user_being_impersonated]/password1@mytnsname
Connected.
SQL> SELECT user FROM DUAL;
USER
------------------------------
USER_BEING_IMPERSONATED
SQL> SHOW USER
USER is "USER_BEING_IMPERSONATED"
SQL>
