Oracle 11.2角色密码设置方法及会话权限授权连接异常排查

一、CREATE SESSION权限缺失问题排查 #

以下是逐步排查和解决步骤：

1. 验证角色的权限配置
   确保你创建的角色确实拥有CREATE SESSION权限。用具备DBA权限的用户执行以下查询：

-- 直接查看角色的系统权限
SELECT privilege FROM dba_sys_privs WHERE grantee = '你的角色名';

-- 若你给角色授予了CONNECT，验证CONNECT角色包含CREATE SESSION
SELECT sp.privilege
FROM dba_role_privs rp
JOIN dba_sys_privs sp ON rp.granted_role = sp.grantee
WHERE rp.grantee = '你的角色名'
AND sp.privilege = 'CREATE SESSION';

Oracle 11.2中，CONNECT角色默认仅包含CREATE SESSION权限，所以只要角色被授予了CONNECT，理论上应该拥有该权限。

2. 确认用户的角色授予状态
   检查用户alvaro_rol是否被正确授予目标角色，且该角色是默认角色：

SELECT granted_role, default_role
FROM dba_role_privs
WHERE grantee = 'ALVARO_ROL';

如果查询结果中目标角色的default_role为NO，则用户登录时不会自动激活该角色，需执行以下语句设置默认角色：

ALTER USER alvaro_rol DEFAULT ROLE 你的角色名;
-- 若用户有多个角色，可设置所有角色为默认：
-- ALTER USER alvaro_rol DEFAULT ROLE ALL;

3. 检查用户账户状态
   确认用户账户未被锁定，且无登录限制：

SELECT username, account_status, profile
FROM dba_users
WHERE username = 'ALVARO_ROL';

若account_status不是OPEN，需解锁账户：ALTER USER alvaro_rol ACCOUNT UNLOCK;。同时检查profile的登录限制参数（如FAILED_LOGIN_ATTEMPTS）是否导致账户锁定。

4. 避免直接权限冲突
   确保未直接收回用户的CREATE SESSION权限：

SELECT privilege FROM dba_sys_privs WHERE grantee = 'ALVARO_ROL' AND privilege = 'CREATE SESSION';

若用户被直接收回过该权限，即使角色拥有，也可能导致登录失败，此时可直接授予用户该权限（若符合你的需求）：

GRANT CREATE SESSION TO alvaro_rol;

二、Oracle 11.2中为角色设置密码 #

创建带密码的角色 #

创建角色时通过IDENTIFIED BY子句指定密码：

CREATE ROLE 角色名 IDENTIFIED BY 你的密码;

为现有角色添加/修改密码 #

使用ALTER ROLE语句修改角色的密码设置：

-- 添加或修改角色密码
ALTER ROLE 角色名 IDENTIFIED BY 新密码;

-- 取消角色密码（设为无密码）
ALTER ROLE 角色名 NOT IDENTIFIED;

激活带密码的角色 #

若角色设置了密码且未设为用户默认角色，用户登录后需执行以下命令激活角色：

SET ROLE 角色名 IDENTIFIED BY 角色密码;

内容的提问来源于stack exchange，提问作者Alvaro