oracle profile password_life_time,password_life_time引发的生产问题

上午突然接到业务部门电话，反映某重要业务交易异常。放下电话后手心紧张的冒汗，该业务非常重要，即便中断几分钟对单位影响巨大。先不想这些，硬着头皮尽快排查问题吧。调用脚本进行了一些常规检查，都未发生异常。应用服务器各项资源正常，数据库服务器CPU、内存、文件系统都没发现什么问题。奇怪了，什么原因造成的呢？正在一筹莫展找不到眉目的时候，突然一旁的同事说了一句：数据库的应用用户无法正常登陆了，提示ORA-28001：口令已经失效。那赶紧的吧，alter userusernameidentified bypassword重置密码，重置密码后，第一时间跟业务部门确认交易恢复正常。故障很突然，但相应速度快，第一时间解决了问题，对业务未造成太大影响。

故障处理完毕后，静下心来考虑问题根源。“口令失效”，那应该和用户的profile相关。下面就按照此想法进行问题跟踪：数据库环境：

1.查看用户profile

2.查看DEFAULT定义资源的情况。

可以看到PASSWORD_LIFE_TIME的LIMIT值为180，PASSWORD_GRACE_TIME的LIMIT值为7，因此用户同一密码一旦使用超过180(PASSWORD_LIFE_TIME的LIMIT)天，数据库就会警告提醒你修改密码，如果在7(PASSWORD_GRACE_TIME)天内依然没有修改，数据库会拒绝用户连接。仔细推算了一下，上次该用户的密码变更应该是半年前(【背景】该用户16年9月份从10g迁移到现数据库11g)，时间点和口令失效的时间完全吻合。和预想的一样，果然是profile造成的，大功告成。突然一想，咦，不对啊？为什么之前在旧数据库运行了好几年没出现此情况，刚迁移到新数据库才半年就出现了此情况呢。

难道数据库默认的default profile的这个参数11g版本做了修改？带着这个疑问，找了一台10.2.0.1.0的数据库进行了确认，结果如下：

点击(此处)折叠或打开

SELECTRESOURCE_NAME,RESOURCE_TYPE,LIMITFROMdba_profilesWHEREPROFILE='DEFAULT'

RESOURCE_NAME                      RESOURCE_TYPE      LIMIT

------------------------------ ------------------------

COMPOSITE_LIMIT                       KERNEL              UNLIMITED

SESSIONS_PER_USER                    KERNEL              UNLIMITED

CPU_PER_SESSION                       KERNEL               UNLIMITED

CPU_PER_CALL                             KERNEL               UNLIMITED

LOGICAL_READS_PER_SESSION   KERNEL               UNLIMITED

LOGICAL_READS_PER_CALL         KERNEL               UNLIMITED

IDLE_TIME                                    KERNEL               UNLIMITED

CONNECT_TIME                          KERNEL                UNLIMITED

PRIVATE_SGA                              KERNEL                UNLIMITED

FAILED_LOGIN_ATTEMPTSPASSWORD10

PASSWORD_LIFE_TIMEPASSWORDUNLIMITED

PASSWORD_REUSE_TIMEPASSWORDUNLIMITED

PASSWORD_REUSE_MAXPASSWORDUNLIMITED

PASSWORD_VERIFY_FUNCTIONPASSWORDNULL

PASSWORD_LOCK_TIMEPASSWORDUNLIMITED

PASSWORD_GRACE_TIMEPASSWORDUNLIMITED

想必大家也猜出原因了，在10g中default的profile中PASSWORD_LIFE_TIME缺省值为UNLIMITED，数据库用户可以永久的使用一个密码，不会出现口令失效的情况。在11g版本做了修改同一密码仅能用180天，然后需要手工进行重置。这也是为什么用户在10g中好好的，但通过数据泵导入到11g过了半年左右，出现口令失效的原因。

那么10g的用户通过impdp迁移到11g(或者手工创建属于default profile用户)应该怎么规避出现上述情况呢？总体来说可以分为几种方案：

1.新建一个profile，对用户指定为新的profilealter user scott profile newpfile;

2.修改default profile的password_life_time为unlimitedalter profile default limit password_life_time unlimited;

3.建立定期(180内)修改用户密码的机制虽然技术上并不难，理论也很容易理解，属于一个小细节，但就是这样的小细节可能会对生产系统产生大的影响，因此做咱DBA这一行不但要懂技术，还应仔细认真，不要轻视任何小细节。

关于password类型的设置，每个参数官网说明如下，整理在这里供大家参考。

Use the following clauses to set password parameters. Parameters that set lengths of time are interpreted in number of days. For testing purposes you can specify minutes (n/1440) or even seconds (n/86400).FAILED_LOGIN_ATTEMPTSSpecify the number of failed attempts to log in to the user account before the account is locked.

PASSWORD_LIFE_TIMESpecify the number of days the same password can be used for authentication. If you also set a value for PASSWORD_GRACE_TIME, the password expires if it is not changed within the grace period, and further connections are rejected. If you do not set a value for PASSWORD_GRACE_TIME, its default of UNLIMITED will cause the database to issue a warning but let the user continue to connect indefinitely.

PASSWORD_REUSE_TIMEand PASSWORD_REUSE_MAXThese two parameters must be set in conjunction with each other. PASSWORD_REUSE_TIME specifies the number of days before which a password cannot be reused.PASSWORD_REUSE_MAX specifies the number of password changes required before the current password can be reused. For these parameter to have any effect, you must specify an integer for both of them.If you specify an integer for both of these parameters, then the user cannot reuse a password until the password has been changed the password the number of times specified for PASSWORD_REUSE_MAX during the number of days specified for PASSWORD_REUSE_TIME.For example, if you specify PASSWORD_REUSE_TIME to 30 and PASSWORD_REUSE_MAX to 10, then the user can reuse the password after 30 days if the password has already been changed 10 times. If you specify an integer for either of these parameters and specify UNLIMITED for the other, then the user can never reuse a password. If you specify DEFAULT for either parameter, then Oracle Database uses the value defined in the DEFAULT profile. By default, all parameters are set to UNLIMITED in the DEFAULT profile. If you have not changed the default setting of UNLIMITED in the DEFAULT profile, then the database treats the value for that parameter as UNLIMITED. If you set both of these parameters to UNLIMITED, then the database ignores both of them.

PASSWORD_LOCK_TIMESpecify the number of days an account will be locked after the specified number of consecutive failed login attempts.

PASSWORD_GRACE_TIMESpecify the number of days after the grace period begins during which a warning is issued and login is allowed. If the password is not changed during the grace period, the password expires.

PASSWORD_VERIFY_FUNCTIONThe PASSWORD_VERIFY_FUNCTION clause lets a PL/SQL password complexity verification script be passed as an argument to the CREATE PROFILE statement. Oracle Database provides a default script, but you can create your own routine or use third-party software instead. For function, specify the name of the password complexity verification routine.Specify NULL to indicate that no password verification is performed.If you specify expr for any of the password parameters, the expression can be of any form except scalar subquery expression.