SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
Audited Table Schema Column Addition Change

 
Reply to topic    SoftTree Technologies Forum Index » DB Audit, DB Mail, DB Tools View previous topic
View next topic
Audited Table Schema Column Addition Change
Author Message
healthscreen



Joined: 05 Nov 2008
Posts: 5
Country: Canada

Post Audited Table Schema Column Addition Change Reply with quote
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?
Wed Nov 05, 2008 8:59 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6509

Post Reply with quote
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….
Wed Nov 05, 2008 9:48 pm View user's profile Send private message
healthscreen



Joined: 05 Nov 2008
Posts: 5
Country: Canada

Post Reply with quote
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
Thu Nov 06, 2008 11:51 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6509

Post Reply with quote
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
Thu Nov 06, 2008 12:03 pm View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » DB Audit, DB Mail, DB Tools All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.