Oracle 11g密码utlpwdmg.sql

utlpwdmg.sql是用户建立密码复杂度校验函数的脚本，简单描述下使用情况：

首先检查当前系统profile情况
SQL> select * from dba_profiles order by profile;

PROFILE         RESOURCE_NAME                      RESOURCE   LIMIT
---------------------- --------------------------------
DEFAULT         COMPOSITE_LIMIT                    KERNEL     UNLIMITED
DEFAULT         LOGICAL_READS_PER_SESSION      KERNEL     UNLIMITED
DEFAULT         CPU_PER_SESSION                    KERNEL     UNLIMITED
DEFAULT         CPU_PER_CALL                      KERNEL     UNLIMITED
DEFAULT         FAILED_LOGIN_ATTEMPTS            PASSWORD 10
DEFAULT         PASSWORD_GRACE_TIME               PASSWORD UNLIMITED
DEFAULT         PASSWORD_LOCK_TIME              PASSWORD UNLIMITED
DEFAULT         PASSWORD_LIFE_TIME              PASSWORD UNLIMITED
DEFAULT         PASSWORD_VERIFY_FUNCTION       PASSWORD NULL
DEFAULT         PASSWORD_REUSE_MAX              PASSWORD UNLIMITED
DEFAULT         PASSWORD_REUSE_TIME              PASSWORD UNLIMITED
DEFAULT         PRIVATE_SGA                        KERNEL     UNLIMITED
DEFAULT         CONNECT_TIME                      KERNEL     UNLIMITED
DEFAULT         IDLE_TIME                          KERNEL     UNLIMITED
DEFAULT         LOGICAL_READS_PER_CALL          KERNEL     UNLIMITED
DEFAULT         SESSIONS_PER_USER                KERNEL     UNLIMITED

创建密码复杂度校验函数：

cd $ORACLE_HOME/rdbms/admin
SQL> @utlpwdmg.sql

Function created.


Profile altered.


Function created.

再次检查profile情况
SQL>  select * from dba_profiles order by profile;

PROFILE         RESOURCE_NAME                    RESOURCE LIMIT
----------------------- ------------  --------------------
DEFAULT         CPU_PER_SESSION                   KERNEL     UNLIMITED
DEFAULT         CPU_PER_CALL                    KERNEL     UNLIMITED
DEFAULT         SESSIONS_PER_USER              KERNEL     UNLIMITED
DEFAULT         PASSWORD_GRACE_TIME            PASSWORD 7
DEFAULT         PASSWORD_LOCK_TIME            PASSWORD 1
DEFAULT         PASSWORD_VERIFY_FUNCTION     PASSWORD VERIFY_FUNCTION_11G
DEFAULT         FAILED_LOGIN_ATTEMPTS          PASSWORD 10
DEFAULT         PASSWORD_LIFE_TIME            PASSWORD 180
DEFAULT         PASSWORD_REUSE_MAX            PASSWORD UNLIMITED
DEFAULT         PASSWORD_REUSE_TIME            PASSWORD UNLIMITED
DEFAULT         PRIVATE_SGA                      KERNEL     UNLIMITED
DEFAULT         CONNECT_TIME                    KERNEL     UNLIMITED
DEFAULT         IDLE_TIME                        KERNEL     UNLIMITED
DEFAULT         LOGICAL_READS_PER_CALL        KERNEL     UNLIMITED
DEFAULT         LOGICAL_READS_PER_SESSION    KERNEL     UNLIMITED
DEFAULT         COMPOSITE_LIMIT                  KERNEL     UNLIMITED

尝试修改密码：
SQL> alter user leiou identified by oracle;
alter user leiou identified by oracle
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20001: Password length less than 8

发现已经生效。

如果需要恢复到默认的profile，执行$ORACLE_HOME/rdbms/admin/undopwd.sql即可。
检查undopwd.sql的脚本：
[ora11g@oratest admin]$ cat undopwd.sql
Rem
Rem $Header: undopwd.sql 11-jul-2006.12:07:15 asurpur Exp $
Rem
Rem undopwd.sql
Rem
Rem Copyright (c) 2006, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      undopwd.sql - undo 11g password changes to the default profile  
Rem
Rem    DESCRIPTION
Rem      This script. is called by DBCA to undo the 11g secure configuration
Rem      changes to the password portion of the default profile. It reverts
Rem      to the default 10gR2 settings. It is not intended to be run during
Rem      upgrade, since that would undo all customer settings as well.
Rem
Rem    NOTES
Rem     
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    nlewis      07/11/06 - add comments, fix script
Rem    asurpur     06/16/06 - audit changes for sec config
Rem    asurpur     06/16/06 - Created
Rem

ALTER PROFILE DEFAULT LIMIT
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_GRACE_TIME UNLIMITED
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
;

--To check values:

--  select resource_name, limit from dba_profiles
--  where profile='DEFAULT' and resource_type='PASSWORD';

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28300293/viewspace-753754/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28300293/viewspace-753754/