Oracle 11g 中按角色授予的权限发生了变化吗？
我尝试通过角色向用户授予 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;
当我在 10g 中尝试此操作时，它工作正常，而在 11g 中登录被拒绝：
ORA-01045:用户 MY_USER 缺少 CREATE SESSION特权;登录被拒绝
向角色授予CREATE SESSION 不会产生任何影响。
我只能在直接向用户授予 CONNECT （或 CREATE SESSION）后才能登录。
Oracle 改变了这种行为还是我做错了什么？
I tried to grant CONNECT to a user through a role:
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 10g it works fine, while in 11g login is rejected:
ORA-01045:user MY_USER lacks CREATE SESSION privilege; logon denied
Granting CREATE SESSION to the role does not make a difference.
I can only login after directly granting CONNECT (or CREATE SESSION) to the user.
Has Oracle changed this behavior or am I doing something wrong?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我认为您可能已经摆脱了 10g 中的安全“功能”。我阅读 11g 的 SQL 参考和安全指南的方式表明，启用密码的角色需要使用
SET ROLE my_role IDENTIFIED BY passwd才能使该角色授予的任何权限生效。在拥有该角色之前，您无法
CREATE SESSION，并且在发出SET ROLE之前，您无法拥有该角色。第 22 条军规。
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.You can't
CREATE SESSIONuntil you have the role, and you can't have the role until you issueSET ROLE.Catch-22.
Oracle 知识库 [ID 745407.1] 对此进行了解释。
DEFAULT 子句中：
alter user default Roles；
指定用户登录时默认授予的角色。该子句只能包含使用 GRANT 语句直接授予用户的角色，或由用户使用 CREATE ROLE 权限创建的角色。您不能使用 DEFAULT ROLE 子句启用：
未授予用户的角色
通过其他角色授予的角色
角色由外部服务（例如操作系统）或 Oracle Internet Directory 管理
作为密码的角色经过身份验证。
作为安全应用程序角色实现的角色。
对于密码身份验证的角色，已在版本 10.2.0.5 和 11.1.0.7 中引入了更改。
对于安全应用程序角色，Oracle 版本 10.2.0.4 和 11.1.0.7 中引入了更改
这些更改将适用于所有未来版本。上述限制将在未来的文档中引入。
通过运行以下脚本，可以轻松地将启用密码的角色转换为标准角色：
select 'alter role '||role||'未识别；'来自 dba_roles，其中，password_required='YES' 并且角色不在（从 dba_application_roles 中选择角色）；
Oracle Knowledge Base [ID 745407.1] explains this.
The DEFAULT clause in the:
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:
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.
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.
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);
激活默认角色（作为默认授予用户），这些角色也受密码保护，在 Oracle 10g 版本 10.2.0.5 中进行了更改（至少对于我们的副本而言）。在版本 10.2.0.5 中，默认情况下将不再激活受密码保护的角色。必须使用适当的密码专门打开它。
据我们所知，这没有记录在案。但是，当我们的系统从 10.2.0.4 升级到 10.2.0.5 时，此更改破坏了我们的多个系统，我们必须为功能帐户创建并行的非受保护角色，这些帐户没有任何机制来激活默认角色。我们基本上创建了没有密码的 old_role_batch 作为受密码保护的 old_role 的副本。
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.
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.