CODEWITHSUNDEEP
sqldatabasedatabase-design
Niklay
Niklay

Reputation: 31

How to design a immutable append-only database?

for a project I need to implement a database which is immutable and only allows new entries. Editing or deleting entries should be impossible in any case.

I was thinking about a database which allows editing and deleting only for admins (so only me). However, I'm unsure if that is 100% safe or if it's possible to illegally get admin rights and forge the data. So the best solution would be to have a database which does not offer editing or deleting in the first place.

Suggestions appreciated! Thanks

Upvotes: 2

Views: 3505

Answers (2)

serge
serge

Reputation: 1022

The simplest way is to GRANT separated rights to INSERT/UPDATE/DELETE to users but it may be insufficient for some business rules. However, many DBMS (SQL Server for example) support INSTEAD OF triggers which can quietly bypass any DELETE/UPDATE and process INSERT depending on your custom criteria implemented in trigger code.

You can also define an updateable view having INSTEAD OF triggers to insert-only data.

Upvotes: 1

TheWildHealer
TheWildHealer

Reputation: 1616

PostgreSQL supports, since 9.5, Row Security Policies, which allow you to define select, insert, delete and update policies depending on the user, and/or some fields values in the table. You might find what you search there.

Upvotes: 3

Related Questions