Profiles
In the contextof system resources, a profile is a named set of resource limits and passwordparameters that restrict database usage and instance resources for a user. Profilescan limit the number of concurrent sessions for a user, CPU processing time availablefor each session, and amount of logical I/O available. For example, the clerkprofile could limit a user to system resources required for clerical tasks.
Note: It is preferable touse Database Resource Manager to limit resources and to use profiles to managepasswords.
Profiles providea single point of reference for users that share a set of attributes. You canassign a profile to one set of users, and a default profile to all others. Eachuser has at most one profile assigned at any point in time.
Profile文件里保存了一些资源的限制信息以及密码参数限制,如限制一个用户的数据库使用率、实例资源等。
如果在创建用户时指定了profile。那么用户就不能超过profile里指定的资源。
在我们创建用户的时候,如果没有指定profile,那么就可以使用默认的profile。名称叫DEFAULT。
--查看用户的profile
SQL>select username,profile from dba_users;
USERNAME PROFILE
------------------------------------------------------------------------------------------------------------------------
SYS DEFAULT
SYSTEM DEFAULT
SHARON DEFAULT
GGS DEFAULT
TEST DEFAULT
OUTLN DEFAULT
MGMT_VIEW DEFAULT
FLOWS_FILES DEFAULT
MDSYS DEFAULT
ORDSYS DEFAULT
EXFSYS DEFAULT
….
--查看default profile
SQL>select * from dba_profiles where profile='DEFAULT';
PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT
--------------------------------------------- -------------------- ----------
DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED
DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED
DEFAULT CPU_PER_SESSION KERNEL UNLIMITED
DEFAULT CPU_PER_CALL KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_SESSIONKERNEL 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 NULL
DEFAULT PASSWORD_LOCK_TIME PASSWORD 1
DEFAULT PASSWORD_GRACE_TIME PASSWORD 7
创建profile
SQL>create profile test_profile limit PASSWORD_LIFE_TIME unlimited;
Profile created.
查看TEST_PROFILE
SQL>select * from dba_profiles where profile='TEST_PROFILE';
PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT
--------------------------------------------- -------------------- ----------
TEST_PROFILE COMPOSITE_LIMIT KERNEL DEFAULT
TEST_PROFILE SESSIONS_PER_USER KERNEL DEFAULT
TEST_PROFILE CPU_PER_SESSION KERNEL DEFAULT
TEST_PROFILE CPU_PER_CALL KERNEL DEFAULT
TEST_PROFILE LOGICAL_READS_PER_SESSION KERNEL DEFAULT
TEST_PROFILE LOGICAL_READS_PER_CALL KERNEL DEFAULT
TEST_PROFILE IDLE_TIME KERNEL DEFAULT
TEST_PROFILE CONNECT_TIME KERNEL DEFAULT
TEST_PROFILE PRIVATE_SGA KERNEL DEFAULT
TEST_PROFILE FAILED_LOGIN_ATTEMPTS PASSWORD DEFAULT
TEST_PROFILE PASSWORD_LIFE_TIME PASSWORD UNLIMITED
TEST_PROFILE PASSWORD_REUSE_TIME PASSWORD DEFAULT
TEST_PROFILE PASSWORD_REUSE_MAX PASSWORD DEFAULT
TEST_PROFILE PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT
TEST_PROFILE PASSWORD_LOCK_TIME PASSWORD DEFAULT
TEST_PROFILE PASSWORD_GRACE_TIME PASSWORD DEFAULT
创建用户时指定profile
SQL>create user test_p identified by test_p profiletest_profile;
User created.
SQL>select username,profile from dba_users where username='TEST_P';
USERNAME PROFILE
--------------------------------------------------
TEST_P TEST_PROFILE
修改已建用户的profile
SQL>alter user sharon profile test_profile;
User altered.
SQL>select username,profile from dba_users where username='SHARON';
USERNAME PROFILE
--------------------------------------------------
SHARON TEST_PROFILE