My team makes a product (business management software) that our customers install on their own servers. The product uses a SQL database for data storage and app configuration.

There have been quite a few cases where something strange happened in the customer's database (caused by bugs in our app and also sometimes admins who mess with the database). To figure out what is wrong with the data, we have to send SQL scripts to the customer and tell them how to run them on the database server. Then, once we know how to fix it, we have to send another script to repair the data.

Is there a secure way to add a page in our application that allows an application admin to enter SQL scripts that read and write directly to the database? Our support team could use that to help customers run these scripts, without needing direct access to the SQL server.

My big concerns are that someone might abuse this power to get data they shouldn't have and maybe to erase or modify data that they shouldn't be able to modify. I'm not worried about system admins, because they could find another way to do the same thing. But what if someone else got access to the form? Is there any way to do this kind of thing securely?

  • 5
    I would never add a way for an end-user to execute arbitrary SQL against your database. Give the information to the DBAs and let them do their job. – Michael Todd Jun 27 '11 at 16:19
  • 2
    Is there a reason the admin needs a special page to do this if they are running arbitrary scripts? Having that feature in the application is potentially dangerous. Would it be possible to just log in directly to the database through some dbadmin tool (will vary, depending on the database being used)? – FrustratedWithFormsDesigner Jun 27 '11 at 17:35
  • Some of our customers do not have DBAs, and a lot of the others have limited access to their DBAs. This makes it difficult or impossible to use the normal db admin tools. – Josh Yeager Jun 29 '11 at 16:07

I have provided support pages such as the one you have described in the past to a system that was already Role based.

It is not a problem of Authentication as I assume you will be able to tell WHO is currently logged on, Eg. Joe User, Joe Support, etc...

It is a problem of Authorization. This can be typically solved in a stateful web application by adding a Role to specific users, Eg. 'Support', 'Administrator', 'Restricted User'.

This support page can simply redirect to an error page if that role doesn't exist for the given user.

  • That's basically what I was thinking, but the idea still makes me nervous. – Josh Yeager Jun 29 '11 at 16:09
  • @Josh, Understandable, just test it extremely well and write good solid logging. Make everything function on the support page fully reversable by another function or set of arguments to a function on the support page as well. That way when Support screws up (and they will), you can just check the logs to see what they REALLY did and then just fix it. – maple_shaft Jun 29 '11 at 16:50

You might be able to do something with PKCS, encrypting the scripts with the server's public key and having it decrypt all files uploaded to it using its private key. You could map the page to a secure port, and possibly only open that port for maintenance (although that might involve additional admin support on the client side). Overall, though, I wouldn't recommend any kind of "back-door" maintenance tools -- opening a security hole for the sake of convenience looks really bad if anything ever goes wrong.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.