Looking for ways to time-out inactive users

Looking for ways to time-out inactive users

Post by mms.. » Sun, 16 Sep 2001 01:01:43



Our ASE database version is 11.9.2.5 on NT.  An internal
audit questioned our inability to time-out inactive users as
well as locking logins after n- number of unsuccesfull
attempts.   Does anyone have workarounds for these.

Is there some means of creating a table keeping i/o, cpu
count info and killing those users who show no gains in i/o
during, say a two hour period.  What are some of the columns
I can query that give i/o, cpu, mem usage results?

I know that version 12 addresses the max failed logins
problem, however our application vendor has not certified 12
yet and probably wont for at least 6 months.  

-d

 
 
 

Looking for ways to time-out inactive users

Post by Paul Tom » Sun, 16 Sep 2001 01:41:48




>Our ASE database version is 11.9.2.5 on NT.  An internal
>audit questioned our inability to time-out inactive users as
>well as locking logins after n- number of unsuccesfull
>attempts.   Does anyone have workarounds for these.

>Is there some means of creating a table keeping i/o, cpu
>count info and killing those users who show no gains in i/o
>during, say a two hour period.  What are some of the columns
>I can query that give i/o, cpu, mem usage results?

cpu physical_io memusage in sysprocesses. In 11.9.2, you don't have dynamic
SQL, so you would probably have to write a script to prepare the kill
statements based on info in syslogins, then run the script. You
probably want to put the kill in an IF statement to make sure the
total cpu and io has not creeped up in the mean time. It is still
somewhat dangerous because there is a nonzero amount of time between
the IF and the Kill. ie, there is a small chance that the process
actually logs out, and you kill a brand new process. Also, you want to
make sure this process is not idle because of being blocked by someone else.

Quote:>I know that version 12 addresses the max failed logins
>problem, however our application vendor has not certified 12
>yet and probably wont for at least 6 months.  

Does your application have an error handler in the login process? You
can write failed logins to a table, and use that to count the failed
logins. Loggin in can then be blocked using the application for anyone
who exceeded the limit.
A similar thing can be done using an Open Server as a passthrough server.
The application does not have to change, it points at the open server
and all SQL is passed through. The Open server intercepts any return code
from a bad login, and updates the table we talked about earlier. It also
disallows logins if the count is too high.

Paul
--

10000+ Humorous Quotes              http://www.tomkoinc.com/quotes.html
"Even if you can deceive people about a product through misleading statements,
sooner or later the product will speak for itself." - Hajime Karatsu

 
 
 

Looking for ways to time-out inactive users

Post by mms.. » Wed, 19 Sep 2001 11:32:25


Thanks Tom,

Thanks Tom,

I am working on a little procedure that does an insert every
15 minutes or so and puts fields  like spid, suid,
physical_io, etc  into a kill_user table incrementing a
"kill_counter" field when current physical_io = the snapshot
physical_io(i/o does not increase over say a 15 minute
period).  This seems to work ok as long as I only insert new
users each 15 minutes this is ran.

Once this counter reaches a certain number, i then first
perform an insert into a killed_users table(for auditing
purpose) and then would like to use the "kill" command
inside a batch to kill the spid of any users where the
counter has reached n.

I can't seem to dynamically build this sql statement.
How can i use something to the effect of
kill (select spid from kill_user where kill_counter = 5)
What syntax can I use for this?

After successfuly killing the user I then would delete their
spid row from the kill_user table to ensure they will get a
new start.

-d






>>Our ASE database version is 11.9.2.5 on NT.  An internal
>>audit questioned our inability to time-out inactive users as
>>well as locking logins after n- number of unsuccesfull
>>attempts.   Does anyone have workarounds for these.

>>Is there some means of creating a table keeping i/o, cpu
>>count info and killing those users who show no gains in i/o
>>during, say a two hour period.  What are some of the columns
>>I can query that give i/o, cpu, mem usage results?

>cpu physical_io memusage in sysprocesses. In 11.9.2, you don't have dynamic
>SQL, so you would probably have to write a script to prepare the kill
>statements based on info in syslogins, then run the script. You
>probably want to put the kill in an IF statement to make sure the
>total cpu and io has not creeped up in the mean time. It is still
>somewhat dangerous because there is a nonzero amount of time between
>the IF and the Kill. ie, there is a small chance that the process
>actually logs out, and you kill a brand new process. Also, you want to
>make sure this process is not idle because of being blocked by someone else.

>>I know that version 12 addresses the max failed logins
>>problem, however our application vendor has not certified 12
>>yet and probably wont for at least 6 months.  

>Does your application have an error handler in the login process? You
>can write failed logins to a table, and use that to count the failed
>logins. Loggin in can then be blocked using the application for anyone
>who exceeded the limit.
>A similar thing can be done using an Open Server as a passthrough server.
>The application does not have to change, it points at the open server
>and all SQL is passed through. The Open server intercepts any return code
>from a bad login, and updates the table we talked about earlier. It also
>disallows logins if the count is too high.

