--1、创建profile
格式:
Create Profile profile_name Limit
参数名1 值1
......
参数名n 值n;
SQL> Create Profile prof Limit
2 Sessions_Per_User 2
3 Failed_Login_Attempts 2
4 Password_Life_Time 5
5 Password_Grace_Time 0
6 Password_Lock_Time 2;
Profile created
--2、修改profile
SQL> Alter Profile prof Limit
2 Sessions_Per_User 1
3 Password_Grace_Time 3;
Profile altered
--3、查看资源文件
SQL> Select * From dba_profiles Where Profile='PROF';
PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT
------------------------------ -------------------------------- ------------- ----------------------------------------
PROF COMPOSITE_LIMIT KERNEL DEFAULT
PROF SESSIONS_PER_USER KERNEL 1
PROF CPU_PER_SESSION KERNEL DEFAULT
PROF CPU_PER_CALL KERNEL DEFAULT
PROF LOGICAL_READS_PER_SESSION KERNEL DEFAULT
PROF LOGICAL_READS_PER_CALL KERNEL DEFAULT
PROF IDLE_TIME KERNEL DEFAULT
PROF CONNECT_TIME KERNEL DEFAULT
PROF PRIVATE_SGA KERNEL DEFAULT
PROF FAILED_LOGIN_ATTEMPTS PASSWORD 2
PROF PASSWORD_LIFE_TIME PASSWORD 5
PROF PASSWORD_REUSE_TIME PASSWORD DEFAULT
PROF PASSWORD_REUSE_MAX PASSWORD DEFAULT
PROF PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT
PROF PASSWORD_LOCK_TIME PASSWORD 2
PROF PASSWORD_GRACE_TIME PASSWORD 3
16 rows selected
SQL>
--4、删除资源文件
SQL> drop profile prof;
Profile dropped
--5、启用资源限制
SQL> alter system set resource_limit=true;
System altered
SQL>
--6、创建profile并指定给用户
SQL> Create Profile prof Limit
2 Sessions_Per_User 1
3 Failed_Login_Attempts 2
4 Password_Life_Time 5
5 Password_Grace_Time 3
6 Password_Lock_Time 4;
Profile created
SQL> Create User u1 Identified By u1 Profile prof ;
User created
SQL> Grant Connect To u1;
Grant succeeded
SQL> Select username,to_char(created,'yyyy-mm-dd hh24:mi:ss') As created,
2 account_status,to_char(lock_date,'yyyy-mm-dd hh24:mi:ss') As lock_date,
3 to_char(expiry_date,'yyyy-mm-dd hh24:mi:ss') As expiry_date
4 From dba_users Where username='U1';
USERNAME CREATED ACCOUNT_STATUS LOCK_DATE EXPIRY_DATE
------------------------------ ------------------- -------------------------------- ------------------- -------------------
U1 2015-08-12 23:08:32 OPEN 2015-08-17 23:08:32
格式:
Create Profile profile_name Limit
参数名1 值1
......
参数名n 值n;
SQL> Create Profile prof Limit
2 Sessions_Per_User 2
3 Failed_Login_Attempts 2
4 Password_Life_Time 5
5 Password_Grace_Time 0
6 Password_Lock_Time 2;
Profile created
--2、修改profile
SQL> Alter Profile prof Limit
2 Sessions_Per_User 1
3 Password_Grace_Time 3;
Profile altered
--3、查看资源文件
SQL> Select * From dba_profiles Where Profile='PROF';
PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT
------------------------------ -------------------------------- ------------- ----------------------------------------
PROF COMPOSITE_LIMIT KERNEL DEFAULT
PROF SESSIONS_PER_USER KERNEL 1
PROF CPU_PER_SESSION KERNEL DEFAULT
PROF CPU_PER_CALL KERNEL DEFAULT
PROF LOGICAL_READS_PER_SESSION KERNEL DEFAULT
PROF LOGICAL_READS_PER_CALL KERNEL DEFAULT
PROF IDLE_TIME KERNEL DEFAULT
PROF CONNECT_TIME KERNEL DEFAULT
PROF PRIVATE_SGA KERNEL DEFAULT
PROF FAILED_LOGIN_ATTEMPTS PASSWORD 2
PROF PASSWORD_LIFE_TIME PASSWORD 5
PROF PASSWORD_REUSE_TIME PASSWORD DEFAULT
PROF PASSWORD_REUSE_MAX PASSWORD DEFAULT
PROF PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT
PROF PASSWORD_LOCK_TIME PASSWORD 2
PROF PASSWORD_GRACE_TIME PASSWORD 3
16 rows selected
SQL>
--4、删除资源文件
SQL> drop profile prof;
Profile dropped
--5、启用资源限制
SQL> alter system set resource_limit=true;
System altered
SQL>
--6、创建profile并指定给用户
SQL> Create Profile prof Limit
2 Sessions_Per_User 1
3 Failed_Login_Attempts 2
4 Password_Life_Time 5
5 Password_Grace_Time 3
6 Password_Lock_Time 4;
Profile created
SQL> Create User u1 Identified By u1 Profile prof ;
User created
SQL> Grant Connect To u1;
Grant succeeded
SQL> Select username,to_char(created,'yyyy-mm-dd hh24:mi:ss') As created,
2 account_status,to_char(lock_date,'yyyy-mm-dd hh24:mi:ss') As lock_date,
3 to_char(expiry_date,'yyyy-mm-dd hh24:mi:ss') As expiry_date
4 From dba_users Where username='U1';
USERNAME CREATED ACCOUNT_STATUS LOCK_DATE EXPIRY_DATE
------------------------------ ------------------- -------------------------------- ------------------- -------------------
U1 2015-08-12 23:08:32 OPEN 2015-08-17 23:08:32