0

In a server -> client scenario, wouldn't it be simpler and faster to grant a public user access to a Stored Procedure rather than using web services (XML, REST, SOAP, etc) and other interoperable solutions?

That way you could connect directly to a database and get relevant content and do what you will with it in the same way across all languages and systems?

  • How would you grant access to a stored procedure without some kind of service layer? By allowing direct TCP connections to the port on the DB server? That is probably not a good idea for security reasons. – Kilian Foth Feb 11 '14 at 12:11
  • @KilianFoth: Most, if not all SQL platforms have their own user and security modules already. Stored Procedures are much more powerful in terms of retrieving data. Granted there will be scenarios where you need more complex services on your service layer but there's countless existing web services out there today that could be replaced with a stored procedure – ThreaT Feb 11 '14 at 12:15
8

I think the key is right in your question:

In a server -> client scenario, wouldn't it be simpler and faster to grant a public user access to a Stored Procedure rather than using web services (XML, REST, SOAP, etc) and other interoperable solutions?

As any form of service provider, there are tradeoffs you make, one of these is often trading the flexibility of direct-access to something that power-user types appreciate, with the ease-of-use of a more interoperable solution.


Security Concerns

So let's start off by saying there are security concerns. They may not be so much the SQL server itself, but rather the risks inherent everytime you expose a computer to the internet. The benefit of doing this with your web server instead of your database server is one of risk mitigation.

The train of thought generally goes like this: If your OS has a security flaw found, and your database server is exposed to the internet, someone may take advantage of that flaw to get admin on that exposed machine - voila All your data are belong to them. So what's usually done is web-servers that don't have any data and have hardly any permissions on the DB are exposed to the internet. If someone get's root on these, they should ideally only have permissions to do what the website can do so the abuser has really gained nothing.


Ideal World (it still doesn't make sense)

Security concerns are (probably) the number 1 reason people don't do this in practice, but we'll assume an ideal world where an exposed database server doesn't have security concerns for a moment.

In this ideal world, what happens when you have some data you want exposed and you create and expose the stored procedure? Who have you just exposed this data to? The entire internet? Sort of. The reality is you just exposed this data to everyone with the skills, tools, and time to create a connection and access your stored procedure.

Now let's ask a slightly different version of the above question: what happens when you have some data you want exposed - who do you want it exposed to? If your main target is to expose that data to the internet at large, you'll want to create a web-page that displays that data. At this point, you've created the interoperable solution already, so now what value does the direct stored procedure access have? Same value as before I guess, except those power users would probably just use your interoperable interface instead of the SQL server one because it's easier.

Let's take another approach to the above thought - What if your main target you want to expose the data to is third party developers? Great, they probably have the skill, time, and tools to access your stored procedure directly! But wait a minute, if you have fixed set of consumers of your dataset, do you really need to expose the server to the entire internet? Not really, it's going to simplify things from a security and auditing perspective a lot if you create secure tunnels for your 3rd party consumers which puts them in an effective intranet with your DB - an intranet is exactly how people normally access DBs anyways so now you're back to the typical scenario.


Conclusion

So as I hope I've illustrated with some logical thought experiments and a bit of leading the witness, we can agree the times where such a scenario actually provides value are pretty slim. I suppose you could do it, and there may even be an edge case you hit where it actually makes sense. However as soon as you make that logical leap, you also have to recognize there are security risks. In conclusion: why bother?

  • Thanks for the well-thought of response and the time spent writing it. In terms of security, have you heard of the Peer-to-Peer specification? (What torrents are based on). People have been sharing data interoperably for quite some time now... In terms of user validation and entry - It's based on TCP/IP so assuming we're in a world of static IP's, validation is pretty trivial, right? – ThreaT Feb 11 '14 at 14:19
  • @ThreaT like I mention above, the security risk is less about the server or service itself, it's more about the risk when your DB has critically private data like passes or user addresses. Imagine the machine's OS or other software has a security flaw found, and when taken advantage of now they've got access to your database server. The front-end allows extra layers of security; first they have to exploit your web-server, then from there they have to get through the network barriers to the DB, and then they have to exploit the DB server. Those added layers make the DB's data more secure. – Jimmy Hoffa Feb 11 '14 at 14:27
  • I understand but at the same time I appreciate that a direct DB connection is magnitudes faster than an HTTP web service when it comes to retrieving data in the million record range – ThreaT Feb 11 '14 at 14:31
  • @ThreaT if you have public consumers requesting a million records - I'm going to assume they're power users, at which point GO FOR IT! Just tunnel that so it's like intranet access. I somehow doubt the internet at large has any use for 1 million records ;) - that said I hope you trust that set of users! They are the same set with the skills to exploit your server if they wish it. – Jimmy Hoffa Feb 11 '14 at 14:33
1

The downside is that your client now knows that you're providing data from a database via stored procedures, and has to hook directly into that to get at the data. If you wanted to change to e.g. a NoSQL solution, or proxy the call on to somewhere else, or load-balance requests, etc etc etc, this is much harder if you don't have a layer in between the data and the client.

Essentially, if you have;

Client <====> Interoperability Layer <====> Data

Then the layer could be doing anything...or not very much. But you're now free to change what it does at any time, so long as the interface to the client doesn't change.

  • What I'm proposing is to make the data the Interoperability layer and then the processing logic thereafter can just be for reading the data in different ways. Kinda how the File -> Application logic works: Although you might have one .mp3 file, there's probably thousands of different mp3 players that could play that file, then it's just about preference... – ThreaT Feb 11 '14 at 14:23
  • I see what you're getting at, but I think the equivalent setup would be to publish files for download by clients. Having them get the data directly from the database necessarily introduces an interoperability later, and from experience once a client builds a method for fetching data, they don't like to change it. Hence in the long-term (and maybe that's not a consideration for you), giving yourself a more flexible delivery layer will pay for itself in time IMHO. – Rory Hunter Feb 11 '14 at 14:42
0

Apart from security reason. 1) Will every one knows how to access the data through stored procedure? 2) Will every once have querying tools in their PC\Laptop?

If it is a client requirement we need to satisfy it. Also, we need explain the security issues when database server is exposed to internet.

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.