In Oracle 12.2 Release We can use the INACTIVE_ACCOUNT_TIME resource parameter in profile to automatically lock the account of a database user who has not logged in to the database instance in a specified number of days.

1. By default, it is set to UNLIMITED.
2. The minimum setting is 15 and the maximum is 24855.

col RESOURCE_NAME for a43
col limit for a23
set lines 299
SQL> select RESOURCE_NAME,limit from dba_profiles where profile='DEFAULT';
 
RESOURCE_NAME                               LIMIT
------------------------------------------- -----------------------
COMPOSITE_LIMIT                             UNLIMITED
SESSIONS_PER_USER                           UNLIMITED
CPU_PER_SESSION                             UNLIMITED
CPU_PER_CALL                                UNLIMITED
LOGICAL_READS_PER_SESSION                   UNLIMITED
LOGICAL_READS_PER_CALL                      UNLIMITED
IDLE_TIME                                   UNLIMITED
CONNECT_TIME                                UNLIMITED
PRIVATE_SGA                                 UNLIMITED
FAILED_LOGIN_ATTEMPTS                       10
PASSWORD_LIFE_TIME                          180
PASSWORD_REUSE_TIME                         UNLIMITED
PASSWORD_REUSE_MAX                          UNLIMITED
PASSWORD_VERIFY_FUNCTION                    NULL
PASSWORD_LOCK_TIME                          1
PASSWORD_GRACE_TIME                         7
INACTIVE_ACCOUNT_TIME                       UNLIMITED ----------- > This is the resource_name introduced in oracle 12.2.
 
17 rows selected.

To make an account lock automatically after 30 days of inactivity, Create a profile by setting INACTIVE_ACCOUNT_TIME to 30 and Set the profile to that user.

CREATE PROFILE "END_PROFILE4"
    LIMIT
         COMPOSITE_LIMIT UNLIMITED
         SESSIONS_PER_USER UNLIMITED
         CPU_PER_SESSION UNLIMITED
         CPU_PER_CALL UNLIMITED
         LOGICAL_READS_PER_SESSION UNLIMITED
         LOGICAL_READS_PER_CALL UNLIMITED
         IDLE_TIME UNLIMITED
         CONNECT_TIME UNLIMITED
         PRIVATE_SGA UNLIMITED
         FAILED_LOGIN_ATTEMPTS 10
         PASSWORD_LIFE_TIME 15552000/86400
         PASSWORD_REUSE_TIME UNLIMITED
         PASSWORD_REUSE_MAX UNLIMITED
         PASSWORD_VERIFY_FUNCTION NULL
         PASSWORD_LOCK_TIME 86400/86400
         PASSWORD_GRACE_TIME 604800/86400
         INACTIVE_ACCOUNT_TIME 30;
 
SQL>  select RESOURCE_NAME,limit from dba_profiles where profile='END_PROFILE' and resource_name='INACTIVE_ACCOUNT_TIME';
 
RESOURCE_NAME                               LIMIT
------------------------------------------- -----------------------
INACTIVE_ACCOUNT_TIME                       30
 
SQL> CREATE USER testuser identified by testuser profile END_PROFILE;
 
User created.

If you try to give a value less than 15, it will throw error like – ORA-02377: invalid profile limit INACTIVE_ACCOUNT_TIME

CREATE PROFILE "END_PROFILE5"
    LIMIT
         COMPOSITE_LIMIT UNLIMITED
         SESSIONS_PER_USER UNLIMITED
         CPU_PER_SESSION UNLIMITED
         CPU_PER_CALL UNLIMITED
         LOGICAL_READS_PER_SESSION UNLIMITED
         LOGICAL_READS_PER_CALL UNLIMITED
         IDLE_TIME UNLIMITED
         CONNECT_TIME UNLIMITED
         PRIVATE_SGA UNLIMITED
         FAILED_LOGIN_ATTEMPTS 10
         PASSWORD_LIFE_TIME 15552000/86400
         PASSWORD_REUSE_TIME UNLIMITED
         PASSWORD_REUSE_MAX UNLIMITED
         PASSWORD_VERIFY_FUNCTION NULL
         PASSWORD_LOCK_TIME 86400/86400
         PASSWORD_GRACE_TIME 604800/86400
         INACTIVE_ACCOUNT_TIME 10;
 
   CREATE PROFILE "END_PROFILE5"
*
ERROR at line 1:
ORA-02377: invalid profile limit INACTIVE_ACCOUNT_TIME 

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.