>Paul

 
 
 

Looking for ways to time-out inactive users

Post by Rob Verschoo » Thu, 20 Sep 2001 03:00:28


I think this is a very dangerous approach: you cannot really rely on
the value in sysprocesses.physical_io as an indicator of the activity
of a session. For example, you'd miss sessions doing queries requiring
only logical I/O or sessions doing the same (type of) query all the
time which does the same amount of physical I/O. Also, this counter is
reset for every new statement that's executed on the session. If you
were to use this as an activity indicator, I think you should sample
much more frequently, like every minute, to make it at least a little
more reliable.
BTW, in 12.5, there is a new column in syslogins called
loggedindatetime, which holds the date/time when the session logged in
to ASE. This also does not indicate whether the session is busy or
not, but it will tell you how "old" the session is.

HTH,

Rob V.


> Thanks Tom,

> Thanks Tom,

> I am working on a little procedure that does an insert every
> 15 minutes or so and puts fields  like spid, suid,
> physical_io, etc  into a kill_user table incrementing a
> "kill_counter" field when current physical_io = the snapshot
> physical_io(i/o does not increase over say a 15 minute
> period).  This seems to work ok as long as I only insert new
> users each 15 minutes this is ran.

> Once this counter reaches a certain number, i then first
> perform an insert into a killed_users table(for auditing
> purpose) and then would like to use the "kill" command
> inside a batch to kill the spid of any users where the
> counter has reached n.

> I can't seem to dynamically build this sql statement.
> How can i use something to the effect of
> kill (select spid from kill_user where kill_counter = 5)
> What syntax can I use for this?

> After successfuly killing the user I then would delete their
> spid row from the kill_user table to ensure they will get a
> new start.

> -d






> >>Our ASE database version is 11.9.2.5 on NT.  An internal
> >>audit questioned our inability to time-out inactive users as
> >>well as locking logins after n- number of unsuccesfull
> >>attempts.   Does anyone have workarounds for these.

> >>Is there some means of creating a table keeping i/o, cpu
> >>count info and killing those users who show no gains in i/o
> >>during, say a two hour period.  What are some of the columns
> >>I can query that give i/o, cpu, mem usage results?

> >cpu physical_io memusage in sysprocesses. In 11.9.2, you don't have
dynamic
> >SQL, so you would probably have to write a script to prepare the
kill
> >statements based on info in syslogins, then run the script. You
> >probably want to put the kill in an IF statement to make sure the
> >total cpu and io has not creeped up in the mean time. It is still
> >somewhat dangerous because there is a nonzero amount of time
between
> >the IF and the Kill. ie, there is a small chance that the process
> >actually logs out, and you kill a brand new process. Also, you want
to
> >make sure this process is not idle because of being blocked by
someone else.

> >>I know that version 12 addresses the max failed logins
> >>problem, however our application vendor has not certified 12
> >>yet and probably wont for at least 6 months.

> >Does your application have an error handler in the login process?
You
> >can write failed logins to a table, and use that to count the
failed
> >logins. Loggin in can then be blocked using the application for
anyone
> >who exceeded the limit.
> >A similar thing can be done using an Open Server as a passthrough
server.
> >The application does not have to change, it points at the open
server
> >and all SQL is passed through. The Open server intercepts any
return code
> >from a bad login, and updates the table we talked about earlier. It
also
> >disallows logins if the count is too high.

> >Paul

 
 
 

1. Timing out and re-validating inactive users

I'm setting up an application with an Access 2000 front end linked to SQL
server (with integrated security).  Each user has either a WIN 2000
workstations, WIN ME,  Win XP, or Win 89 notebook.   If the user is idle for
5 minutes or more I need to deny them access to the SQL database until the
user is revalidated (re-enters their password).

There are several solutions that might work:

Use NT security to log off idle users, but that impacts users accessing the
SQL database as well as users reading their e-mail, which isn't a great
solution.

Another solution is to use Win Screen saver to time out idle workstations
and then require that then NT password be entered to unlock the workstation,
but this would impact users reading their e-mail, as well as require me to
upgrade non-win2000 computers and enforce policy's that require screen
savers and passwords.

A solution I would prefer requires the access front end to time out the
access program after 5 minutes of inactivity (easy), and require the user to
re-validate themselves using their NT username and password as the access
database starts up.  I have no idea how to revalidate a user via NT
security.   Is there an API that can be called from VB?

Does anyone have any ideas how to send a password to NT and have NT validate
the password, or alternative solutions  to my problem.

Thanks in advance.

2. IP Address Change

3. Continual time-outs (time is not on my side)

4. Changing the Date data type format

5. MS SQL Server 2000 time-outs periodicly over the internet

6. ODBC for Velocis

7. Time outs

8. AfterUpdate event

9. Time-outs that didn't happen under NT4sp6

10. Time outs!

11. query time outs! please help

12. Time-outs on only 2 of many stored procedures

13. Distribution Agent Time Outs