Oracle密码概要文件,密码过期时间180天修改为3天,相关用户密码是否过期

时间:2022-08-17 16:31:34

#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天内用户可用, 对于新的用户来说,密码过期时间,按照用户创建的时间+密码过期时间设置