Oracle密码概要文件,密码过期时间180天修改为3天,相关用户密码是否过期
#Oracle用户密码,概要文件修改测试
#默认的用户使用概要文件,默认概要文件密码过期时间参数180天,修改为3天,对于老的用户来说,是密码过期,还是未发生改变, 对于新用户来说,新设置的密码过期时间是否有效
#选取SYS or HR 用户作为老用户进行测试:
SYS > select username,account_status,lock_date,created,profile from dba_users where username in ('SYS','HR');
USERNAME ACCOUNT_STATUS LOCK_DATE CREATED PROFILE
------------------------------ -------------------------------- ------------------- ------------------- ------------------------------
SYS OPEN 2013-08-24 11:37:40 DEFAULT
HR OPEN 2016-05-26 00:29:36 DEFAULT --
#查询概要文件参数:
SYS > 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 --密码有效期,180后过期
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 --密码过期后,还能使用7天
SYS > select sysdate from dual;
SYSDATE
-------------------
2018-05-01 21:24:35
SYS > select expiry_date,username from dba_users where username in ('SYS','HR');
EXPIRY_DATE USERNAME
------------------- ------------------------------
2016-11-22 00:31:13 SYS
2018-05-23 22:32:23 HR --正常来说,22天后过期 +7天密码过期可用天数=29天
#修改概要文件:将密码有效期修改为3天+ 密码过期后允许使用7天,最多10天
alter profile default limit PASSWORD_LIFE_TIME 3;
#对于HR用户来说,是10天后过期,还是已经过期,查询
SYS > select expiry_date,username from dba_users where username in ('SYS','HR');
EXPIRY_DATE USERNAME
------------------- ------------------------------
2016-05-29 00:31:13 SYS
2017-11-27 22:32:23 HR
SYS > select username,account_status,lock_date,created,profile from dba_users where username in ('SYS','HR');
USERNAME ACCOUNT_STATUS LOCK_DATE CREATED PROFILE
------------------------------ -------------------------------- -------------------
SYS OPEN 2013-08-24 11:37:40 DEFAULT
HR OPEN 2016-05-26 00:29:36 DEFAULT
#################################以上视图,未主动更新###################
SYS > conn hr/hr --连接用户,提示密码过期,宽限7天(即使密码过期,但是还是允许连接,宽限时间概要文件参数PASSWORD_GRACE_TIME决定,触发条件,密码过期,用户连接后触发)
ERROR:
ORA-28002: the password will expire within 7 days
Connected.
Session altered.
HR > show user
USER is "HR"
#再次查询视图:
SYS > select expiry_date,username from dba_users where username in ('SYS','HR');
EXPIRY_DATE USERNAME
------------------- ------------------------------
2016-05-29 00:31:13 SYS
2018-05-08 21:39:51 HR --密码过期时间 :7天后 与系统时间,对比,得出结论,宽限时间概要文件参数决定
SYS > select sysdate from dual;
SYSDATE
-------------------
2018-05-01 21:41:32 --查询用户状态:账户密码过期状态,但未锁定
SYS > select username,account_status,lock_date,created,profile from dba_users where username in ('SYS','HR');
USERNAME ACCOUNT_STATUS LOCK_DATE CREATED PROFILE
------------------------------ --------------------------------
SYS OPEN 2013-08-24 11:37:40 DEFAULT
HR EXPIRED(GRACE) 2016-05-26 00:29:36 DEFAULT
#对于新创建的用户来说,是否使用修改后的概要文件,密码保留时间
SYS > create user abc identified by abc;
select username,account_status,lock_date,created,profile from dba_users where username in ('ABC');
USERNAME ACCOUNT_STATUS LOCK_DATE CREATED PROFILE
------------------------------ -------------------------------- -------------------
ABC OPEN 2018-05-01 21:34:58 DEFAULT
#
SYS > select expiry_date,username from dba_users where username in ('ABC');
EXPIRY_DATE USERNAME
------------------- ------------------------------
2018-05-04 21:34:58 ABC
#结论:对于老的用户来说,密码已经过期,但是有宽限时间7天内用户可用, 对于新的用户来说,密码过期时间,按照用户创建的时间+密码过期时间设置