0

I have a table with billions of rows in SQL Server. I want to ensure that the data is read-only (immutable) and always will not changed after being inserted into the database.

My current thought is to use SHA3 hash algorithm to make a hash chain. 

HASH(current_row) = HASH(HASH(previous_row) + current_row) # as the hash content of the current row

The solution above has the following shortcomings:

  1. It may take too long if there are billions of rows as every record hash is related to the hash of the previous record.

  2. It does not allow compute in parallel.

Considering the performance, what is the most practical way to make sure data integrity?

ichbinblau
  • 4,507
  • 5
  • 23
  • 36

1 Answers1

2

Data integrity means something else. If you need to make sure that some users will not be updating and deleting anything, then do not provide them with the update and delete grant.

Whether reading will be performant is unrelated to the grants. You'll need to know your fetch scenarios and setup correct indexes. If that is not enough you may need other more advanced techniques (sharding, only reading from replicated slaves etc)

cherouvim
  • 31,725
  • 15
  • 104
  • 153
  • What if the database is hacked? – ichbinblau Dec 03 '16 at 08:42
  • 1
    Hacked? That is too broad. If your systems get hacked then you have too many problems. If you want to prevent problems when a system is hacked then do not put your data into that system. – cherouvim Dec 03 '16 at 08:43
  • Then where should I put the data? And which system is absolutely safe? – ichbinblau Dec 03 '16 at 08:45
  • @Theresa I'm not sure if you are serious of having fun right now. Nobody is in a position to answer a generic question like that without any context. And by the way, no system connected to the net is safe. – cherouvim Dec 03 '16 at 08:47
  • Your task as an application engineer is to make something work and perform reasonably well, and the task of your sysadmin is to keep your servers safe. You also need to collaborate with a senior application engineer or a security expert in order to make sure that your application will not allow evil people compromising your data. Good luck. – cherouvim Dec 03 '16 at 08:48
  • We just want to prevent the case that someone uses sysadmin's account to change the data or someone hacks the database. Then we have a way to detect the change. – ichbinblau Dec 03 '16 at 08:52
  • 1
    @Theresa if someone has the sysadmin's account who is managing the server then you are pretty much done. You can find ways to encrypt your data stored in SQL server but if someone hacks your systems then they'll be able to figure that out. – cherouvim Dec 03 '16 at 08:54
  • Consider making new questions about specific and concrete things. Your question currently feels like "how can I stay safe in life?". – cherouvim Dec 03 '16 at 08:55