How can we help you today? How can we help you today?

Tracking flyway_schema_history changes

Has anyone attempted to put guardrails around modifying flyway_schema_history? In theory, it should only be modified through Flyway processes, but in practice, it's just a table! Anyone with write permissions could change the data.

A trigger seems like the most immediately straightforward way to prevent someone unauthorized from messing with it, but then that is detectable on the schema model. My context is SQL Server, so perhaps an extended event or audit? Those wouldn't prevent changes being made, but would mean that we would have logs of any time the table had been touched if we started having issues.

Motivation: We ran into an issue at one point where FSH was missing rows even though a Migrate had run, and we have no idea how that might have happened.

emmar00
1

Comments

1 comment

Sort by
  • Jon Kirkwood
    Official comment

    Hi emmar00 

    Thank you for reaching out on the Redgate forum.

    You’re right in that flyway_schema_history is just a regular table, so anyone with write access can modify it.
     

    The simplest protection is to lock down permissions so only the Flyway service account can perform DML on the table and everyone else gets read-only access.

    This table can be dropped as part of a Flyway process so sometimes expected that this table may be dropped and recreated e.g. flyway clean

     

    If you want extra assurance, we'd suggest enabling SQL Audit or Extended Event to log any writes to that table. This should provide some extra tracking if records are changed and you can trace who made the changes.

     

    Triggers could work too, but they’re visible and can interfere with Flyway operations, so generally these are recommended to be avoided unless tampering is a known issue.

    Jon Kirkwood

Add comment

Please sign in to leave a comment.