Re: Inactive users
- From: stephen booth <stephenbooth.uk@xxxxxxxxx>
- To: cemail4@xxxxxxxxxxxx
- Date: Sun, 26 Dec 2004 18:08:28 +0000
On Sun, 19 Dec 2004 21:55:37 -0800, J. Dex <cemail4@xxxxxxxxxxxx> wrote:
> I want to get a list of all the users in the database that have been
> inactive for more than 90 days. What is the best way to do that? What
> query/table should be used?
>
You could audit logons but that could cause problems if you've got
users logging in and out frequently orand have a very large number of
users. Some third party applications (especially if they were
originally designed to work with other RDBMSs that have been ported to
Oracle, i.e. most of the ones that I seem to end up working with) will
login, run a transaction, commit then log out. You may find yourself
having to deal with huge volumes of audit data so keeping 90 days
worth may not be practical.
I'd go with something like a table with three columns (username
(primary key), last_logged_in and do_not_drop (defaults to false)) and
an on logon trigger. When a user logs on the trigger first sees if
there is already a record for that user and if there is it updates the
last_logged_in column with the value of SYSDATE, if not then it
inserts a row for that username with a last_logged_in value of
SYSDATE. Periodically query the table for users with a last_logged_in
value less than SYSDATE - 90 days. The do_not_delete column would be
a flag so you can manually create records with that column set to
some value you define as true for users that may not log in within a
90 day period but you do not want deleted (for example often you'll
have the data schema for an application owned by one user but the
actually application users all log in as different users who can
access the tables but cannot drop them, for security reasons). You're
query to identify users who are candidates for dropping can specify
only those users where that value is false.
Stephen
--
//www.freelists.org/webpage/oracle-l
Other related posts:
