在alert_.log里报如下错误:
ORA-12012: error on auto execute of job 2
ORA-28031: maximum of 148 enabled roles exceeded
今天一同事提到ORA-28031错误,错误信息明显的提示了授予用户role的数量超过148个导致的。这些role的数量,包括直接授予的,也包括间接授予的。当一个用户被直接和间接授予的role超过148个时,导致的直接问题是用户不能登录数据库,布置的后台job也不能执行。有一个例外就是sys可以拥有超过148个role,它可以正常的登录数据库,但后台sys的JOB不能正常的跑。同时要注意,创建role的用户,隐含的被授予了这些role.metalink 780749.1有关错ORA-28031的详细内容。个人猜测,当一个普通session初始化时,要分配一定的内存,当分配的role所占内存超过了某一限制,就不能创建session了.
下面是测试普通用户,
环境:OS: Linux version 2.6.9-67.ELsmp, DB:10.2.0.4
SYS@ ORCL>create user w1 identified by w1;
User created.
SYS@ ORCL>create user w2 identified by w2;
User created.
SYS@ ORCL>grant create session,create role to w1;
Grant succeeded.
SYS@ ORCL>grant create session to w2;
Grant succeeded.
SYS@ ORCL>conn w1/w1
Connected.
W1@ ORCL>create role role_00;
Role created.
W1@ ORCL>begin
2 for i in 1..147 loop
3 execute immediate 'create role role'||i;
4 execute immediate 'grant role'||i||' to role_00';
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
W1@ ORCL>grant role_00 to w2;
Grant succeeded.
W1@ ORCL>conn w2/w2
Connected.
从这里可以看出,用户w2有148个role:role_00+role[1..147], 可以正常的连接数据库。
W2@ ORCL>conn w1/w1
Connected.
W1@ ORCL>create role role148;
Role created.
W1@ ORCL>grant role148 to role_00;
Grant succeeded.
W1@ ORCL>conn w2/w2
ERROR:
ORA-28031: maximum of 148 enabled roles exceeded
当w2超过148个role时,不能登录数据库了。
Warning: You are no longer connected to ORACLE.
@ >conn w1/w1
ERROR:
ORA-28031: maximum of 148 enabled roles exceeded
所有这些role是w1创建的,w1被默认的的授予了这些权限.此时W1已经创建了149个role了,也不能登录数据库了.
@ >conn / as sysdba
Connected.
SYS@ ORCL>select count(1) from dba_role_privs where grantee='W1';
COUNT(1)
----------
149
SYS@ ORCL>select count(1) from dba_role_privs where default_role='YES' start with grantee='W2' connect by prior granted_role=grantee;
COUNT(1)
----------
149
对用户Sys的测试来看,当role数量超过148时,能够正常的登录数据库,布置的后台job会报ORA-28031,但貌似不是每次执行job都报错误。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/45188/viewspace-1023878/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/45188/viewspace-1023878/