How to use the INACTIVE_ACCOUNT_TIME resource parameter in the 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.
We can use the INACTIVE_ACCOUNT_TIME resource parameter in the 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. Within the Production or Testing or Development database.1. By default, it is set to UNLIMITED.
2. The minimum setting is 15 and the maximum is 24855.
SQL> select RESOURCE_NAME,limit from dba_profiles where profile='DEFAULT';RESOURCE_NAME LIMIT------------------------------------------- -----------------------COMPOSITE_LIMIT UNLIMITEDSESSIONS_PER_USER UNLIMITEDCPU_PER_SESSION UNLIMITEDCPU_PER_CALL UNLIMITEDLOGICAL_READS_PER_SESSION UNLIMITEDLOGICAL_READS_PER_CALL UNLIMITEDIDLE_TIME UNLIMITEDCONNECT_TIME UNLIMITEDPRIVATE_SGA UNLIMITEDFAILED_LOGIN_ATTEMPTS 10PASSWORD_LIFE_TIME 180PASSWORD_REUSE_TIME UNLIMITEDPASSWORD_REUSE_MAX UNLIMITEDPASSWORD_VERIFY_FUNCTION NULLPASSWORD_LOCK_TIME 1PASSWORD_GRACE_TIME 7INACTIVE_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 "ENDUSERINACTIVE" 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 1552000/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='ENDUSERINACTIVE' and resource_name='INACTIVE_ACCOUNT_TIME';RESOURCE_NAME LIMIT------------------------------------------- -----------------------INACTIVE_ACCOUNT_TIME 30SQL> CREATE USER test identified by test123 profile ENDUSERINACTIVE;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
Note: Info On Lock Account Automatically with INACTIVE_ACCOUNT_TIME may differ in your environment like production, testing, development and naming conventions, etc..
We can use the INACTIVE_ACCOUNT_TIME resource parameter in the 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. Within the Production or Testing or Development database. 1. By default, it is set to UNLIMITED.
2. The minimum setting is 15 and the maximum is 24855.
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 "ENDUSERINACTIVE" 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 1552000/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='ENDUSERINACTIVE' and resource_name='INACTIVE_ACCOUNT_TIME'; RESOURCE_NAME LIMIT ------------------------------------------- ----------------------- INACTIVE_ACCOUNT_TIME 30 SQL> CREATE USER test identified by test123 profile ENDUSERINACTIVE; 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 "ENDUSERINACTIVE" 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 "ENDUSERINACTIVE" * ERROR at line 1: ORA-02377: invalid profile limit INACTIVE_ACCOUNT_TIMENote: Info On Lock Account Automatically with INACTIVE_ACCOUNT_TIME may differ in your environment like production, testing, development and naming conventions, etc..