How to terminate user's sessions in Oracle DB exactly 8 hours after user connected?

All we need is an easy explanation of the problem, so here it is.

I would like to terminate user sessions (and optionally also lock the user), exactly 8 hours after user established DB connection. I tried creating a profile, but it just doesn’t terminate user’s sessions after 8h. I use Oracle Enterprise DB 12c.

CREATE PROFILE "PROFILE_8H_EXPIRY" LIMIT
  SESSIONS_PER_USER UNLIMITED
  CPU_PER_SESSION UNLIMITED
  CPU_PER_CALL UNLIMITED
  CONNECT_TIME 480 -- Specify the total elapsed time limit for a session, expressed in minutes.
  IDLE_TIME UNLIMITED
  LOGICAL_READS_PER_SESSION UNLIMITED
  LOGICAL_READS_PER_CALL UNLIMITED
  COMPOSITE_LIMIT UNLIMITED
  PRIVATE_SGA UNLIMITED
  FAILED_LOGIN_ATTEMPTS UNLIMITED
  INACTIVE_ACCOUNT_TIME UNLIMITED
  PASSWORD_LIFE_TIME 0.33 -- Specify the number of days the same password can be used for authentication
  PASSWORD_REUSE_TIME UNLIMITED
  PASSWORD_REUSE_MAX UNLIMITED
  PASSWORD_LOCK_TIME UNLIMITED -- Specify the number of days an account will be locked after too many failed login attemps.
  PASSWORD_GRACE_TIME 0.33 --  If the password is not changed during the grace period marked in days, the password expires.
  PASSWORD_VERIFY_FUNCTION FUNCTION_8H_EXPIRY;

ALTER USER JOHN PROFILE PROFILE_8H_EXPIRY;

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

CREATE PROFILE

To specify resource limits for a user, you must:

  • Enable resource limits dynamically with the ALTER SYSTEM statement or with the initialization parameter RESOURCE_LIMIT. This parameter
    does not apply to password resources. Password resources are always
    enabled.

Check the resource_limit parameter.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply