Re: Inactive users

On Sun, 19 Dec 2004 21:55:37 -0800, J. Dex <cemail4_at_adelphia.net> 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

--
http://www.freelists.org/webpage/oracle-l