惜分飞

中联的his系统在alert日志中经常会看到如下的日志告警

[oracle@oracle1 trace]$ tail -f alert_orcl.log 
Tue May 02 22:06:46 2023
Maximum of 148 enabled roles exceeded for user ZLHIS. Not loading all the roles.
Tue May 02 22:06:50 2023
Maximum of 148 enabled roles exceeded for user ZLHIS. Not loading all the roles.
Tue May 02 22:06:50 2023
Maximum of 148 enabled roles exceeded for user ZLHIS. Not loading all the roles.
Tue May 02 22:06:50 2023
Maximum of 148 enabled roles exceeded for user ZLHIS. Not loading all the roles.
Tue May 02 22:06:50 2023
Maximum of 148 enabled roles exceeded for user ZLHIS. Not loading all the roles.

查询ZLHIS用户当前的role情况

SQL>  select Grantee, count(*) "Role Number" from
  2   (
  3   select distinct connect_by_root grantee Grantee, granted_role
  4   from dba_role_privs 
 connect by prior granted_role=grantee
  5    6   ) where GRANTEE='ZLHIS'
 group by Grantee  7  
  8  /

GRANTEE                        Role Number
------------------------------ -----------
ZLHIS                                  149

虽然max_enabled_roles参数为150

SQL> show parameter MAX_ENABLED_ROLES;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_enabled_roles                    integer     150

但是用户支持的默认最大enable role为148个,对于该问题可以把一些角色的权限进行合并,然后再授权给ZLHIS,或者删除掉一些不需要的角色授权.如果一定需要这些角色,而且使其在用户登录的时候enable,可以以下两种常见方法解决

alter user <username> default roles <list of roles>;   
--可以是all或者角色列表

或者在会话中启用role

set roles all;
or
execute dbms_session.set_role('ALL');

参考:What to Check When Dealing With Ora-28031: Maximum Of 148 Enabled Roles Exceeded? (Doc ID 778785.1)