#5 Audit/History tables

closed
nobody
None
5
2007-10-08
2007-06-10
No

Hi,

I want to build a solution with Andomeda, but I need some form of audit/historie tables.
I saw the topic mentioned in the documentation. It should be possible with the cascading options.
Is there a sample definition available how to do this?

Thanks in advance.

Juan José.

Last response from Kenneth:

I wonder if we can do it less verbosely. I'm thinking of something like this:

table a_schemas:
module: models
description: Schema of database
uisort: 110
# here is the key trick. This single setting would
# -> Create a table a_schemas_history
# -> Put a column ts_audit (or somesuch name) into the child table
# -> Create triggers to push the insert and updates there
audithistory: "Y"
# This setting would cause the child table to automatically contain
# all columns in the parent table
auditallcolumns: "Y"

# these columns would automatically be audited
column a:
column b:
column c:

# Let's turn off auditing for a particular column
column d:
audithistory: "N"

As for security, the audit table would get the same SELECT permission as its parent table. It would be denied UPDATE, DELETE and INSERT to all users.
We would still need to define the purge process, what group or user is authorized to delete rows. One way to do this is to build stored procedures that do them and assign EXECUTE privileges to one particular group, perhaps the "purge" group, and have them go by date. A simpler procedure would simply be to allow DELETEs to some audit-purge group. Then we'd need a UI utility to launch that process. I have not really thought that through yet.

Discussion

  • Kenneth Downs
    Kenneth Downs
    2007-10-08

    • status: open --> closed