PROFILE——限制用户资源、密码参数

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