SQL*Plus: Release 11.2.0.1.0 Production on Mon Feb 18 15:42:14 2013

OS:Linux node 2.6.32-200.13.1.el5uek #1 SMP Wed Jul 27 21:02:33 EDT 2011 x86_64 x86_64 x86_64 GNU/Linux

由于应用的需要，某些用户密码不能过期，需要将密码置成永不过期。下面进行这个实验：

SQL> create user test identified by test;

User created.

SQL> select username,account_status,expiry_date,profile from dba_users where username='TEST';

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DA PROFILE
------------------------------ -------------------------------- --------- ------------------------------
TEST                           OPEN                             17-AUG-13 DEFAULT

SQL> select sysdate from dual;

SYSDATE
---------
18-FEB-13

可以看出用户密码有效期为6个月。

控制用户密码有效期限的参数为password_life_time，此参数不能通过show parameter 来查看，它在user的profile中，默认创建用户时profile为default，我们可以查看一下default profile中都控制哪些参数：

SQL> select * from dba_profiles where profile='DEFAULT';

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 180

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 1
DEFAULT                        PASSWORD_GRACE_TIME              PASSWORD 7

16 rows selected.

可以看到password_life_time参数就在里面，而且值为180，单位天。

要让一个用户密码永不过期，我们可以创建一个新的profile文件，并将里面的password_life_time参数设置为unlimited。

创建一个新的profile，名为my_profile：

SQL> create profile my_profile limit password_life_time unlimited;

Profile created.

SQL> alter user test profile my_profile;

User altered.

SQL> select username,account_status,expiry_date,profile from dba_users where username='TEST';

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DA PROFILE
------------------------------ -------------------------------- --------- ------------------------------
TEST                           OPEN                                                            MY_PROFILE

这时我们可以看到EXPITY_DATE已经为空了，代表该用户密码永不过期。

我们也可以通过查看新创建的my_profile的内容：

SQL> select * from dba_profiles where profile='MY_PROFILE';

PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
MY_PROFILE                     COMPOSITE_LIMIT                  KERNEL   DEFAULT
MY_PROFILE                     SESSIONS_PER_USER                KERNEL   DEFAULT
MY_PROFILE                     CPU_PER_SESSION                  KERNEL   DEFAULT
MY_PROFILE                     CPU_PER_CALL                     KERNEL   DEFAULT
MY_PROFILE                     LOGICAL_READS_PER_SESSION        KERNEL   DEFAULT
MY_PROFILE                     LOGICAL_READS_PER_CALL           KERNEL   DEFAULT
MY_PROFILE                     IDLE_TIME                        KERNEL   DEFAULT
MY_PROFILE                     CONNECT_TIME                     KERNEL   DEFAULT
MY_PROFILE                     PRIVATE_SGA                      KERNEL   DEFAULT
MY_PROFILE                     FAILED_LOGIN_ATTEMPTS            PASSWORD DEFAULT
MY_PROFILE                     PASSWORD_LIFE_TIME               PASSWORD UNLIMITED

PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
MY_PROFILE                     PASSWORD_REUSE_TIME              PASSWORD DEFAULT
MY_PROFILE                     PASSWORD_REUSE_MAX               PASSWORD DEFAULT
MY_PROFILE                     PASSWORD_VERIFY_FUNCTION         PASSWORD DEFAULT
MY_PROFILE                     PASSWORD_LOCK_TIME               PASSWORD DEFAULT
MY_PROFILE                     PASSWORD_GRACE_TIME              PASSWORD DEFAULT

16 rows selected.

可以发现password_life_time已经是unlimited了。

 

我们也可以在创建oracle  用户的时候就指定profile：

SQL> create user test2 identified by test2 profile my_profile;

User created.

SQL> select username,account_status,expiry_date,profile from dba_users where username='TEST2';

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DA PROFILE
------------------------------ -------------------------------- --------- ------------------------------
TEST2                          OPEN                                       MY_PROFILE