--查看系统所有profile文件
SQL> col profile for a10
SQL> col resource_name for a30
SQL> col resource for a10
SQL> select * from dba_profiles;
PROFILE RESOURCE_NAME RESOURCE 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_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 UNLIMITED
PROFILE RESOURCE_NAME RESOURCE LIMIT
---------- ------------------------------ -------- ----------------------------------------
DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL
DEFAULT PASSWORD_LOCK_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_GRACE_TIME PASSWORD UNLIMITED
16 rows selected
一、profile相关参数详解
1、对数据库资源做限制
{ { SESSIONS_PER_USER 每个用户名所允许的并行会话数
| CPU_PER_SESSION 一个会话一共可以使用的CPU时间,单位是百分之一秒
| CPU_PER_CALL 一次SQL调用(解析、执行和获取)允许使用的CPU时间
| CONNECT_TIME 限制会话连接时间,单位是分钟
| IDLE_TIME 允许空闲会话的时间,单位是分钟
| LOGICAL_READS_PER_SESSION 限制会话对数据块的读取,单位是块
| LOGICAL_READS_PER_CALL 限制SQL调用对数据块的读取,单位是块
| COMPOSITE_LIMIT “组合打法”
} { integer | UNLIMITED | DEFAULT }
| PRIVATE_SGA 限制会话在SGA中Shared Pool中私有空间的分配 { size_clause | UNLIMITED | DEFAULT}
}
2、对密码做限制
{ { FAILED_LOGIN_ATTEMPTS 帐户被锁定之前可以错误尝试的次数
| PASSWORD_LIFE_TIME 密码可以被使用的天数,单位是天,默认值180天
| PASSWORD_REUSE_TIME 密码可重用的间隔时间(结合PASSWORD_REUSE_MAX)
| PASSWORD_REUSE_MAX 密码的最大改变次数(结合PASSWORD_REUSE_TIME)
| PASSWORD_LOCK_TIME 超过错误尝试次数后,用户被锁定的天数,默认1天
| PASSWORD_GRACE_TIME 当密码过期之后还有多少天可以使用原密码
} { expr | UNLIMITED | DEFAULT }
| PASSWORD_VERIFY_FUNCTION { function | NULL | DEFAULT }
}
二、创建、修改profile
{ { SESSIONS_PER_USER 每个用户名所允许的并行会话数
| CPU_PER_SESSION 一个会话一共可以使用的CPU时间,单位是百分之一秒
| CPU_PER_CALL 一次SQL调用(解析、执行和获取)允许使用的CPU时间
| CONNECT_TIME 限制会话连接时间,单位是分钟
| IDLE_TIME 允许空闲会话的时间,单位是分钟
| LOGICAL_READS_PER_SESSION 限制会话对数据块的读取,单位是块
| LOGICAL_READS_PER_CALL 限制SQL调用对数据块的读取,单位是块
| COMPOSITE_LIMIT “组合打法”
} { integer | UNLIMITED | DEFAULT }
| PRIVATE_SGA 限制会话在SGA中Shared Pool中私有空间的分配 { size_clause | UNLIMITED | DEFAULT}
}
2、对密码做限制
{ { FAILED_LOGIN_ATTEMPTS 帐户被锁定之前可以错误尝试的次数
| PASSWORD_LIFE_TIME 密码可以被使用的天数,单位是天,默认值180天
| PASSWORD_REUSE_TIME 密码可重用的间隔时间(结合PASSWORD_REUSE_MAX)
| PASSWORD_REUSE_MAX 密码的最大改变次数(结合PASSWORD_REUSE_TIME)
| PASSWORD_LOCK_TIME 超过错误尝试次数后,用户被锁定的天数,默认1天
| PASSWORD_GRACE_TIME 当密码过期之后还有多少天可以使用原密码
} { expr | UNLIMITED | DEFAULT }
| PASSWORD_VERIFY_FUNCTION { function | NULL | DEFAULT }
}
二、创建、修改profile
SQL> create profile prod_prof
limit
2 FAILED_LOGIN_ATTEMPTS 5
3 PASSWORD_LIFE_TIME 100
4 PASSWORD_LOCK_TIME 1/24/60
5 PASSWORD_GRACE_TIME 10;
Profile created.
SQL> select * from dba_profiles;
PROFILE RESOURCE_NAME RESOURCE 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_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 UNLIMITED
PROFILE RESOURCE_NAME RESOURCE LIMIT
---------- ------------------------------ -------- ----------------------------------------
DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL
DEFAULT PASSWORD_LOCK_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_GRACE_TIME PASSWORD UNLIMITED
PROD_PROF COMPOSITE_LIMIT KERNEL DEFAULT
PROD_PROF SESSIONS_PER_USER KERNEL DEFAULT
PROD_PROF CPU_PER_SESSION KERNEL DEFAULT
PROD_PROF CPU_PER_CALL KERNEL DEFAULT
PROD_PROF LOGICAL_READS_PER_SESSION KERNEL DEFAULT
PROD_PROF LOGICAL_READS_PER_CALL KERNEL DEFAULT
PROFILE RESOURCE_NAME RESOURCE LIMIT
---------- ------------------------------ -------- ----------------------------------------
PROD_PROF IDLE_TIME KERNEL DEFAULT
PROD_PROF CONNECT_TIME KERNEL DEFAULT
PROD_PROF PRIVATE_SGA KERNEL DEFAULT
PROD_PROF FAILED_LOGIN_ATTEMPTS PASSWORD 5
PROD_PROF PASSWORD_LIFE_TIME PASSWORD 100
PROD_PROF PASSWORD_REUSE_TIME PASSWORD DEFAULT
PROD_PROF PASSWORD_REUSE_MAX PASSWORD DEFAULT
PROD_PROF PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT
PROD_PROF PASSWORD_LOCK_TIME PASSWORD .0006
PROD_PROF PASSWORD_GRACE_TIME PASSWORD 10
32 rows selected.
--修改profile配置信息
SQL> alter profile PROD_PROF
limit PASSWORD_REUSE_TIME 1/24/60;
Profile altered.
SQL> alter profile PROD_PROF
limit PASSWORD_REUSE_MAX 3;
Profile altered.
三、修改用户默认profile文件
--查看用户profile信息
SQL> select username,profile from dba_users;
USERNAME PROFILE
------------------------------ ------------------------------
SCOTT DEFAULT
OPS$ORACLE DEFAULT
HA DEFAULT
TOM DEFAULT
TSMSYS DEFAULT
DIP DEFAULT
DBSNMP DEFAULT
OUTLN DEFAULT
SYS DEFAULT
SYSTEM DEFAULT
10 rows selected.
由此可见,默认都是使用defult profile
--修改tom用户的profile文件
SQL> alter user tom profile prod_prof;
User altered.
--测试tom用户使用新profile的效应(连输错五次密码,看帐号是否被锁)
SQL> conn tom/1
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn tom/2
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> conn tom/3
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> conn tom/4
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> conn tom/5
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> conn tom/tom
ERROR:
ORA-28000: the account is locked
--一分钟后帐号解锁,并提示密码快过期(期限为10天)
SQL> conn tom/tom
ERROR:
ORA-28002: the password will expire within 10 days
--修改tom密码,三次内且一分钟内不可重用密码设置
SQL> alter user tom identified by tom;
User altered.
SQL> alter user tom identified by tom;
alter user tom identified by tom
*
ERROR at line 1:
ORA-28007: the password cannot be reused
SQL> alter user tom identified by tom1;
User altered.
SQL> alter user tom identified by tom2;
User altered.
SQL> alter user tom identified by tom3;
User altered.
SQL> alter user tom identified by tom;
alter user tom identified by tom
*
ERROR at line 1:
ORA-28007: the password cannot be reused
注意:虽然PASSWORD_REUSE_MAX过了3次限制,但因PASSWORD_REUSE_TIME未达1分钟,也会受此限制。
--一分钟后,再试
SQL> alter user tom identified by tom;
User altered.
修改密码为tom成功。
四、删除profile
SQL> drop profile prod_prof;
drop profile prod_prof
*
ERROR at line 1:
ORA-02382: profile PROD_PROF has users assigned, cannot drop without CASCADE
注意:因为tom用户使用了prod_prof,所以不能直接删除,需采用级联删除方式,如下:
SQL> drop profile prod_prof
cascade;
Profile dropped.
SQL> select username,profile from dba_users;
USERNAME PROFILE
------------------------------ ----------
SCOTT DEFAULT
OPS$ORACLE DEFAULT
HA DEFAULT
TOM DEFAULT
TSMSYS DEFAULT
DIP DEFAULT
DBSNMP DEFAULT
OUTLN DEFAULT
SYS DEFAULT
SYSTEM DEFAULT
10 rows selected.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21251711/viewspace-1148946/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/21251711/viewspace-1148946/