Audited Table Schema Column Addition Change

Hi,

How does your product handle schema changes under MySQL?

The example case is

- Create a table
- Setup auditing of data changes for that table for all columns
- Run an alter schema to add a column to the table
- Update or insert row for that table
- I want the updated/inserted row do appear in the audit_xxxx table. It does not.

Is there a way to have this column created dynamically or at least provide support in the DB Audit Export GUI to modify the audit without having to throw away the old audit login?

Hi,

It sounds like you are talking about data-change auditing. If you modify structure of the table being audited (e.g. make a schema change) you obviously need to update the audit configuration to reflect the change. In all database systems, except MySQL, , adding a new column to a table, doesn't break the auditing, just data changes in the new column don't get auditing until the audit configuration gets updated. This is like if the added column hasn't been selected for the auditing in the first place. In comparison, in MySQL it breaks the auditing and the auditing stops working for the table until the it disabled and re-enabled for that particular table or some other configuration change is made. This update can be made using the DB Audit Console (GUI or web version) or programmatically using the API.
Note that you modify the configuration, you don't have to "throw away" anything. You can choose to keep the old audit trail table in place, or if you want, export it to an external file. A better solution would be to deploy the central audit repository and don't store the data locally on the server being audited.

PS. The config cannot be updated automatically; DB Audit is not part of the database engine and not hooked to the engine directly. It is an external auditing and security toolset. Besides ALTER TABLE... ADD COLUMN is only one type of schema changes, there are tons of other types of schema changes for which it is completely unclear how to handle "schema changes", for example, adding new columns via CREATE TABLE new.. INSERT INTO new SELECT FROM old... DROP TABLE old. and what to do if that is done using external files or backup restore, etc….

Hi,

The background for our requirement is that we are an ISV that produces software for medical offices. We are looking at using DB Audit in our product to audit changes to the database table. Because of this the client will not have a central audit repository available and the audit logs must be accessible in a DB format since the user must be able to view the audit trail. The problem is that when we upgrade the client software we would also upgrade their table schema. I take your point about the complexity of schema auditing. I guess we will have to figure out a way to export to an external file and then upgrade.

Interestingly, MySQL audting does not appear to break when I added a column although it obviously did not audit the new column.

Can you confirm that your API works for MySQL? Also would it be possible to get an trial version of the API to play around with?

Finally, we would like to discuss licensing for embedding your product in our product. Who should I contact to discuss?

Thanks,

Martin

Hi,

The best option for dealing with schema changes in this situation is using DB Audit's API. Basically when deploying schema changes, application upgrades, you also run a small script using DB Audit's API command line interface (or programmatic interface if you wish) and that script goes to the database and resyncs auditing settings for all affected tables. As you can see the entire process is not complicated. You can also use the same API to automate installation of auditing in the first place.

Please email to sales@*NOSPAM*softtreetech.com for further assistance. Feel free to refer to this support topic for more details.

Thanks,
SysOp