Are stored procedures a good idea when the server is not under your control?

Question

Is there a way to protect the integrity of stored procedures at the software level to ensure that they will do what they expect?

I do not want to use sprocs, because reading and modifying them is so simple as long as you have administrative access to the computer and a copy of Management Studio Express. This is a common environment in many installations where clients own the server, and previous experience has taught me that customers (or competitors) cannot be trusted to keep their dirty hands off the database.

If the stored procedure can be modified to do something, if the software does not check the constant check, it could be forced to do what it should not, and it would not be wiser. It makes me feel very uncomfortable to know that I do not control what the software does.

Is this a valid problem?

Answer 1

It can be encrypted with stored procedures using WITH ENCRYPTION . There are such disadvantages as a stored process, so it is extremely difficult to decrypt.

Another option is to use the ORM tool (or similar), which generates SQL code dynamically, for example. Linq to SQL / Entities, NHibernate, etc.

Or you can just make sure that the client knows that the system will no longer be supported if any changes have been made at the database level - there may be enough incentive for them not to intervene.

Answer 2

To be honest, if they have administrator rights, stored procedures are just one of many ways they can mess up your software. Why don't you just make it clear to the client that they are not supported if they store stored procedures. If they create a problem, ask them to run a script that will upload your stored procedures to a file and ask them to send it to you.

Any changes mean that you will fix it, but on their penny, and not on yours, as they caused the problem.

From a validation point of view, the only thing I could think of was to start a stored process with known data and ensure that it would return what you think should. But this can be difficult to achieve in your database.

Answer 3

"Is that real concern?"

Yes. Stored procedures are a maintenance nightmare, even on a locally managed server.

You really don't need them. Build the application as if you were using stored procedures. Create strict, short procedural elements as if they were implemented as stored procedures. Then implement them in your chosen language - it will work as fast as a stored procedure. (In some cases, faster).

Some people have jokes claiming that stored procedures are fast. These jokes are never compared between a targeted transaction outside the database and a stored procedure. A joke always compares some sprawling unwanted program with a revision that uses stored procedures.

This creates targeted transactions that improve performance. This can also be done in your application.

Answer 4

I do not quite understand your scenario. Why do your competitors have direct access to your database so they can mess things up?

How could you support your application if users manually make changes to your database without consulting you?

Well, in addition to properly setting the stored procedure privileges, you must put all your stored procedures in the original control and write a simple script that deletes all (user-created) stored procedures and populates the database with the appropriate ones. Every time you feel that someone has ruined your stored procedures, you simply run the script. You can even run it as a scheduled task.

But honestly, you should not give permission to anyone to directly access the database.

Answer 5

Of course, this is an urgent problem. If you are worried that stored procedures are changing, you probably should also be worried about other parts of the database!

You must, of course, program more defensive protection than usual on stored procedures. There is always the temptation to consider this as a reliable medium when you are in full control of the content. For example: check that the parameters are what you expect before they are called, check the length of the fields as expected, etc.

I agree with Pax above that it is best to handle through contact; if the client breaks the database into "fiddling", then after that they will have to pay for the fix.

You can always check a representative sample of the length of the stored procedure, at startup or something else - you can catch up with the user if something does not match.

Answer 6

If you give them administrator rights (general), then you cannot stop them. However, there are some great tools, such as RedGate SQL Compare, that allow you to compare two database schemas (for example, a snapshot of what it should be against what it is now), and it will instantly tell you if there are any any changes, and what they are.

If you know that the user is going to add their own material, tell them some basic rules, for example, if they are going to add tables, add some kind of prefix to the name of the table, which designates it as his. The same thing works for stored procs ... if they add new ones, add them with some kind of prefix to indicate them.

If you want to give them the opportunity to change the functionality of your stored processes or add columns to their tables, you need to do some projects in advance. You can take each of your tables, such as TableA, and add a child tableA TableA_User with a one-to-one relationship. This table contains any user data that you can allow them to access this main table. You can also give them predefined hooks to change the functionality of your business logic. For example, every time you add a new such-and-such line, you can call the stub of the stored procedure, which transfers the data of the new line, and they can change it before storing it in the database. Obviously, this is getting complicated quickly.

The best way to handle this is to stay responsive with your client. If they want change, quickly implement them and pay for them.

Answer 7

I use stored procedures as “interfaces”. Of the performance benefits (stored processes almost always outperform special requests), it allows you to freely communicate between my db and user interface code.