Intro:
Recently a project fell into my lap which presented an interesting challenge; my boss wanted to be able to audit who was accessing social security information in our database. The term audit is thrown about a good deal and can be less than speciffic. Here I am talking about audit in the sense of being able to trace an action back to a single end user. Note my boss is interested in who is accessing this information, not just updating, inserting or deleting this information.
Thus, the interesting bit, we want to be able to track not only the usual inserts, updates, and deletes, we are also interested in tracking selects. It is the tracking of selects which presents the largest problem so I will focus on this issue. I hit the net and did some research. Turns out there are several ways to do auditing with informix.
1. Set up an onalter mask
2. Modify programs to insert into a table when an action is preformed
3. Buy/build a gateway product.
4. Use a trigger
Part 1 Which option to select:
Option 2 is really the best option when one wants to track selects and is running a version of IDS prior to 9.2x, particularly in a high demand environment onaudit masks do not provide the necessary functionality (see blow). It seams to me in all other cases option 1 would create less overhead as using compiled or interpreted code has less of a performance impact than creating an alter mask which can handle specific selects.
Option 3 Is a fine option for lower demand high security heterogeneous database environments. A database gateway is a bit like a middle man between the world and a database. The gateway keeps track of all access to the database both outside of and within the database structure. Gateways are database aware centennials which look for fishy activity and log this activity while (depending on product) responding to such activity. A good database gateway can take evasive steps to alert database Admins of policy violation or suspicious behavior. Unfortunately a gateway can stifle traffic in high demand environments and is another product (often a dedicated peace of blade hardware) to maintain.
Option 1 is perhaps the most elegant and easy to maintain option for exceedingly simple low use database structures. An informix command, onaudit provides a great way to inspect insert, updates and deletes across an entire database. onaudit creates a plain text log file which is rotated per Linux’s normal log rotation methodology.
However option 1 dose not work with selects and can not be isolated to a single table or row thus it dose not meet my bosses criteria. We could, however, enable onaudit to track every time a table is accessed in a database. This is relatively problematic as it would produce LARGE log files which would then need to be parsed. The log files could be parsed with an external program and its data inserted into an audit table; once the external program is executed and the data would be exported to an audit table the log file could be removed and the onaudit process could be restarted with the -n option (which would create a new log file). Needless to say there is a HUGE performance (specifically I/O) downside to this method but if your using a version of IDS prior to 9.2x I see few other options aside from changing your code base.
Part 2: What we chose:
If you are running a version of IDS greater than 9.2x (which we are) you are in luck as triggers can be created which will also monitor selects. This process is a sort of a modification to option 2. Now as a rule, the RDMS will do something each time a select is issued on an SSN in or outside of code.
A trigger is a defined action which is executed when an operation on a table is invoked. Prior to IDS 9.2x these operation were limited to updates, deletes, and inserts. It should be noted triggers can entail a rather large performance impact as they will be executed (and thus preform at least one operation) for each specified action on a table. In our case we are interested in gathering information on who selects social security numbers from our social security table. So I created a trigger something like
CREATE TRIGGER ssn_audit SELECT OF row_interest ON table_interest
REFERENCING OLD AS table_interest_alias
FOR EACH ROW (INSERT INTO ssn_audit_table
VALUES(table_interest_alias.item_interest, table_interest_alias.other_item_interest, USER, CURRENT))
This trigger now insert some data, a user name, and the current daytime into a table each time row_o_interest is selected (or sub-selected).
Filed under: Technology, Work | 1 Comment »
