How can I detect unauthorized changes to RT?

Marc_A_Tisseur · March 29, 2006, 5:29pm

Greetings,

I want to monitor my RT installation for unauthorized changes. I can use an
intrusion tool to detect changes to the files (AIDE, Tripwire, etc), but I’m
interested in changes to objects that are stored in the database itself
(e.g. global scrips, templates, custom fields).

I suppose I could keep a reference copy of the various tables I’m interested
in monitoring, and periodically compare the lastupdated field values; If
something doesn’t match, launch additional queries to find who made the
change (lastupdatedby) and what was changed (diffs on the key data fields
like custompreparecode). This sounds VERY CPU intensive however.
Alternatively, I might be able to use database trigger functions but I’d
prefer not to start messing with the DB schema.

Has anyone implemented a solution for a similar requirement, or can offer
better suggestions?

Regards,

Marc Tisseur
Manager, Desktop Support Group and Helpline - IITS
Concordia University
Montreal, Canada

der_Mouse · March 29, 2006, 7:20pm

From: “Marc Tisseur” marct@alcor.concordia.ca

Alcor? Hey, I know that host! Small world.

I want to monitor my RT installation for unauthorized changes. I can
use an intrusion tool to detect changes to the files (AIDE, Tripwire,
etc), but I’m interested in changes to objects that are stored in the
database itself (e.g. global scrips, templates, custom fields).

You could grab copies of the relevant database entries with stuff like
(assuming mysql, which is what the rt instance I’ve been working with
uses; I assume other databases have similar capabilities)

echo ‘select Scrips.* from Scrips’ | mysql rt > scrips.dump

and then monitor scrips.dump with your Tripwire or moral equivalent.

I suppose I could keep a reference copy of the various tables I’m
interested in monitoring, and periodically compare the lastupdated
field values; If something doesn’t match, launch additional queries
to find who made the change (lastupdatedby) and what was changed
(diffs on the key data fields like custompreparecode). This sounds
VERY CPU intensive however.

It actually doesn’t sound all that bad to me. If you use it on tables
like Tickets or Transactions, yes, it will be expensive, but I assume
you’re more interested in things like Scrips or Users. I would suggest
trying it rather than assuming it will be too cpu-hungry.

/~\ The ASCII der Mouse
\ / Ribbon Campaign
X Against HTML mouse@rodents.montreal.qc.ca
/ \ Email! 7D C8 61 52 5D E7 2D 39 4E F1 31 3E E8 B3 27 4B

Jesse_Vincent · March 30, 2006, 1:16am

Greetings,

I want to monitor my RT installation for unauthorized changes. I can use an
intrusion tool to detect changes to the files (AIDE, Tripwire, etc), but I’m
interested in changes to objects that are stored in the database itself
(e.g. global scrips, templates, custom fields).

Has anyone implemented a solution for a similar requirement, or can offer
better suggestions?

I’ve not seen this done before, but the suggestion that you dump the
relevant tables and look for changes seems sane. Whatever you end up
with, I’d be thrilled if you could document it on
http://wiki.bestpractical.com

Thanks,
Jesse

Michael_Erana · March 30, 2006, 10:36am

Just to expand a bit on derSouris’ and Jesse’s suggestions;

I’d probably follow this process:

To monitor the actual files and mods:

Post install - create a repository on SVN or CVS and commit the base profile as a reference.
Secure access to repository to controlled account
Create cron job that runs a diff against the files on the file system vs the repository and create a hook script that will e-mail on change detection.

To monitor the DB schema:

Post install - dump the schema to file.
Commit known good schema into the repository (created from prior phase)
Use similar script as in #3 in above phase but first dumps the schema over older version (to refresh) and then compare current snapshot with repository and alert as mentioned before.

That method will actually show you what changed… The tripwire method is good for alerting of a change but I can’t recall how far tripwire goes into actually telling you the nature of the change in the files. If I recall correctly tripwire simply does a hash comparison…

Regards,

Michael Eraña, CISSP
CTO
PC Network, Inc.
eranam@lanusa.com

John_Rouillard · March 30, 2006, 2:42pm

Greetings,

I want to monitor my RT installation for unauthorized changes. I
can use an intrusion tool to detect changes to the files (AIDE,
Tripwire, etc), but I’m interested in changes to objects that are
stored in the database itself (e.g. global scrips, templates,
custom fields).

Has anyone implemented a solution for a similar requirement, or
can offer better suggestions?

I’ve not seen this done before, but the suggestion that you dump the
relevant tables and look for changes seems sane. Whatever you end up
with, I’d be thrilled if you could document it on
http://wiki.bestpractical.com

Another possibility might be database triggers on update for the
tables you want to watch. Don’t know well that works with mysql but
it worked fine for a similar problem on oracle that had nothing to do
with RT. They used a trigger to update an audit table that was scanned
on a regular basis.

I don’t remember if the trigger copied the original entry to an
alternate table or not to allow reverting the change. I remember it
being discussed but not the outcome.

			-- rouilj

John Rouillard
System Administrator
Renesys Corporation
603-643-9300 x 111

Jesse_Vincent · March 30, 2006, 2:53pm

Another possibility might be database triggers on update for the
tables you want to watch. Don’t know well that works with mysql but
it worked fine for a similar problem on oracle that had nothing to do
with RT. They used a trigger to update an audit table that was scanned
on a regular basis.

That would assume that an attacker couldn’t exploit the database below
the SQL level to modify things.

John_Rouillard · March 30, 2006, 4:12pm

Another possibility might be database triggers on update for the
tables you want to watch. Don’t know well that works with mysql but
it worked fine for a similar problem on oracle that had nothing to do
with RT. They used a trigger to update an audit table that was scanned
on a regular basis.

That would assume that an attacker couldn’t exploit the database below
the SQL level to modify things.

I assumed the OP wanted auditing of the database for changes to the
tables when done via RT.

However, that’s a good point. As I said I am not sure how permissions
on triggers etc work on mysql. If a normal user can bypass then it’s
not useful. If you allow all your users access to the root user in
mysql well then you deserve what you get.

			-- rouilj

John Rouillard
System Administrator
Renesys Corporation
603-643-9300 x 111