Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2002
    Posts
    23

    Unanswered: Set up timeout in db table

    Session timeout is a common feature. Now what I want to do is like this:
    When a user logs in, I record a starttime in a table. If they stay in the system for 3 hrs, I will log them out.

    Is it possible to put a trigger in the table so that as long as 3 hrs past since starttime, the user can be logged out?

    Or, I have to run a procedure and compare starttime with current timestamp all the time?(I would believe this is a bad option).

    Any idea is welcome. Thanks!
    DTS -> Oracle

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    I doubt a trigger would give you the desired action.

    You could setup a job to run every hour or so and check the sysprocesses table for the last activity. If it has been more than three hour you can bump them off.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Apr 2002
    Posts
    23

    Smile

    Thanks Paul.

    You refer to a procedure when u say a "job"? and how do I schedule a proc to be run every hour?

    I worked on db mostly as a developer, so not familiar with this kind of stuff.

    Thank you for your help,

    -Lili
    DTS -> Oracle

  4. #4
    Join Date
    Oct 2002
    Posts
    369

    Post Re: Set up timeout in db table

    RE: Originally posted by lili3000
    Session timeout is a common feature. Now what I want to do is like this:
    When a user logs in, I record a starttime in a table. If they stay in the system for 3 hrs, I will log them out. Is it possible to put a trigger in the table so that as long as 3 hrs past since starttime, the user can be logged out? Or, I have to run a procedure and compare starttime with current timestamp all the time?(I would believe this is a bad option). Any idea is welcome. Thanks!
    [/QUOTE]

    Q1 [Is it possible to implement connection / "private logon" time limits via triggers]?
    A1 Yes. However this approach is likely to be "expensive" in terms of server resources if you have many user connections. In that case it will probably result in resource issues (either concurrency - resource locking issues, etc. if implemented on common tables, or user object count allocation resource related issues if implemented by instantiating supporting objects on a per connection basis).

    Q2 [Is it necessary to implement connection / "private logon" time limits via a procedure that runs continuously]?
    A2 No, not necessarily.

    Q3 [Any other ideas, comments etc.,] ?
    A3 Yes. The issue may be approached either by i) managing Sql Server login connections (directly), or by ii) creating a system to manage database accesses (indirectly). (In larger relatively complex environments, it is often an issue whose subtleties are only realized following an initial implementation, and one or two necessary corrective 'fixes'.)

    A3 i Managing Sql Server login connections (directly):
    Generally, this is easy to implement. Typically it amounts to one process that polls and records login connection information at an interval, and another that polls the recorded login connection information to identify and kill (and / or also deny access to) the appropriate user logins / connections (in your case those that are 'too old'). In certain environments, this approach tends to be a "blunt" tool that can refined to a certain (limited) point only; especially on a large multi-database sql server system that supports different applications with complex mixed authentication access designs that host a variety of 'types' of connections. Examples of pertinent issues to consider in crude implementations include applications that use multiple connections, apps that are not connection oriented, multi-server connectivity issues, etc., etc.) A common issue in environments not using and tracking unique logins (for each and every user) is that typically a user need only exit an application and restart it to obtain a new "fresh" connection and subvert the system (for applications that use their 'own' standard login connections regardless of user identity).

    A3 ii Creating a system to manage database access (indirectly):
    This approach actually lumps together many different related strategies, a number of which serve much more elaborate functions e.g.(auditing). A fairly common element involves an application oriented private system that in one form or another uniquely identifies, tokenizes and tracks a specific user's access, activities, and time for each database (oftentimes in far more granularly than that). This approach seems to be chosen often when shared logins, common application logins, and the like are already heavily implemented (this is not necessarily the best choice, however). Issues with this approach include the fact that access must be restricted to the applications to prevent user subversion. An obvious drawback is that the approach "reinvents the wheel" in that often an additional set of application user accounts must be administered and managed which are separate from and hidden to the OS / network / domain and sql server.

  5. #5
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    First I would write a proc to look at your table or the sysprocess system table figure out who needs to get booted and then kill the offending sessions. Once that is working, you can use Enterprise Manager to schedule the job.

    Open up EM, click on your server, Management, Jobs. You can probably fiddle with this a bit and figure it out, Books On Line has an excelent example of how to create a job.
    Paul Young
    (Knowledge is power! Get some!)

  6. #6
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    Using Paul's idea of master..sysporcesses you could go off of the last_batch, not the login_time. A user may have been logged into the system for more that 3 hours (DATEDIFF(hh,login_time,GETDATE()) > 3) but has just submitted a batch 1 minute ago.

    I don't know if connection pooling will effect your process. However I think I would rather build an internal clock in the online application. When a user submits a request the timer is set to zero and then starts to count up. On each submit the timer is set to zero, once the timer gets to 3 minutes the next submit for request would send to user to the login screen with some nice timeout message.
    I would have the timeout parameter as a variable that gets passed to the online application at startup time, that way you can increase or decrease the time without rebuilding the app.
    MCDBA

  7. #7
    Join Date
    Oct 2002
    Posts
    369
    Originally posted by achorozy
    RE: Using Paul's idea of master..sysporcesses you could go off of the last_batch, not the login_time. A user may have been logged into the system for more that 3 hours (DATEDIFF(hh,login_time,GETDATE()) > 3) but has just submitted a batch 1 minute ago.

    I don't know if connection pooling will effect your process. However I think I would rather build an internal clock in the online application. When a user submits a request the timer is set to zero and then starts to count up. On each submit the timer is set to zero, once the timer gets to 3 minutes the next submit for request would send to user to the login screen with some nice timeout message.
    I would have the timeout parameter as a variable that gets passed to the online application at startup time, that way you can increase or decrease the time without rebuilding the app.


    Those are some of the kinds of pertinent issues I tried to warn about (in my previous post on the subject).

    The general issue may be approached either by
    i) managing Sql Server login connections (directly), or by
    ii) creating a system to manage database accesses (indirectly); the achorozy "build an internal clock in the online application" suggestion falls into this latter category.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •