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

oracle 12.2.profilesecurity ORACLE 12CORACLE SECURITY


Related Topics

ENABLE_DDL_LOGGING in oracle 12c
Open wallet automatically after starting the database
Purge AUD$ table using DBMS_AUDIT_MGMT
how to use DBMS_PRIVILEGE_CAPTURE to capture privs in oracle 12c
Lock account automatically with INACTIVE_ACCOUNT_TIME
How to move AUD$ table to another tablespace using DBMS_AUDIT_MGMT
Unified audit trail in Oracle 12c
orapwd tool for password file in oracle
how to send mail using utl_mail in oracle 11g
PDB Lockdown Profiles in Oracle 12.2

You May Also Like

ORA-32773: operation not supported for smallfile tablespace
OGG-00665 OCI Error describe for query
Useful DGMGRL commands in oracle dataguard
Change dbsnmp password for target db in oem 12c
Open wallet automatically after starting the database
How to keep or delete columns/Variable of a data frame in R
Oswatcher tool for collecting server diagnostic information
SAMPLE parameter in EXPDP to export subset of data
Useful flashback related commands
Shell script to monitor asm diskgroup usage

From This Website

Important views in For Oracle DBA in daily usage
How to setup dataguard broker configuration (DG broker) in 12c
EMCLI command - OEM 12C
ORA-32771: cannot add file to bigfile tablespace
how to use DBMS_PRIVILEGE_CAPTURE to capture privs in oracle 12c
ENABLE_PARALLEL_DML hint in oracle 12c
ORA-20101: TABLESPACE is not a ASSM (Automatic Segment Space Management) tablespace
Steps for changing public hostname for a standalone grid infrastructure
How to create encrypted tablespace in PDB( oracle 12c)
Ansible script to run script on remote server and fetch the output