How do I enforce a write-once then read only database table in SQL?

Question

Is it even possible?

My use case is a ledger table, with a requirement that once a record is created, it should be read-only, i.e. no-one should be able to edit or delete it. This only applies to the ledger table and tables with a direct relation to it - there are other tables in the same schema which will be updated/deleted as normal.

My understanding is that for data integrity purposes these sorts of constraints should be applied at the database layer, but I can't find a clean, widely accepted way of doing this - is this a use case where I'd just be better doing it in the application layer?

The ideal would be for a way to do it in plain SQL, so as to be agnostic of what DB platform is used, since that may be subject to change, but I realise that may be too much to ask for, so if it has to be platform-dependent, some flavour of MySQL is preferred.

Thank you!

Answer 1

I see at least two ways of accomplishing this. The first approach is to not grant DELETE and UPDATE privileges on these write-once tables, or, for that matter, any privileges apart from INSERT and SELECT, thus only allowing users to insert into or select from them. This option has no performance overhead, as the privilege check is a part of any statement processing, but it can be overridden by users with elevated privileges, like root.

Another option is to define BEFORE UPDATE and BEFORE DELETE triggers on these tables and use the SIGNAL statement in the trigger body to raise an exception, which would prevent updates and deletes respectively. There is slight performance penalty that you will pay for this, but also some added security, as even privileged users won't be able to get past the error without disabling or dropping the triggers.

Answer 2

Permissions seem to to be the obvious choice - however you could also use the ARCHIVE Storage Engine. This table engine is designed to record large amounts of data that will not change:

The ARCHIVE engine supports INSERT, REPLACE, and SELECT, but not DELETE or UPDATE. It does support ORDER BY operations, BLOB columns, and basically all but spatial data types (see Section 11.5.1, “Spatial Data Types”). The ARCHIVE engine uses row-level locking.

The difference to permissions is that someone with extended privileges would still be able to change data on most other table types, while ARCHIVE does not allow anyone to alter data that is already in the table.

Answer 3

Look into "Point in Time Architecture" or "Temporal Database Architecture"

Database Design: A Point in Time Architecture

In most relational database implementations. Update and Delete commands destroy the data that was there prior to their issue. However, some systems require that no information is ever physically deleted from or updated in the database. In this article, Arthur Fuller presents a solution to this requirement in the form of a Point-in-Time architecture: a database design which allows a user to recreate an image of the database as it existed at any previous point in time, without destroying the current image.

Temporal database

From Wikipedia, the free encyclopedia
Temporal database stores data relating to time instances. It offers temporal data types and stores information relating to past, present and future time.

The basic ideas of both is that you either need to add data without deleting - or store data in such a way that you can pull the data as it exists currently... or existed at a previous datetime.

related question here: how-to-create-a-point-in-time-architecture-in-mysql,