User_Profile设置成Default= Resuorce UNLIMITED?

前几日,有位新员工准备调整数据库中User_Profile的SESSIONS_PER_USER限制前抛出了一个结论:“如果把用户的Profile设置成Default后就等同于资源无限制了,百度搜索显示很多文章表明Default便代表无限制

很显然这个结论是不严谨或者是错误的,Oracle官方文档中已经有明确的说明:

“Specify DEFAULT if you want to omit a limit for this resource in this profile. A user assigned this profile is subject to the limit for this resource specified in the DEFAULT profile. The DEFAULT profile initially defines unlimited resources. You can change those limits with the ALTER PROFILE statement.

Any user who is not explicitly assigned a profile is subject to the limits defined in the DEFAULT profile. Also, if the profile that is explicitly assigned to a user omits limits for some resources or specifies DEFAULT for some limits, then the user is subject to the limits on those resources defined by the DEFAULT profile.”

也就是说,如果用户省略设置单独PROFILE, 分配此PROFILE的用户受Default Profile文件中指定的此资源的限制,如果之前没有修改DEFAULT PROFILE前,DEFAULT PROFILE中的资源限定是: unlimited ,也就是说,即使用户使用继承了Default Profile内容,众多数据库中配置不一,因为Default Profile是可以被修改的, 在没有确认Default Profile资源限制前不能下此结论即”Default Profile = unlimited resources”

为了帮助他今后思考更加严谨,做了一个实验给他:

SQL> select *from dba_profiles where profile='DEFAULT';

PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT                        COMPOSITE_LIMIT                  KERNEL   UNLIMITED
DEFAULT                        SESSIONS_PER_USER                KERNEL   3
DEFAULT                        CPU_PER_SESSION                  KERNEL   UNLIMITED
DEFAULT                        CPU_PER_CALL                     KERNEL   UNLIMITED
DEFAULT                        LOGICAL_READS_PER_SESSION        KERNEL   UNLIMITED
DEFAULT                        LOGICAL_READS_PER_CALL           KERNEL   UNLIMITED
DEFAULT                        IDLE_TIME                        KERNEL   UNLIMITED
DEFAULT                        CONNECT_TIME                     KERNEL   UNLIMITED
DEFAULT                        PRIVATE_SGA                      KERNEL   UNLIMITED
DEFAULT                        FAILED_LOGIN_ATTEMPTS            PASSWORD 10
DEFAULT                        PASSWORD_LIFE_TIME               PASSWORD 180
DEFAULT                        PASSWORD_REUSE_TIME              PASSWORD UNLIMITED
DEFAULT                        PASSWORD_REUSE_MAX               PASSWORD UNLIMITED
DEFAULT                        PASSWORD_VERIFY_FUNCTION         PASSWORD VERIFY_FUNCTION_11G
DEFAULT                        PASSWORD_LOCK_TIME               PASSWORD 1
DEFAULT                        PASSWORD_GRACE_TIME              PASSWORD 7

16 rows selected.

SQL> create user liangce identified by liangce123;

User created.

SQL> grant connect,resource to liangce;

Grant succeeded.

SQL> select username,profile from dba_users where username='LIANGCE';

USERNAME                       PROFILE
------------------------------ ------------------------------
LIANGCE                        DEFAULT

开启3个会话,均执行:
SQL> conn liangce/liangce123;
Connected.

第4个会话:
SQL> conn liangce/liangce123;
ERROR:
ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit
Warning: You are no longer connected to ORACLE.
创建新的PROFILE,并没有显示指定SESSIONS_PER_USER limit
SQL> CREATE PROFILE "LIANGCE"
  2      LIMIT
  3           COMPOSITE_LIMIT DEFAULT
         CPU_PER_SESSION DEFAULT
         CPU_PER_CALL DEFAULT
  4    5    6           LOGICAL_READS_PER_SESSION DEFAULT
  7           LOGICAL_READS_PER_CALL DEFAULT
  8           IDLE_TIME DEFAULT
  9           CONNECT_TIME DEFAULT
 10           PRIVATE_SGA DEFAULT
 11           FAILED_LOGIN_ATTEMPTS DEFAULT
 12           PASSWORD_LIFE_TIME DEFAULT
 13           PASSWORD_REUSE_TIME DEFAULT
 14           PASSWORD_REUSE_MAX DEFAULT
 15           PASSWORD_VERIFY_FUNCTION DEFAULT
 16           PASSWORD_LOCK_TIME DEFAULT
 17           PASSWORD_GRACE_TIME DEFAULT;

Profile created.

SQL> alter profile LIANGCE limit SESSIONS_PER_USER 2;

Profile altered.

SQL> select *from dba_profiles where PROFILE='LIANGCE';

PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
LIANGCE                        COMPOSITE_LIMIT                  KERNEL   DEFAULT
LIANGCE                        SESSIONS_PER_USER                KERNEL   2
LIANGCE                        CPU_PER_SESSION                  KERNEL   DEFAULT
LIANGCE                        CPU_PER_CALL                     KERNEL   DEFAULT
LIANGCE                        LOGICAL_READS_PER_SESSION        KERNEL   DEFAULT
LIANGCE                        LOGICAL_READS_PER_CALL           KERNEL   DEFAULT
LIANGCE                        IDLE_TIME                        KERNEL   DEFAULT
LIANGCE                        CONNECT_TIME                     KERNEL   DEFAULT
LIANGCE                        PRIVATE_SGA                      KERNEL   DEFAULT
LIANGCE                        FAILED_LOGIN_ATTEMPTS            PASSWORD DEFAULT
LIANGCE                        PASSWORD_LIFE_TIME               PASSWORD DEFAULT
LIANGCE                        PASSWORD_REUSE_TIME              PASSWORD DEFAULT
LIANGCE                        PASSWORD_REUSE_MAX               PASSWORD DEFAULT
LIANGCE                        PASSWORD_VERIFY_FUNCTION         PASSWORD DEFAULT
LIANGCE                        PASSWORD_LOCK_TIME               PASSWORD DEFAULT
LIANGCE                        PASSWORD_GRACE_TIME              PASSWORD DEFAULT

SQL> alter user liangce PROFILE LIANGCE;

User altered.

SQL> select username,profile from dba_users where username='LIANGCE';

USERNAME                       PROFILE
------------------------------ ------------------------------
LIANGCE                        LIANGCE

此时,才不会继续继承DEFAULT的配置,SESSIONS_PER_USER 为2,只能再一个实例中登录2个会话,RAC多个实例中单独计算,不进行累计。