CODEWITHSUNDEEP
sqlt-sqlreadonlyrecords
Mike de Klerk
Mike de Klerk

Reputation: 12338

Effectively make database records read-only

How can I make sure that specific data in the database isn't altered anymore.

We are working with TSQL. Inside the database we store contract revisions. These have a status: draft / active. When the status has become active, the revision may never be altered anymore. A revision can have 8 active modules (each with its own table), each with their own settings and sub-tables. This creates a whole tree of tables with records that may never change anymore when the contract revision has been set to active.

Ideally I would simply mark those records as read-only. But such thing does not exists as of today. The next thing that comes to mind are triggers. Thus I have to add those triggers to a lot of tables, all which are related to the contract revision.

Now maybe there are other approaches, like a database only for archiving on which the user only has insert rights. Thus when a contract revision has become active, it is moved from one DB to the archive DB (insert is allowed). And can never be altered anymore (DENY UPDATE|DELETE).

But maybe there are other more ingenious options I haven't thought of, and you did. Maybe including the CLR or what not.

So how can I make a tree-structure of records inside our TSQL database effectively readonly that is the most maintenance free, easy to understand, quickly to setup, and can be applied in a most generic way?

Upvotes: 2

Views: 434

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67341

What ever you do (triggers, granted rights...) might be overcome by a user with higher rights, this you know for sure...

Is this just to archive this data?

One idea coming into my mind was to create a nested XML with all data within on big structure and put this somewhere into a side table. Create a INSTEAD OF UPDATE,DELETE TRIGGER where you just do nothing. Let these tables be 1:1-related.

You can still work with this data, but not quite as fast as being read from physical tables.

If you want, you even might convert the XML to a string and calculate some Hash-Code, which you store in a different place to check for manipulations.

The whole process might be done in one single Stored Procedure call.

Upvotes: 1

Related Questions