0

Our grails app creates the normal tables for storing the domain objects.

For fraud and regulatory purposes, the key tables must be audited, i.e. every change should recorded along with who did it, and when.

On previous projects, we created a new audit schema, and put a copy of the normal tables into it, were each table was the same is the normal table except:

  1. 4 additional Columns: userId, IP, DataChanged, Action (Update, Delete, Insert)
  2. ID is no longer unique
  3. No constraints, no PK, no indexes.
  4. in an schema called audit.

On he prev. projects, the Oracle DB wrote a complex script to read the tables and columns from the normal schema, and create the corresponding tables/columns in the audit schema, then create the insert/update/delete triggers on the normal DBs tables.

We need to do something similar for MySQL.

WE need to do it at the DB (not code) level for security.

We are wondering if there is any way grails can help us with this. eg.

  1. Create a new grails audit project, copy (cut and paste) the domain classes, add the extra columns. This will auto-generate the schema at least. Then we will need to hand write the triggers, and put them in the BootStrap of the normal project.
  2. Use a grails script to read the table and column meta data from the main schema, and create the necessary sql statements to generate the audit schema, and generate the triggers on the normal schema tables.
  3. Dont use grails at all, write the entire script to create the audit tables and triggers outside of grails. Unfortunately, we dont have a DBA or SQL scripting guru available.
  4. use a plugin someone has already written to do this (Cant find any).
  5. something else?

Any ideas? Anyone else done something like this?

John Little
  • 10,707
  • 19
  • 86
  • 158
  • So, not http://grails.org/plugin/audit-logging or http://grails.org/plugin/audit-trail then? That would be my first look if I were you. Otherwise don't use Grails at all and manage everything from the DB directly. – Joshua Moore Jan 27 '15 at 12:14
  • there is also a grails envers plugin (yet a little old) – cfrick Jan 27 '15 at 12:24
  • @JoshuaMoore - good point. I tried the first one. Adding it to the BuildConfig.groovy, and setting static auditable = true in domain class has no effect on the schema - it does not create any audit tables, so I am guessing that it just logs changes to a text file (which is not what we need). We could use its "onChange" hooks, but this is not as secure as a trigger, as grails may not be the only thing to change the DB. – John Little Jan 27 '15 at 12:45
  • i don't know why audit-logging plugin doesn't work, seems that your plugin configuration it's ok. Anyway, if grails it's not the only things that can change db, i think that the best way to accomplish what you want is using triggers – Luca Farsetti Jan 27 '15 at 13:27
  • I use the `audit-logging` plugin for this purpose. In has worked very well for me. – Dónal Jan 27 '15 at 13:59

0 Answers0