Oracle 11g 中按角色授予的权限发生了变化？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2282061/
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
Grants by role changed in Oracle 11g?
提问by Peter Lang
I tried to grant CONNECTto a user through a role:
我试图CONNECT通过角色授予用户：
CREATE ROLE my_role IDENTIFIED BY "passwd";
GRANT CONNECT TO my_role;
CREATE USER my_user IDENTIFIED BY "passwd";
GRANT my_role TO my_user;
When I try this in 10git works fine, while in 11glogin is rejected:
当我在10g 中尝试这个时它工作正常，而在11g 中登录被拒绝：
ORA-01045:user MY_USER lacks CREATE SESSION privilege; logon denied
ORA-01045:user MY_USER lacks CREATE SESSION privilege; logon denied
Granting CREATE SESSIONto the role does not make a difference.
I can only login after directly granting CONNECT(or CREATE SESSION) to the user.
授予CREATE SESSION角色没有任何区别。
我只能在直接授予CONNECT（或CREATE SESSION）给用户后才能登录。
Has Oracle changed this behavior or am I doing something wrong?
Oracle 是否改变了这种行为，还是我做错了什么？
回答by Adam Musch
I think you might have gotten away with a security "feature" in 10g. The way I read the SQL Reference and Security Guide for 11g indicates that password-enabled roles require the use of the SET ROLE my_role IDENTIFIED BY passwdbefore any rights granted by that role are effective.
我认为您可能已经摆脱了 10g 中的安全“功能”。我阅读 11g 的 SQL 参考和安全指南的方式表明启用密码的角色需要在SET ROLE my_role IDENTIFIED BY passwd该角色授予的任何权限生效之前使用。
You can't CREATE SESSIONuntil you have the role, and you can't have the role until you issue SET ROLE.
你不能CREATE SESSION，直到你的角色，你不能有作用，直到发出SET ROLE。
Catch-22.
抓住-22。
回答by Trev Bryan
Oracle Knowledge Base [ID 745407.1] explains this.
Oracle 知识库 [ID 745407.1] 对此进行了解释。
The DEFAULT clause in the:
中的 DEFAULT 子句：
alter user default roles ; specifies the roles granted by default to the user at logon. This clause can contain only roles that have been granted directly to the user with a GRANT statement, or roles created by the user with the CREATE ROLE privilege. You cannot use the DEFAULT ROLE clause to enable:
更改用户默认角色；指定在登录时默认授予用户的角色。该子句只能包含使用 GRANT 语句直接授予用户的角色，或由具有 CREATE ROLE 权限的用户创建的角色。您不能使用 DEFAULT ROLE 子句来启用：
Roles not granted to the user
Roles granted through other roles
Roles managed by an external service (such as the operating system), or by the Oracle Internet Directory
Roles that are password authenticated.
Roles that are implemented as secure application roles.
未授予用户的角色
通过其他角色授予的角色
由外部服务（例如操作系统）或 Oracle Internet Directory 管理的角色
经过密码验证的角色。
作为安全应用程序角色实现的角色。
For password authenticated roles, the change has been introduced in version 10.2.0.5 and 11.1.0.7. For secure application roles, the change has been introduced in the Oracle releases 10.2.0.4 and 11.1.0.7 These changes will apply to all future releases. The above mentioned restrictions will be introduced in the future documentation.
对于密码验证角色，已在版本 10.2.0.5 和 11.1.0.7 中引入了更改。对于安全应用程序角色，已在 Oracle 版本 10.2.0.4 和 11.1.0.7 中引入了更改。这些更改将适用于所有未来版本。上述限制将在以后的文档中引入。
One can easily turn the password enabled roles into standard roles by running the script resulting from:
通过运行以下脚本，可以轻松地将启用密码的角色转换为标准角色：
select 'alter role '||role||' not identified;' from dba_roles where password_required='YES' and role not in (select role from dba_application_roles);
选择'改变角色'||角色||' 未确定；' 来自 dba_roles，其中 password_required='YES' 并且角色不在（从 dba_application_roles 中选择角色）；
回答by rlstrommen
Activating default roles (granted to a user as Default) which are also password protected changed in Oracle 10g, version 10.2.0.5 (at least for our copy). In release 10.2.0.5, a password protected role would no longer become activated by default. It had to be specifically turned on with the appropriate password.
激活默认角色（授予用户作为默认值）也受密码保护在 Oracle 10g 版本 10.2.0.5 中更改（至少对于我们的副本）。在 10.2.0.5 版中，受密码保护的角色将不再默认激活。必须使用适当的密码专门打开它。
This was not documented as far as we could tell. But when our systems were upgraded from 10.2.0.4 to 10.2.0.5, this change broke several of our systems, and we had to create parallel non-protected roles for our functional accounts which did not have any mechanism to activate default roles. We basically created old_role_batch with no password as a copy of old_role whish was password protected.
据我们所知，这并没有记录在案。但是当我们的系统从 10.2.0.4 升级到 10.2.0.5 时，这个变化破坏了我们的几个系统，我们不得不为我们的功能帐户创建并行的非保护角色，这些角色没有任何机制来激活默认角色。我们基本上创建了没有密码的 old_role_batch 作为受密码保护的 old_role 副本。
