2

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;
Improve this question
1
  • Was the profile assigned before or after the user logged in? Were there multiple sessions? Were they all established at the same time? Each session will have its own, separate 8 hour clock; they won't all terminate at once unless they were all established at once.
    – pmdba
    Jun 22 at 20:08

1 Answer 1

Reset to default
4

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.

Improve this answer
2
  • 1
    I have altered system and enabled resource_limit and it now works. CONNECT_TIME value is the one that terminates user's sessions. Additional question : is there any way once session is terminated, that user gets Locked at the same time?
    – korodani
    Jun 22 at 21:10
  • @korodani That will not happen. Users can be locked manually or automatically by password rules (failed login attemptso, expiry).
    – Balazs Papp
    Jun 23 at 10:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.