Geek Answers Handbook

Download and execute SQL script - what are the security issues + solutions?

I am tasked with developing a .Net application that will download the sql script file from a specific server and execute that file on the database. I can think of a few security steps that I want to include:

  • Use secure server connection (SFTP)
  • A database user has certain access (insertion, updating data on specific tables).
  • I suggested isolating the transaction in a separate database instance. Unfortunately, they say that the transfer dataset is too large for this to be practical.

First of all, I am worried not only about letting someone purposefully damage information in a very large database, but ideally, to prevent accidental damage.

Questions:

  • Did I miss something? Are there any recommendations to consider for this kind of thing?
  • What would be the best way to authenticate a server certificate against a man-in-the-middle attack?
+4
source share
2 answers

To point 1)

  • Keep audit trail.
  • To any extent, help the user create these SQL scripts. Drop-down lists for selecting table names, switches for selecting a command, column selector, etc. This will help prevent accidents.
  • Ideally, you can go back before any particular script is executed (think about how the bank should be able to play back your transactions in order to check your account balance if it has ever been interrogated). Depending on the frequency of updates and the importance of this data, you are probably doing a great job with some daily backups, not a real transcriptional, rewritable story.

To point 2)

+1
source

I would fulfill your point 2 as restrictive as possible, but obviously your script should be allowed to do some things. Therefore, you will have to trust the person who provides the script. To make sure that you are executing a script that really belongs to that person you trust, I would sign the script and verify the signature before executing the script. Therefore, you can be sure that it was not changed by someone else.

0
source

More articles:


All Articles