Skip to main content

Search

Welcome to Tibero GTS!

Improvement of password_grace_time Operation

Document Type | Troubleshooting

Category | Patch/Upgrade

Applicable Product Version | 7FS02PS

Document Number | TPATS001

 

Issue

This explains improvements related to the operation of password_grace_time.
 
  • When password_grace_time is set to unlimited, it immediately expires as if the setting value is 0 instead of unlimited.
  • When password_grace_time is 0, the user is in expired (grace) status and can still log in without immediate expiration.
  • If password_grace_time is set to a value beyond 9999/12/31, the profile job continuously throws errors.
 

[Scenario]

#. User Create
SQLcreate user test01 identified by test01;
SQLgrant dba to test01;

#. User Create
SQLcreate user test01 identified by test01;
SQLgrant dba to test01;


#. Create profile
SQLcreate profile prof limit
password_life_time 2/1440
password_grace_time unlimited;


#. Check profile
SQLselect * from dba_profiles where profile='PROF' and RESOURCE_NAME in ('PASSWORD_LIFE_TIME', 'PASSWORD_GRACE_TIME');

#. Apply profile to user
SQLalter user test01 profile prof;

#. Check User status
SQLselect username, account_status, profile, created, expiry_date from dba_users where username='TEST01';

#. Check time
SQLalter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
SQLselect sysdate from dual;

#. Connect after expiration time - check status
SQLconn test01/test01
SQLselect username, account_status, profile, created, expiry_date from dba_users where username='TEST01'

 

 

Cause

The original design of unlimited seems to be intended not to use the grace feature, but it was judged appropriate to modify it so that it does not expire in expired (grace) status,
 
After the password expires, the grace time adds the number of days set in password_grace_time to the login_date for exptime. If the date passes in expired (grace) status, it expires. However, if the added date exceeds 9999/12/31, the DML throws an error, preventing the exptime update DML from executing.
 
 

Solutions

When password_grace_time is set to unlimited or a large value that causes the sum with login_date to exceed 9999/12/31 (an unrepresentable date type), the exptime is fixed to 9999/12/31.
 
When password_grace_time is set to 0, users who have passed password_life_time will immediately expire without going through grace status.
 

[Scenario]

#. When grace time is set to unlimited
SQLcol username for a20;
SQLcol account_status for a20;
SQLdrop user u1;
SQLdrop profile p1;
SQLcreate profile p1 limit password_life_time 1/2880 password_grace_time unlimited;

SQLcreate user u1 identified by tibero profile p1;


SQLgrant connect to u1;


SQLalter system set nls_date_format = 'YYYY/MM/DD HH24:MI:SS';


SQLselect username, account_status, expiry_date, SYSDATE from dba_users where username ='U1';


#. After patch, check expired (grace) - password not expired
SQLconn u1/tibero


SQLselect username, account_status, expiry_date from dba_users where username ='U1';


USERNAME                       ACCOUNT_STATUS                 EXPIRY_DATE
------------------------------ ------------------------------ -------------------
U1                             EXPIRED(GRACE)                 9999/12/31 00:00:00

1 row selected.

 

#. When grace time is set to 0

SQLalter profile p1 limit password_grace_time 0;
SQLselect username, account_status, expiry_date from dba_users where username ='U1';

USERNAME                       ACCOUNT_STATUS                 EXPIRY_DATE
------------------------------ ------------------------------ -------------------
U1                             EXPIRED                        2025/02/27 00:27:47

1 row selected.

SQLconn u1/tibero
SQLconn u1/tibero
TBR-17002: Password has expired.

Enter new password:

 

 

 

 

 

Related to