当我尝试连接时，为什么 Oracle 认为我的用户没有“创建会话”权限？

Why doesn't Oracle think my user has the "Create Session" privilege when I try to connect?

提问by Sterno

I've created a role in my Oracle 11g database called TestUserRolethat will eventually have more privileges, but currently has only the Create Sessionprivilege. I've assigned that role to a user, TestUser. It is their only role.

我在我的 Oracle 11g 数据库中创建了一个名为TestUserRole的角色，该角色最终将拥有更多权限，但目前只有创建会话权限。我已将该角色分配给用户TestUser。这是他们唯一的角色。

I created this as follows:

我创建如下：

CREATE ROLE TestUserRole IDENTIFIED BY somepassword;
GRANT Create Session TO TestUserRole;
CREATE USER TestUser IDENTIFIED BY somepassword;
GRANT TestUserRole TO TestUser;

When I try to connect to the database, I receive:

当我尝试连接到数据库时，我收到：

ORA-01045: user TESTUSER lacks CREATE SESSION privilege; logon denied

ORA-01045: 用户 TESTUSER 缺乏 CREATE SESSION 权限；登录被拒绝

I have verified (I think) that the user and role were setup successfully. If I query

我已经验证（我认为）用户和角色设置成功。如果我查询

select * from dba_role_privs where grantee = 'TESTUSER'

I get

我得到

| Grantee  | Granted_Role | Admin_Option | Default_Role |
---------------------------------------------------------
| TESTUSER | TESTUSERROLE | NO           | YES          |

Then if I query

然后如果我查询

select * from role_sys_privs where role = 'TESTUSERROLE'

I get

我得到

| Role         | Privilege      | Admin_Option |
------------------------------------------------
| TESTUSERROLE | CREATE SESSION | NO           |

So it appears that I have created the user and role successfully, the user has the role, and the role has the create session permission. Yet, when I try to log on, Oracle is telling me that the user doesn't have the Create Session permission. Where am I going wrong? Do I have to assign this privilege directly to the user rather than through a role?

这样看来我已经成功创建了用户和角色，用户有角色，角色有创建会话权限。然而，当我尝试登录时，Oracle 告诉我该用户没有创建会话权限。我哪里错了？我是否必须直接将此权限分配给用户而不是通过角色？

回答by MondKin

Can you try creating your role without the "IDENTIFIED BY password" clause ?

您可以尝试在没有“IDENTIFIED BY 密码”子句的情况下创建您的角色吗？

Hereit says the following:

这里有以下内容：

The BY password clause lets you create a local role and indicates that the user must specify the password to the database when enabling the role.

BY password 子句允许您创建本地角色，并指示用户在启用该角色时必须指定数据库的密码。

Then the problem is because when logging in the role is not enabled yet, the user must enable it using the "SET ROLE" statement as specified herebut this can only be done after logging in.

那么问题是因为当登录角色尚未启用时，用户必须使用此处指定的“SET ROLE”语句启用它，但这只能在登录后完成。