在学习profile过程中,对于profile文件对sysdba用户能不能控制,一直不确定,
实验证明:
profile文件对sysdba的用户是不会起作用的
首先profile文件如下:
SQL> select * from dba_profiles where profile='FRANK_PROFILE';
profile文件对sysdba的用户是不会起作用的
首先profile文件如下:
SQL> select * from dba_profiles where profile='FRANK_PROFILE';
PROFILE RESOURCE_NAME RESOURCE LIMIT
-------------- -------------------------------- -------- ---------------
FRANK_PROFILE COMPOSITE_LIMIT KERNEL DEFAULT
FRANK_PROFILE SESSIONS_PER_USER KERNEL 5
FRANK_PROFILE CPU_PER_SESSION KERNEL DEFAULT
FRANK_PROFILE CPU_PER_CALL KERNEL DEFAULT
FRANK_PROFILE LOGICAL_READS_PER_SESSION KERNEL DEFAULT
FRANK_PROFILE LOGICAL_READS_PER_CALL KERNEL DEFAULT
FRANK_PROFILE IDLE_TIME KERNEL 2
FRANK_PROFILE CONNECT_TIME KERNEL DEFAULT
FRANK_PROFILE PRIVATE_SGA KERNEL DEFAULT
FRANK_PROFILE FAILED_LOGIN_ATTEMPTS PASSWORD 5
FRANK_PROFILE PASSWORD_LIFE_TIME PASSWORD DEFAULT
-------------- -------------------------------- -------- ---------------
FRANK_PROFILE COMPOSITE_LIMIT KERNEL DEFAULT
FRANK_PROFILE SESSIONS_PER_USER KERNEL 5
FRANK_PROFILE CPU_PER_SESSION KERNEL DEFAULT
FRANK_PROFILE CPU_PER_CALL KERNEL DEFAULT
FRANK_PROFILE LOGICAL_READS_PER_SESSION KERNEL DEFAULT
FRANK_PROFILE LOGICAL_READS_PER_CALL KERNEL DEFAULT
FRANK_PROFILE IDLE_TIME KERNEL 2
FRANK_PROFILE CONNECT_TIME KERNEL DEFAULT
FRANK_PROFILE PRIVATE_SGA KERNEL DEFAULT
FRANK_PROFILE FAILED_LOGIN_ATTEMPTS PASSWORD 5
FRANK_PROFILE PASSWORD_LIFE_TIME PASSWORD DEFAULT
PROFILE RESOURCE_NAME RESOURCE LIMIT
-------------- -------------------------------- -------- ---------------
FRANK_PROFILE PASSWORD_REUSE_TIME PASSWORD DEFAULT
FRANK_PROFILE PASSWORD_REUSE_MAX PASSWORD DEFAULT
FRANK_PROFILE PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT
FRANK_PROFILE PASSWORD_LOCK_TIME PASSWORD DEFAULT
FRANK_PROFILE PASSWORD_GRACE_TIME PASSWORD DEFAULT
-------------- -------------------------------- -------- ---------------
FRANK_PROFILE PASSWORD_REUSE_TIME PASSWORD DEFAULT
FRANK_PROFILE PASSWORD_REUSE_MAX PASSWORD DEFAULT
FRANK_PROFILE PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT
FRANK_PROFILE PASSWORD_LOCK_TIME PASSWORD DEFAULT
FRANK_PROFILE PASSWORD_GRACE_TIME PASSWORD DEFAULT
resource_limit参数设置为true
SQL> show parameter resource
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
resource_limit boolean TRUE
将该profile分配给sys用户
SQL> alter user sys profile frank_profile;
User altered.
SQL> select username,profile from dba_users;
USERNAME PROFILE
------------------------------ --------------
MGMT_VIEW DEFAULT
SYS FRANK_PROFILE
SQL> show parameter resource
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
resource_limit boolean TRUE
将该profile分配给sys用户
SQL> alter user sys profile frank_profile;
User altered.
SQL> select username,profile from dba_users;
USERNAME PROFILE
------------------------------ --------------
MGMT_VIEW DEFAULT
SYS FRANK_PROFILE
profile 中设置空闲是2分钟断开
但是发现该sys用户的状态一直是inactive
SQL> /
USERNAME STATUS
------------------------------ --------
ACTIVE
SYS INACTIVE
但是发现该sys用户的状态一直是inactive
SQL> /
USERNAME STATUS
------------------------------ --------
ACTIVE
SYS INACTIVE
现在考虑如果一个用户被授予sysdba权限会不会被断掉:
SQL> grant sysdba to frank;
Grant succeeded.
SQL> grant sysdba to frank;
Grant succeeded.
SQL> select username,profile from dba_users where username='FRANK';
USERNAME PROFILE
------------------------------ --------------
FRANK FRANK_PROFILE
USERNAME PROFILE
------------------------------ --------------
FRANK FRANK_PROFILE
打开两个会话,一个用sysdba连入,一个用普通frank用户连入:
SQL> conn frank/frank
Connected.
SQL> conn frank/frank
Connected.
SQL> conn frank/frank as sysdba
Connected.
发现原来只要由sysdba用户连入的普通用户,默认就变成sys用户,所以肯定也不会受到profile的影响。但是用普通的frank用户连入的会话,2分钟后状态发生改变会话被断开。
Connected.
发现原来只要由sysdba用户连入的普通用户,默认就变成sys用户,所以肯定也不会受到profile的影响。但是用普通的frank用户连入的会话,2分钟后状态发生改变会话被断开。
SQL> select username,status from v$session;
USERNAME STATUS
------------------------------ --------
ACTIVE
SYS INACTIVE
FRANK INACTIVE
USERNAME STATUS
------------------------------ --------
ACTIVE
SYS INACTIVE
FRANK INACTIVE
SQL> /
USERNAME STATUS
------------------------------ --------
ACTIVE
SYS INACTIVE
ACTIVE
SYS ACTIVE
ACTIVE
ACTIVE
FRANK SNIPED
------------------------------ --------
ACTIVE
SYS INACTIVE
ACTIVE
SYS ACTIVE
ACTIVE
ACTIVE
FRANK SNIPED
所以现在可以确定,sysdba权限会导致profile概要文件的失效,所以生产环境上我们应当谨慎赋予sysdba权限。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16860121/viewspace-714665/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/16860121/viewspace-714665/