From: David A. Desrosiers <desrod@gn...> - 2006-03-16 15:15:08
This morning, a user reported to me that another new user had
created an account, logged in, and promptly started to set reminders
for EVERY user on the system for every bug he visited. This means
several bugs had 1,311 users monitoring them.
At first, I thought this might be a weird bug in the URI
mangling, or a malformed POST coming in remotely, but then I noticed
the defaults in config_default.php was set so that a user with the
access of REPORTER could add reminders for any bug to any user on the
system, including multi-selecting ALL the users and setting them all
to monitor any bug.
I'd like to suggest that this *NOT* be the default for new or
upgraded installations. Its the first time I've seen it happen, and
I've since disabled it, but I believe this leaves the system up for
some serious abuse, if you have a lot of users as we do here.
Along those lines, I'm trying to construct a query that allows
me to prune old users who have:
a.) Created an account
b.) Logged in at least once
c.) NEVER created a bug or bugnote
d.) Haven't logged in in 'n' months/years.
I did a quick check, and we have the following situation:
73 users have not logged in since 2001
241 users have not logged in since 2002
417 users have not logged in since 2003
350 users have not logged in since 2004
157 users have not logged in since 2005
So I have this situation right now:
SELECT u.id, u.last_visit FROM mantis_user_table u
LEFT JOIN mantis_bug_table b ON u.id = b.reporter_id
LEFT JOIN mantis_bugnote_table c on u.id = c.reporter_id
WHERE b.reporter_id IS NULL
AND c.reporter_id IS NULL
AND u.last_visit LIKE '%2004%';
This returns 166 rows in my case. This means at least 166
users have logged in, but have never reported any bugs or bugnotes.
Does this seem like a plausible query? Or did I miss something
David A. Desrosiers