Are stored procedures a good idea when the server isn't under your control?

Question

Are there any ways to secure stored procedure consistency at the software level, so as to be sure that they're going to do what they're expected to do?

I'm reluctant to use sprocs because reading and modifying them is so easy as long as you have administrative access to the computer and a copy of Management Studio Express. This is a common environment in a lot of installations where the clients own the server, and previous experience has taught me that clients (or competition) can't be trusted to keep their grubby hands off the database.

If a stored procedure can be modified to do anything, unless the software does constant checking, it could be silently made to do things that it shouldn't, and it would be none the wiser. This makes me feel really uncomfortable - to know I'm not in control over what the software does.

Is this a valid concern?

Answer 1

Its possible to encrypt stored procedures using the WITH ENCRYPTION hint - there are cons such as the stored proc then being extremely difficult to decrypt.

Another option would be to use an ORM tool (or similar) that generates the SQL code dynamically e.g. Linq to SQL/Entities, NHibernate etc

Or you could just make sure that the client is aware that the system will no longer be supported if any modifications have been made at a DB level - this may be enough incentive for them not to meddle.

Answer 2

"Is this a valid concern?"

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

You don't really need them. Design your application as if you're going to use stored procedures. Design tight, short procedural elements as if they'll be implemented as stored procedures. Then implement them in your target language of choice -- it will run as fast as a stored procedure. (In some cases, faster.)

Some people have anecdotes claiming that Stored Procedures are fast. These anecdotes are never a head-to-head comparison between a focused transaction outside the DB and a stored procedure. The anecdote is always comparing some sprawling junk program against a revision that used stored procedures.

It's creating focused transactions that improves performance. This can be done in your application, too.

Answer 3

To be honest, if they have admin rights, stored procedures are just one of the many ways they could screw up your software. Why don't you just make it clear to the client that they're not supported if they stuff around with the stored procedures. If they raise a problem, have them run a script of yours which dumps your stored procedures to a file and get them to send it to you.

Any changes means you'll fix it but on their dime, not yours, since they caused the problem.

In terms of checking, the only thing I could think of is to run the stored proc with known data and ensure it returns what you think it should. But that may be hard to achieve on their database.

Answer 4

I don't quite understand you scenario. How come your competitors have direct access to your database so they can mess up things?

How could you possibly support your application if the users are doing manual modifications to your database without consulting you?

Well, apart from setting stored procedure privileges properly, you should put all your stored procedures in source control and write a simple script that drops all (user created) stored procedures and populate the database with the proper ones. Every time you feel that someone messed with your stored procedures, you just run the script. You could even run it as a scheduled job.

But frankly, you should not give permissions to anyone to access the database directly.

Answer 5

I use Stored Procedures as 'interfaces' .Apart from the performance benefits (Stored procs almost always outperform ad hoc queries) , it allows loose coupling between my db and the UI code.

Answer 6

Of course this is a valid concern. If you're worried about stored procedures being changed, you should probably also worry about other parts of the database too!

You should certainly program more defensively than usual against the stored procedures. There's always a temptation to treat this as a trusted medium when you have full control over the contents. For example: check the parameters are what you expect before calling them, check the field lengths are as expected etc.

I agree with Pax above that this is best dealt with through the contact; if the client breaks the database by "fiddling" then they're going to have to pay for the fixup afterwards.

You can always check a representative sample of stored procedure lengths, on startup or whatever - you can nag the user if things don't match.

Answer 7

If you give them admin rights (common) then there's not much you can do to stop them. However, there are some great tools out there like RedGate SQL Compare that lets you compare 2 database schemas (like a snapshot of what it's supposed to be vs. what it is now) and it will instantly tell you if there are any changes, and what they are.

If you know the user is going to add their own stuff, tell them some ground rules, like if they're going to add tables, add some kind of prefix to the table name that designates it as theirs. The same thing works for stored procs... if they add new ones, add them with some kind of prefix to designate them.

If you want to give them the ability to modify the functionality of your stored procs, or add columns to your tables, then you've got to do some design up-front. You could take each one of your tables, like TableA, and add a child table called TableA_User with a one-to-one relationship. This table holds any user data that you may want to allow them to access related to that main table. You could also give them pre-defined hooks for modifying the functionality of your business logic. For instance, every time you add a new such-and-such row, you could call a stored procedure stub that passes in the new row data, and they can modify it before you save it to the database. Obviously this gets complicated fast.

The best way to handle it is to stay responsive with your customer. If they want a change, implement it fast and get paid for it.