[20151110]oracle可以启用多少role.txt

最新推荐文章于 2023-02-22 16:06:34 发布

cuixun7780

最新推荐文章于 2023-02-22 16:06:34 发布

阅读量94

点赞数

文章标签：数据库

[20151110]oracle可以启用多少role.txt

--链接http://blog.itpub.net/4227/viewspace-676078/

Oracle在启用用户的角色时，最多允许启用148个。

其实这个限制比较奇怪，因为148与2的幂相差较大，不知道Oracle出于什么原因确定的这个值：

BEGIN
FOR I IN 1..148 LOOP
EXECUTE IMMEDIATE 'CREATE ROLE R_' || I;
END LOOP;
END;
/

CREATE ROLE R_149;

SCOTT@test01p> SET ROLE ALL;
SET ROLE ALL
*
ERROR at line 1:
ORA-28031: maximum of 148 enabled roles exceeded

--我自己查询参数参数:

SYS@book> @ &r/ver1

PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SYS@book> show parameter role
NAME               TYPE     VALUE
------------------ -------- ------
max_enabled_roles integer 150
os_roles           boolean FALSE
remote_os_roles    boolean FALSE

SYS@book> alter system set max_enabled_roles=151 scope=spfile;
alter system set max_enabled_roles=151 scope=spfile
*
ERROR at line 1:
ORA-00068: invalid value 151 for parameter max_enabled_roles, must be between 1 and 148

--无法更改这个数值更大. 不过还是觉得很奇怪150 与 148 相差2. 难道缺省有2个是默认的.
--最容易想到的public,参看dba_roles的视图定义:

CREATE OR REPLACE FORCE VIEW SYS.DBA_ROLES
(
   ROLE
,PASSWORD_REQUIRED
,AUTHENTICATION_TYPE
)
AS
   SELECT name
         ,DECODE
          (
             password
            ,NULL, 'NO'
            ,'EXTERNAL', 'EXTERNAL'
            ,'GLOBAL', 'GLOBAL'
            ,'YES'
          )
         ,DECODE
          (
             password
            ,NULL, 'NONE'
            ,'EXTERNAL', 'EXTERNAL'
            ,'GLOBAL', 'GLOBAL'
            ,'APPLICATION', 'APPLICATION'
            ,'PASSWORD'
          )
     FROM user$
    WHERE type# = 0 AND name NOT IN ('PUBLIC', '_NEXT_USER');