profile用户密码管理

--查看系统所有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

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/