oracle 更改会话设置 current_schema 权限
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37024856/
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
Alter session set current_schema privilege
提问by tolga_kavukcu
Basicly i have an java application that uses oracle as a data source. Now my application connects with a user and uses connected user's schema for table creation and etc...
基本上我有一个使用 oracle 作为数据源的 java 应用程序。现在我的应用程序与用户连接并使用连接用户的架构来创建表等等......
Now i have a requirment that my application also should work under another schema.
现在我有一个要求，我的应用程序也应该在另一个模式下工作。
So i have to alternatives.
所以我必须有其他选择。
1 - Change my table names with a prefix like
1 - 使用前缀更改我的表名
select * from other_schema.table
2 - Altering the session before running any query like
2 - 在运行任何查询之前改变会话
alter session set current_schema=other_schema
I am curios about is there any possibilty that alter session privilege can be revoked by dba's.
我很好奇 dba 是否有可能撤销更改会话特权。
Oracle documentation says that
Oracle 文档说
You do not need any privileges to perform the other operations of this statement unless otherwise indicated.
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_2012.htm
除非另有说明，否则您不需要任何特权来执行此语句的其他操作。
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_2012.htm
Please note that my app user will have all privileges for other_schema.
请注意，我的应用程序用户将拥有other_schema 的所有权限。
My application will be running many envoriments.
我的应用程序将运行许多环境。
So going with first appoach, it looks like more safe but it can take long time.
因此，使用第一个方法，它看起来更安全，但可能需要很长时间。
Second one is faster but seems like tricky.
第二个更快，但似乎很棘手。
回答by ibre5041
Yes they do. There is a role called CONNECT. This role usually (depending on version) gives you two system privileges:
是的，他们这样做。有一个角色叫做CONNECT。此角色通常（取决于版本）为您提供两个系统权限：
create sessionalter session
create sessionalter session
when your user has only create sessionprivilege granted directly then you can notuse anyalter session ...statement.
当您的用户只有create session直接授予的权限时，您就不能使用任何alter session ...语句。
Se Oracle docs: Addressing The CONNECT Role Change
Se Oracle 文档： 解决 CONNECT 角色更改
Note that the ALTER SESSION privilege is required for setting events. Very few database users should require the alter session privilege.
SQL> ALTER SESSION SET EVENTS ........The alter session privilege is not required for other alter session commands.
SQL> ALTER SESSION SET NLS_TERRITORY = FRANCE;
请注意，设置事件需要 ALTER SESSION 权限。很少有数据库用户需要alter session 权限。
SQL> ALTER SESSION SET EVENTS ........其他alter session 命令不需要alter session 特权。
SQL> ALTER SESSION SET NLS_TERRITORY = FRANCE;
So it really depends on Oracle version.
所以这真的取决于Oracle版本。
回答by Michael Adams
The CONNECT role was changed in 10gR2. It used to have most of the permissions of RESOURCE, but these were revoked in 10gR2.
CONNECT 角色在 10gR2 中发生了变化。它曾经拥有 RESOURCE 的大部分权限，但这些在 10gR2 中被撤销。
I just tried ALTER SESSION with a user that only had CREATE SESSION and much to my surprise it worked, at least in 11.2.0.4.
我刚刚与一个只有 CREATE SESSION 的用户一起尝试了 ALTER SESSION，令我惊讶的是它起作用了，至少在 11.2.0.4 中。
