-- This script alters the default parameters for Password Management
-- This means that all the users on the system have Password Management
-- enabled and set to the following values unless another profile is
-- created with parameter values set to different value or UNLIMITED
-- is created and assigned to the user.
ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 180
PASSWORD_VERIFY_FUNCTION verify_function_11G_WJZYY;
我们将这个脚本，遵守之前Oracle的命名方式，将其命名为utlpwdmg1.sql，放在同样的路径下。
这样，我们执行这个脚本就可以创建这个校验函数：
3.测试验证方案
将上面的删减版脚本进行测试并验证功能是否实现：
--执行脚本创建校验函数
@?/rdbms/admin/utlpwdmg1.sql
--确认执行成功
select limit from dba_profiles where profile='DEFAULT' and resource_name='PASSWORD_VERIFY_FUNCTION';
--将PASSWORD_LIFE_TIME修改为30（选做）
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME 30;
--查询dba_profiles内容
select * from dba_profiles order by 1;
--查询用户状态和过期时间
select USERNAME, PASSWORD, ACCOUNT_STATUS, LOCK_DATE, EXPIRY_DATE from dba_users;
测试用户密码不能与用户名相同或者相似，否则会修改失败：
--密码与用户名一样，修改失败：
SYS@linuxidc1 >alter user jingyu identified by jingyu;
alter user jingyu identified by jingyu
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20002: Password same as or similar to user
--密码与用户名相似，修改失败：
SYS@linuxidc1 >alter user jingyu identified by jingyu1;
alter user jingyu identified by jingyu1
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20005: Password same as or similar to user name
--密码与用户名不一致，修改成功：
SYS@linuxidc1 >alter user jingyu identified by alfred;
User altered.
4.用户最近一次的登录时间
11g默认开启了审计，从aud$表中可以查到用户最近登录的时间：
--查询数据库时区
select property_value from database_properties where property_name='DBTIMEZONE';
--查询aud$表
select MAX(to_char(a.ntimestamp#, 'YYYY-MM-DD HH24:MI:SS')) last_login,
u.username
from sys.aud$ a, dba_users u
where a.USERID(+) = u.username
and u.user_id > 90
group by u.username
ORDER BY 1;
结果示例：
SYS@linuxidc1 >select MAX(to_char(a.ntimestamp#, 'YYYY-MM-DD HH24:MI:SS')) last_login,
2 u.username
3 from sys.aud$ a, dba_users u
4 where a.USERID(+) = u.username
5 and u.user_id > 90
6 group by u.username
7 ORDER BY 1;
LAST_LOGIN USERNAME
------------------- ------------------------------
2018-04-17 07:16:46 JINGYU
TESTTESTTEST
XS$NULL
SYS@linuxidc1 >
上述查询结果LAST_LOGIN为空的用户，就是在审计中没有记录到该用户的登录信息。